Forum Discussion
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 column for 'PlannedCosts'.
And in table "Actuals" there's a column for 'ActualCosts'.
These are applied to the 'ProjectPhase' (each project has Phase1, Phase2, Phase3, Phase4). ProjectPhase is present in both tables.
I'm trying to calculate the percetange difference, more specifically if the phase is over cost or under cost. But I'm getting the exact same value for all phases, which doesn't make sense.
I've already tried these different dax formulas (with chatgpt) but they all return the exact same value for all phases:
| OverCost = ( SUM (Actuals[ActualCosts]) - SUM (Plans[PlannedCosts]) ) / SUM (Plans[PlannedCosts]) |
| OverCost2 = SUM (Actuals[ActualCosts]) / SUM (Plans[PlannedCosts]) - 1 |
OverCost3 = IF( DIVIDE( SUM(Actuals[ActualCosts]), SUM(Plans[PlannedCosts]) ) - 1 > 0, DIVIDE( SUM(Actuals[ActualCosts]), SUM(Plans[PlannedCosts]) ) - 1, 0 ) |
In the star schema, the fact table is "Plans". And there's a relationship with the dim "Actuals".
Could someone please add some help?
Many thanks!
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]))
6 Replies
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]))
Thanks indeed with the CALCULATE function worked.
But in the same table "Plans" I tried to apply the same formula for something equal, which is the projects in over duration. There's another table named "Actual_Duration" that states the values in the column 'ActualDuration'.
OverDuration = (CALCULATE( SUM (Actual_Duration[ActualDuration])) - CALCULATE(SUM (Plans[PlannedDuration]))) / CALCULATE(SUM (Plans[PlannedDuration]))
Error: A circular dependency was detected: Plans[Column], Plans[OverCost], Plans[Column].
- looks like you're missing the calculate at the start otherwise i don't see anything wrong with it, let me know if you're still getting the issue:OverDuration = CALCULATE(CALCULATE(SUM (Actual_Duration[ActualDuration]))- CALCULATE(SUM(Plans[PlannedDuration]))) / CALCULATE(SUM (Plans[PlannedDuration]))
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]))
))
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?
