Forum Discussion
Power BI DAX Measure Total in Matrix Shows Incorrect Value to Calculate Cumulative Total
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.
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 - SumMinMonthHi 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.
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
