Forum Discussion
Visual calculation
Hi good day can anyone help me on my visual calculation. I have two table Plan and Actual and they are connected to my calendar table, bridge table Trade, bridge table location and i have a measaure Delta = [Actual] - [Plan]. What i required is if the total Trade per location per Trade is greaterthan 0 then Delta is 0
Thank you
Hey AllanBerces,
Looking at your tables, here's a concise solution for your Delta calculation:
Create a new measure:
Delta Adjusted =
VAR CurrentTotal = [Actual] - [Plan]
RETURN
IF(
SUMX(
VALUES('Trade'[Trade]),
CALCULATE([Actual] - [Plan])
) > 0,
0,
CurrentTotal
)
Alternative approach if you need it at row level:
Delta Final =
VAR TradeLocationTotal =
CALCULATE(
[Actual] - [Plan],
ALLEXCEPT('YourFactTable', 'Location'[Location], 'Trade'[Trade])
)
RETURN
IF(TradeLocationTotal > 0, 0, [Actual] - [Plan])Key points:
- Replace 'YourFactTable' with your actual fact table name
- The measure checks if the total delta per Trade per Location is positive
- If positive total → returns 0
- If negative/zero total → returns the actual delta value
Test this with your data structure and let me know if you need adjustments for your specific table relationships.
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Best regards,
Jainesh Poojara / Power BI Developer
4 Replies
Hey AllanBerces,
Looking at your tables, here's a concise solution for your Delta calculation:
Create a new measure:
Delta Adjusted =
VAR CurrentTotal = [Actual] - [Plan]
RETURN
IF(
SUMX(
VALUES('Trade'[Trade]),
CALCULATE([Actual] - [Plan])
) > 0,
0,
CurrentTotal
)
Alternative approach if you need it at row level:
Delta Final =
VAR TradeLocationTotal =
CALCULATE(
[Actual] - [Plan],
ALLEXCEPT('YourFactTable', 'Location'[Location], 'Trade'[Trade])
)
RETURN
IF(TradeLocationTotal > 0, 0, [Actual] - [Plan])Key points:
- Replace 'YourFactTable' with your actual fact table name
- The measure checks if the total delta per Trade per Location is positive
- If positive total → returns 0
- If negative/zero total → returns the actual delta value
Test this with your data structure and let me know if you need adjustments for your specific table relationships.
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Best regards,
Jainesh Poojara / Power BI DeveloperHi jaineshp thank you i got the coorect value, i just removed the [Plan] on the Delta adjusted. now working perfectly.
H1 jaineshp thank you for the reply i used the firts measure and this part given wrong data. the measure should base from total if greater than 0 then 0
When I try the measure shows this, the -10 didnt change to 0
Create a new measure:
Delta Adjusted =
VAR CurrentTotal = [Actual] - [Plan]
RETURN
IF(
SUMX(
VALUES('Trade'[Trade]),
CALCULATE([Actual] - [Plan])
) > 0,
0,
CurrentTotal
)
Hey AllanBerces,
I see the issue! The measure is checking per Trade, but you need it to check the total per Trade-Location combination. Here's the corrected version:
Updated Measure:
Delta Adjusted =
VAR CurrentTotal = [Actual] - [Plan]
VAR TradeLocationTotal =
CALCULATE(
SUMX(VALUES('YourTable'[ID]), [Actual] - [Plan]),
ALLEXCEPT('YourTable', 'YourTable'[Trade], 'YourTable'[Location])
)
RETURN
IF(TradeLocationTotal > 0, 0, CurrentTotal)What changed:
- Now it sums ALL rows for the same Trade-Location combination
- Uses ALLEXCEPT to group by Trade and Location only
- If the total delta for that group > 0, then all rows show 0
In your case: Trade CIVIL + Location SOUTH has total delta of 0 (10-10), so individual -10 should remain -10, not become 0.
Try this and let me know if the logic matches what you expect!
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Best regards,
Jainesh Poojara / Power BI Developer
