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

[patem2024]

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 query but I've been struggling to overcome it:

Baseline Month Movement =

VAR MinMonth = CALCULATE(MIN('Concept Savings Profile'[Month]),ALL('Concept Savings Profile'))

VAR SumMinMonth = CALCULATE(SUM('Concept Savings Profile'[Value]),'Concept Savings Profile'[Month] = MinMonth)

VAR CurrentMonth = SUM('Concept Savings Profile'[Value])

RETURN

CurrentMonth - SumMinMonth

I've applied this to a matrix and it doesn't seem to tally up, I'm expecting a value of 10,633.54 but it provides a total of 96,417.37 (which is basically the sum value of all entries less the 1st month, it's also the same when entered in to a card as shown below):

I then want to create a cummulative monthly swing difference versus the minimum month, in excel it would look like this (cummulative savings column):

Total Cost	Month	Baseline Month Movement	Cummlative Savings	Baseline FAST-P Cost
7,798.53	45,200.00	-	-	7,798.53
10,935.95	45,231.00	3,137.42	3,137.42	7,798.53
9,056.50	45,261.00	1,257.97	4,395.38	7,798.53
8,862.18	45,292.00	1,063.65	5,459.03	7,798.53
10,122.73	45,323.00	2,324.20	7,783.23	7,798.53
8,874.40	45,352.00	1,075.87	8,859.10	7,798.53
7,632.78	45,383.00	-                                      165.75	8,693.35	7,798.53
7,993.73	45,413.00	195.20	8,888.55	7,798.53
8,942.65	45,444.00	1,144.12	10,032.67	7,798.53
8,425.53	45,474.00	627.00	10,659.67	7,798.53
8,567.26	45,505.00	768.73	11,428.41	7,798.53
7,003.67	45,536.00	-                                      794.86	10,633.54	7,798.53

Thanks in advance for any assistance offered.

[patem2024]

Thanks   @Sahir_Maharaj and @MFelix   for your inputs so far, apologies if I haven't made things easy yesterday I've tried to tidy up the content somewhat below using the published posting standards (this was my 1st post yesterday).

I've managed to make some progress on this yesterday evening and on a fresh head this morning, I think part of the issue was the the data had many rows with zero in the Value column (example rows below). 

I've created a new calculated table removing zero entries for Value:

Concept Savings Profile New = CALCULATETABLE('Concept Savings Profile','Concept Savings Profile'[Value] > 0)

The new table now looks like this:

I've then created a measure to calculate the difference from 1st month cost using the following DAX logic:

Baseline Month Movement New = 

VAR MinMonth = CALCULATE(MIN('Concept Savings Profile New'[Month]),ALL('Concept Savings Profile New'))
VAR SumMinMonth = CALCULATE(SUM('Concept Savings Profile New'[Value]),'Concept Savings Profile New'[Month] = MinMonth)
VAR CurrentMonth = SUM('Concept Savings Profile New'[Value])
VAR TotalValue = CALCULATE(SUM('Concept Savings Profile New'[Value]),ALL('Concept Savings Profile New'))

RETURN

    IF(
        ISINSCOPE('Concept Savings Profile New'[Month]),
        CurrentMonth - SumMinMonth,
        TotalValue - SumMinMonth
    )

I've then used another measure for SUMX against the Baseline Month Movement New to correct the overall summed total which now displays 10,633.54

Baseline Month Swing New = SUMX(VALUES('Concept Savings Profile New'[Month]),[Baseline Month Movement New])

This returns the following which is correct:

I've then tried to produce a cumulative sum DAX formula against the [Baseline Month Swing New] measure and it's almost right, it's just producing a cumulative sum against the Value total and missing the 1st month. What I'd like is the Baseline Monthly Swing column to be the value of the cumulative sum, showing the change month on month and ending with the 10,633.54 value for 01/09/2024. Here is the code I've used for the cumulative sum:

New Cumulative 2 = 

VAR DateMax = MAX('Concept Savings Profile New'[Month]) VAR _Table = FILTER(ALLSELECTED('Concept Savings Profile New'),[Month] <= DateMax) 

RETURN 

SUMX(_Table,[Baseline Month Swing New])

And here is the output currently which shows the incorrect value:

In excel it would look like this guys (red column is current, green column is desired):

Month	Sum of Value	Baseline FAST-P Cost (New)	Baseline Month Swing New	New Cumulative 2	Correct Cumulative
01/10/2023	7,798.53	7,798.53	-	-	-
01/11/2023	10,935.95	7,798.53	3,137.42	10,935.95	3,137.42
01/12/2023	9,056.50	7,798.53	1,257.97	19,992.44	4,395.38
01/01/2024	8,862.18	7,798.53	1,063.65	28,854.62	5,459.03
01/02/2024	10,122.73	7,798.53	2,324.20	38,977.35	7,783.23
01/03/2024	8,874.40	7,798.53	1,075.87	47,851.75	8,859.10
01/04/2024	7,632.78	7,798.53	-                                   165.75	55,484.53	8,693.35
01/05/2024	7,993.73	7,798.53	195.20	63,478.26	8,888.55
01/06/2024	8,942.65	7,798.53	1,144.12	72,420.91	10,032.67
01/07/2024	8,425.53	7,798.53	627.00	80,846.44	10,659.67
01/08/2024	8,567.26	7,798.53	768.73	89,413.70	11,428.41
01/09/2024	7,003.67	7,798.53	-                                   794.86	96,417.37	10,633.54

Any assistance you can offer would be massively appreciated.

[Anonymous · Accepted solution]

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.

[patem2024]

@Anonymous  thank you so much for this it works like a charm, I really appreciate your help.

Also thanks to @MFelix  and @Sahir_Maharaj for taking the time to look at my query

[MFelix]

Hi @patem2024 ,

Once again I believe that the best option here and since I do not have access to any of your model, is to create a new measure to make the SUMX based on the  values you have and don't add the MAX has part of your calculation because that is what is causing your total to go wrong.

Try the following code:

NEW MEASURE = SUMX(VALUES(Table[Montth], SUM(Table[Value]) -  SUM(Table[Baseline FAST-P Cost]))

This should give you the expected result.

Once again if you are able to provide a sample file it would be appreciated because there is a lot of things that can make this go wrong from the calculations, the columns used on your visualization your relationships. and so on.

[Sahir_Maharaj]

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]

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.

[MFelix]

Hi @patem2024 ,

Measures are based in context and in the total line the values include everything you have on your fact table in this case since you are picking up the minimum and then making the sum of the value you get these hig value try the following code:

VAR MinMonth = CALCULATE(MIN('Concept Savings Profile'[Month]),ALL('Concept Savings Profile'))
VAR SumMinMonth = CALCULATE(SUM('Concept Savings Profile'[Value]),'Concept Savings Profile'[Month] = MinMonth)
VAR CurrentMonth = SUMX(ALLSELECTED('Concept Savings Profile'[Month]),SUM('Concept Savings Profile'[Value]))
RETURN

CurrentMonth - SumMinMonth

[patem2024]

Hi Miguel,

Many thanks for the offer of support, I've tried the code excert above and it provided the following:

There are no other filters applied, it's worth noting there are zero Value rows in the data set, would this make a difference to the calculation?

Working out the calculation it seems like the 1st row is the month cost x 11 (7,798.53 x 11 = 85,783.83), then the 2nd row is the same calculation of 11 months but minus the month on month difference (109,35.95 x 11 = 120,295.45, then 120,295.45 subtracted from 123,432.82 is 3,137.37.

Its the 3,137.37 month to month difference I want to show month on month to the earliest month value, then provide a cummulative sum of the month on month difference (in excel it would look like the following table):

Total Cost	Month	Baseline Month Movement	Cummlative Savings	Baseline FAST-P Cost
7,798.53	01/10/2023	-	-	7,798.53
10,935.95	01/11/2023	3,137.42	3,137.42	7,798.53
9,056.50	01/12/2023	1,257.97	4,395.38	7,798.53
8,862.18	01/01/2024	1,063.65	5,459.03	7,798.53
10,122.73	01/02/2024	2,324.20	7,783.23	7,798.53
8,874.40	01/03/2024	1,075.87	8,859.10	7,798.53
7,632.78	01/04/2024	-                                      165.75	8,693.35	7,798.53
7,993.73	01/05/2024	195.20	8,888.55	7,798.53
8,942.65	01/06/2024	1,144.12	10,032.67	7,798.53
8,425.53	01/07/2024	627.00	10,659.67	7,798.53
8,567.26	01/08/2024	768.73	11,428.41	7,798.53
7,003.67	01/09/2024	-                                      794.86	10,633.54	7,798.53

Thanks in advance for your support.

[MFelix]

Hi @patem2024 ,

The easist way is to create a secondary measure with the following syntax:

Baseline Total = 

SUMX(ALLSELECTED('Concept Savings Profile'[Month]),[Baseline Month Movement])

The other option is to use the visual calculations and do a running sum on your baseline. Be aware that this value will only be available for that specific visual, because it's a visual calculation.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview

https://learn.microsoft.com/en-us/power-platform/release-plan/2023wave2/power-bi/visual-calculations