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

KT_Bsmart2gethe's avatar
KT_Bsmart2gethe
Icon for Impactful Individual rankImpactful Individual
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I am getting the below error with this code. 

    Expression.Error: A cyclic reference was encountered during evaluation.