locked
filter vs. calculate RRS feed

  • Question

  • Hello. If I have a column that has budget and actuals to differentiate my data. Should I just filter everything by that column and write one set of measures, or should I write measures with calculate for each. I guess if I want to cmopare side by side, i should write measures....?
    Wednesday, November 2, 2016 12:07 AM

Answers

  • Hi AlexMartini,

    Your budget and actuals data are in same column, you want to create measure based the column, right? From what I know, you can use filter to calculate budget or actuals separately. I try to reproduce your scenario and create the below sample data.

     

    Calculate the sum of budget using the measure: Actuals:=CALCULATE(SUM(Table11[amont]),FILTER(Table11,Table11[Category]="Actuals"))
    Calculate the sum of budget using the measure: Buget:=CALCULATE(SUM(Table11[amont]),FILTER(Table11,Table11[Category]="Budget"))
    Calculate the difference between budget and actuals using the measure: varience:=[Actuals]-[Buget]

    Then create a pivot table, select the Category as Rows, above three measures as Values, it will calculated according to the rows. From the screenshot, the 507 highlighted in yellow is obtained  by 507 subtracts 0, the -464 in red line is obtained  by 0 subtracts  464.



    While, we will get the right difference when just select the  varience measure as Values, like below picture.


     
    If this is what you want, please share more details or sample data for further analysis.

    Best Regards,
    Angelia

    Wednesday, November 2, 2016 9:58 AM