How do I display a Grand-Total of Grouped-totals?

Hello, my input CSV file looks like (a simplified version):

Dept,Amount
ABC,10
DEF,20
ABC,10
DEF,20
XYZ,50

and I have the ff. Powershell code:

$data = Import-Csv
$data | Group-object -property Dept |
    Foreach-object -process {
        $Sum = $_.Group | Measure-object -Sum -Property Amount
        [pscustomobject]@{ Dept = $_.Name; SumTotal = $Sum.Sum}
} | Out-gridview

which produces the desired and correct output. However, I also want to calculate and display as the last line, the “Grand Total” which is the summation of each Dept’s SumTotal. I’ve learned that:

Measure-object -Sum -Property SumTotal

will do the job. When I replace the Out-gridview statement with this Measure-object statement, I get the Grand Total, as expected, but no longer have the individual line totals for each Dept. My question is, how do I “get around” this, preferably still using the Out-gridview way of displaying (if possible). Would be grateful for any tips and guidance, Many thanks.

 

$data = Import-Csv .\file1.csv 
$myOutput = $data | Group-object -property Dept |
    Foreach-object -process {
        $Sum = $_.Group | Measure-object -Sum -Property Amount
        [pscustomobject]@{ Dept = $_.Name; SumTotal = $Sum.Sum }
} 

$myOutput += [pscustomobject]@{ Dept = 'GrandTotal'; SumTotal = ($myOutput.SumTotal | measure -Sum).Sum }

$myOutput

Store the first Measure-Object outputs in a variable. Then do an Add-Member to the output variable with -Name as GrandTotal and -Value as (variable | Measure-Object for GrandTotal | select-Object -ExpandProperty Sum)

Thanks a lot Mr Sam Boutros, much appreciated!

Thanks Mr kvprasoon, when I do the Add-Member, what -Membertype will I use? I am guessing this would have to be a ScriptProperty. Thanks,

Need not so be, It can be a NoteProperty as the new member name is GrandTotal and the value is already the sum of all.

$Grouped = $data | Group-object -property Dept |
        ForEach-object {
            $Sum = $_.group |
            Measure-object -Sum -Property Amount
            [pscustomobject]@{ Dept = $_.Name; SumTotal = $Sum.Sum }
        }
$GTotal = $Grouped | Select-object -Expandproperty SumTotal | Measure-object -Sum
$Grouped | Add-member -Membertype NoteProperty -Name "Grand total" -Value $GTotal.sum

will produce:

Dept SumTotal Grand total
---- -------- -----------
ABC  20       110
DEF  40       110
XYZ  50       110

Instead of Add-member, I utilized Mr Boutros’ solution and replaced the last line with:

$Grouped += [pscustomobject]@{Dept="Grand total";SumTotal=$GTotal.sum}

which produces:

Dept SumTotal
---- --------
ABC  20
DEF  40
XYZ  50
Grand total 110

I struggled but could not get Add-member to output the last line the way it should.
Thanks to you both, Messrs Sam Boutros and KVprasson, this was an eye-opener and very educational.

Cool, Sam’s solution will be simple here.

Thanks again. There’s actually a typo in my last post, the last line should read

$Grouped += [pscustomobject]@{Name=“Grand total”;SumTotal=$GTotal.sum}
and not “Dept”.