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

Welcome Forums General PowerShell Q&A How do I display a Grand-Total of Grouped-totals?

This topic contains 8 replies, has 3 voices, and was last updated by

 
Participant
2 months, 4 weeks ago.

  • Author
    Posts
  • #146493

    Participant
    Topics: 73
    Replies: 119
    Points: 504
    Rank: Major Contributor

    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.

     

  • #146523

    Participant
    Topics: 8
    Replies: 375
    Points: 445
    Helping Hand
    Rank: Contributor
    $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
    
    • #146540

      Participant
      Topics: 73
      Replies: 119
      Points: 504
      Rank: Major Contributor

      Thanks a lot Mr Sam Boutros, much appreciated!

  • #146531

    Moderator
    Topics: 8
    Replies: 889
    Points: 2,740
    Helping Hand
    Rank: Community Hero

    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)

    • #146561

      Participant
      Topics: 73
      Replies: 119
      Points: 504
      Rank: Major Contributor

      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,

  • #146579

    Moderator
    Topics: 8
    Replies: 889
    Points: 2,740
    Helping Hand
    Rank: Community Hero

    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.

  • #146841

    Participant
    Topics: 73
    Replies: 119
    Points: 504
    Rank: Major Contributor
    $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.

  • #146958

    Moderator
    Topics: 8
    Replies: 889
    Points: 2,740
    Helping Hand
    Rank: Community Hero

    Cool, Sam's solution will be simple here.

    • #147030

      Participant
      Topics: 73
      Replies: 119
      Points: 504
      Rank: Major Contributor

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

You must be logged in to reply to this topic.