Forum Discussion

vanya_val's avatar
vanya_val
Frequent Visitor
6 years ago

Running total not working as expected in matrix

Hi all,

 

I have a running total measure which seems to work fine when plotted against a date only, however when I try adding it to a matrix with several additional text columns, it no longer works.

 

The requirement is to create a table or matrix listing a series of transactions with their value and running total. Apart from the date and values I also need to include several descriptive columns (Transaction Type, Account, Tech, Cohort Name). As soon as I add the descriptive columns and try listing each single transaction, the running total stops working.

 

In the screenshots I’ve shared below:

 

Value: the value of each single transaction (the actual column is 'Transactions Table'[Cohort])

 

Cumulative: the running total of Value using this formula:

 

Cumulative = calculate(sum('Transactions Table'[Cohort]),filter(ALLSELECTED('Calendar'),'Calendar'[Date] <= max('Calendar'[Date])))

 

Total: the sum of a starting number (63 in this case) and Cumulative. I think this will work if Cumulative works.

 

The measure Cumulative works fine when plotted only against the Date and Value:

 

 

However, the requirement is to list every single transaction with some descriptive details. As soon as I try doing that Cumulative no longer works as a running total but instead becomes equal to Value:

 

Is there a way to change the DAX for the running total so that it displays correctly when every single item is listed?

Thanks ever so much in advance.

 
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    vanya_val , Try if this can help

    Cumulative =
    VAR _min = MINX(Allselected('Calendar'), 'Calendar'[DATE] )
    return
    calculate(sum('Transactions Table'[Cohort]),filter(ALLSELECTED('Calendar'),'Calendar'[Date] <= max('Calendar'[Date]) && 'Calendar'[Date] >=_min))

    • vanya_val's avatar
      vanya_val
      Frequent Visitor
      6 years ago

      Thanks very much amitchandak . I've added the measure you suggested as CumulativeTest2 but unfortunately I'm getting the same result as before:

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    vanya_val 

    Try with cumulative total using Earlier in the measure.

    Cumulative = calculate(sum('Transactions Table'[Cohort]),filter(all('Transactions Table'),sumx(filter('Transactions Table', earlier('Calendar'[Date])<='Calendar'[Date]),1))

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • vanya_val's avatar
      vanya_val
      Frequent Visitor
      6 years ago

      Anonymous  thanks for your reply. I tried the measure you suggested but it doesn't seem to accept the Date column:

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        vanya_val 

        My bad, should Change the filter table to Calendar table. Give it another try:

         

        Cumulative = calculate(sum('Transactions Table'[Cohort]),filter(all('Calendar'),sumx(filter('Calendar', earlier('Calendar'[Date])<='Calendar'[Date]),1))

         


        Paul Zheng _ Community Support Team
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.