Cumulative total while switching values

[itsmeanuj]

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

[Ritaf1983 · Accepted solution]

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 1

4. 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 · Accepted solution]

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

[Ahmedx · Accepted solution]

pls try this

[Ritaf1983 · Accepted solution]

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 1

4. 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

[itsmeanuj]

@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?

[Ritaf1983 · Accepted solution]

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