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”.