Forum Discussion

patem2024's avatar
patem2024
Frequent Visitor
1 year ago
Solved

Power BI DAX Measure Total in Matrix Shows Incorrect Value to Calculate Cumulative Total

Hi guys,   I've created the following measure to provide a view on monthly cost differences against the earliest month in the report (Oct-23 in this case). Apologies this is likely quite a simple q...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi patem2024 

     

    Thanks for the reply from Sahir_Maharaj and MFelix .

     

    The following test is for your reference, but I am not sure what total you need to display in this step.

     

    Sample:

     

    Create a measure as follows.

     

    Measure = 
VAR _1 = MAX('Concept Savings Profile New'[Month])
VAR _earlier = CALCULATE([Baseline Month Swing New], FILTER(ALL('Concept Savings Profile New'), [Month] < _1))
RETURN
IF(ISINSCOPE('Concept Savings Profile New'[Month]), _earlier + [Baseline Month Swing New], [Baseline Month Swing New])

     

     

    Output:

     

    Please feel free to let me know if you have any questions.

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
1 year ago

Hello patem2024,

 

Can you please tyr this approach:

 

1. Correct the Baseline Month Movement

Baseline Month Movement = 
VAR MinMonth = CALCULATE(MIN('Concept Savings Profile'[Month]), ALL('Concept Savings Profile'))
VAR SumMinMonth = CALCULATE(SUM('Concept Savings Profile'[Total Cost]), 'Concept Savings Profile'[Month] = MinMonth)
VAR CurrentMonth = SUM('Concept Savings Profile'[Total Cost])
RETURN 
IF(
    HASONEVALUE('Concept Savings Profile'[Month]),
    CurrentMonth - SumMinMonth,
    BLANK()
)

2. Create a Cumulative Savings Measure

Cumulative Savings = 
VAR MinMonth = CALCULATE(MIN('Concept Savings Profile'[Month]), ALL('Concept Savings Profile'))
VAR SumMinMonth = CALCULATE(SUM('Concept Savings Profile'[Total Cost]), 'Concept Savings Profile'[Month] = MinMonth)
RETURN
SUMX(
    FILTER(
        ALL('Concept Savings Profile'),
        'Concept Savings Profile'[Month] <= MAX('Concept Savings Profile'[Month])
    ),
    SUM('Concept Savings Profile'[Total Cost]) - SumMinMonth
)
patem2024's avatar
patem2024
Frequent Visitor
1 year ago

Thanks Sahir_Maharaj ,

I've tried your two adjustments and I end up with this (the total disappears from the Baseline Month Movement column and the cummulative is incorrect):

 

I would share the PBIX but it has sensitive detail within, I'm trying to get the totals as they would look in excel in prior posts. 

Any support you can offer would be greatly appreciated.