Forum Discussion
Power BI DAX Measure Total in Matrix Shows Incorrect Value to Calculate Cumulative Total
- Anonymous1 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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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.
- patem20241 year agoFrequent Visitor
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