Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

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 ...
Need Help
  • jaineshp's avatar
    jaineshp
    1 year ago

    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




jaineshp's avatar
jaineshp
Memorable Member
1 year ago

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's avatar
    AllanBerces
    Post Prodigy
    1 year ago

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

  • AllanBerces's avatar
    AllanBerces
    Post Prodigy
    1 year ago

    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
    )