Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subtract two rows from another based on a name column and by date category

I am trying to build a dashboard and need to subtract two specific row values from another row. For example deduct the headcount of two other leaders (say A2 and A3) from another higher level leader ...
Need Help
  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    4 years ago

    Hi Anonymous ,

     

    Try the below modification to the provided code.

     

    Table.AddColumn(#"Trimmed Text", "Final Headcount A1", each if [Leader Name]="A1" then [Headcounts]

                              -Table.SelectRows(#"Trimmed Text",(x)=> x[Leader Name]="A2" and x[Date]=[Date]){0}[Headcounts]

                              -Table.SelectRows(#"Trimmed Text",(x)=> x[Leader Name]="A3" and x[Date]=[Date]){0}[Headcounts]

                              else [Headcounts])

     

    Regards

    KT

artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

First, you will need to create a column indicating the group name. For example the A group might be A1, A2, A3, while the B group might just be B. To do this just use Extract menu option in the transform ribbon.

 

Next, do a groupby operation with Date and the new group column you created. Use 2 aggregations:

1. The Max of HeadCount

2. The Sum of HeadCount

 

Next, subtract the max headcount from the sum headcount, removing the max headcount from the sum headcount calculation.

Finally, subtract sum headcount from the max head count.