Forum Discussion

itsmeanuj's avatar
itsmeanuj
Helper IV
2 years ago
Solved

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 bet...
Need Help
Show and Tell
Tips and Tricks
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    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's avatar
    Ritaf1983
    2 years ago

    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