Visual calculation

[AllanBerces]

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

[jaineshp]

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

[jaineshp · Accepted solution]

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

[AllanBerces]

Hi @jaineshp thank you i got the coorect value, i just removed the [Plan] on the Delta adjusted. now working perfectly.

[AllanBerces]

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
)