Forum Discussion
Cumulative total while switching values
Hi Guys,
My Data looks like this and I need to show it in a matrix visual with a cumulative sum of values. Please note that there is a slicer filter at the top that switches the column values between Month_num and Month.
Input
| Country | Value | Month_num | Month |
| US | 100 | 1 | 1/1/2022 |
| US | 120 | 2 | 2/1/2022 |
| US | 140 | 3 | 3/1/2022 |
| US | 110 | 4 | 4/1/2022 |
| US | 145 | 5 | 5/1/2022 |
| US | 180 | 6 | 6/1/2022 |
| US | 170 | 7 | 7/1/2022 |
| Germany | 140 | 1 | 4/1/2022 |
| Germany | 150 | 2 | 5/1/2022 |
| Germany | 160 | 3 | 6/1/2022 |
| Germany | 170 | 4 | 7/1/2022 |
| Germany | 180 | 5 | 8/1/2022 |
| Germany | 130 | 6 | 9/1/2022 |
| Germany | 140 | 7 | 10/1/2022 |
Output 1 - Wheb Slicer value is selected as Month
| Month | 1/1/2022 | 2/1/2022 | 3/1/2022 | 4/1/2022 | 5/1/2022 | 6/1/2022 | 7/1/2022 | 8/1/2022 | 9/1/2022 | 10/1/2022 |
| US | 100 | 220 | 360 | 470 | 615 | 795 | 965 | |||
| Germany | 140 | 290 | 450 | 620 | 800 | 930 | 1070 |
Output 2 - When the slicer value is selected as Month_num
| Month_num | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| US | 100 | 220 | 360 | 470 | 615 | 795 | 965 |
| Germany | 140 | 290 | 450 | 620 | 800 | 930 | 1070 |
Can someone please help me to achieve this?
Thanks,
Anuj
Hi itsmeanuj
If I understood you correctly and you are working with the field parameters you can apple the following steps :
1. Add to the field parameter column of the slicer's selections ( you will need it at the dax that will switch the formula according to your selection)2. Use this formula for the calculation of the running total :
runming total =if(SELECTEDVALUE(Parameter[Month type])="month_num",CALCULATE(
SUM('Table'[Value]),
FILTER( ALLSELECTED('Table'[Month_num]),
'Table'[Month_num]<= MAX(('Table'[Month_num]) )
)),CALCULATE(
SUM('Table'[Value]),
FILTER( ALLSELECTED('Table'[Month]),
'Table'[Month]<= MAX(('Table'[Month]) )
))
)3. On the slicer, use the new column from step 14. Create the matrix (parameter should be on columns)
Result:
PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi itsmeanuj
This logic will work for any date format according to your needs, everything is dynamic in my formulas.
I changed your format just because my settings are dd/mm/yyyy , and I didn't want to reset them just for the sample solution 🙂If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
pls try this
4 Replies
Hi itsmeanuj
If I understood you correctly and you are working with the field parameters you can apple the following steps :
1. Add to the field parameter column of the slicer's selections ( you will need it at the dax that will switch the formula according to your selection)2. Use this formula for the calculation of the running total :
runming total =if(SELECTEDVALUE(Parameter[Month type])="month_num",CALCULATE(
SUM('Table'[Value]),
FILTER( ALLSELECTED('Table'[Month_num]),
'Table'[Month_num]<= MAX(('Table'[Month_num]) )
)),CALCULATE(
SUM('Table'[Value]),
FILTER( ALLSELECTED('Table'[Month]),
'Table'[Month]<= MAX(('Table'[Month]) )
))
)3. On the slicer, use the new column from step 14. Create the matrix (parameter should be on columns)
Result:
PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Ritaf1983 - Thanks for your response on this. I realized that in the sample data, you have changed the dates of Germany. US data is from Jan-22 to July 22. Germany is from April 22 to Oct-22. In the actual data, there are a lot more countries with different starting dates. will this solution still work in that scenario?
Hi itsmeanuj
This logic will work for any date format according to your needs, everything is dynamic in my formulas.
I changed your format just because my settings are dd/mm/yyyy , and I didn't want to reset them just for the sample solution 🙂If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
pls try this
