Running total not working as expected in matrix

[vanya_val]

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.

[V-pazhen-msft]

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

@V-pazhen-msft  thanks for your reply. I tried the measure you suggested but it doesn't seem to accept the Date column:

[V-pazhen-msft]

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

[vanya_val]

@V-pazhen-msft 

Thank you. I'm afraid I'm getting the same issue as with the other measures: it works when plotted only against the date, but as soon as I add another column it stops working 😞

[amitchandak]

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

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