Forum Discussion

VanNostrand's avatar
VanNostrand
Regular Visitor
3 years ago
Solved

Trouble Calculating Percentage Difference

Hi guys, I'm just starting PowerBI and I'm facing an issue for several days that can't seem to overcome. In my data set I have two tables: "Plans" and "Actuals". Within the table "Plans" there's a ...
Need Help
Tips and Tricks
  • DOLEARY85's avatar
    DOLEARY85
    3 years ago

    wrap the sum functions in a calculate function to change the context

     

    e.g. OverCost = CALCULATE( SUM (Actuals[ActualCosts])) - CALCULATE(SUM (Plans[PlannedCosts])) / CALCULATE(SUM (Plans[PlannedCosts]))

VanNostrand's avatar
VanNostrand
Regular Visitor
3 years ago

Indeed I'm still getting the issue in OverDuration.
I've included ALLEXCEPT in over duration formula and now is returning values but they are incorrect. For example I manually calculated the first value that should be -92,35%, and the dax formula returns -65,56%

OverDuration = CALCULATE(CALCULATE(SUM(Actual_Duration[ActualDuration])) - CALCULATE(SUM(Plans[PlannedDuration])), ALLEXCEPT(Plans, Plans[PlannedDuration])) / CALCULATE(SUM(Plans[PlannedDuration]), ALLEXCEPT(Plans, Plans[PlannedDuration]))

The other formula for OverCost is working but I didn't include the ALLEXCEPT, I also had to include the IF to avoid NaN divide by zero error:

OverCost =

IF(

CALCULATE(SUM(Plans[PlannedCosts])) = 0,

0,

(CALCULATE(CALCULATE(SUM(Actuals[ActualCosts])) - CALCULATE(SUM(Plans[PlannedCosts]))) / CALCULATE(SUM(Plans[PlannedCosts]))

))

DOLEARY85's avatar
DOLEARY85
Resident Rockstar
3 years ago

I've tried a similar calculation and i'm not getting the same error. Do you have a copy of the PBI file i could look at?

  • VanNostrand's avatar
    VanNostrand
    Regular Visitor
    3 years ago

    Hello,

    In the meantime I've updated the ALLEXCEPT and it seems that now is working:

    ALLEXCEPT(Plans, Plans[ProjectID])

     

    thansk for the help.