Forum Discussion
How to filter a Matrix Visual based on a Cumulative Sum
CONTINUED
loaded into a table like this:
I'd like to filter this matrix to only show items where the cumulative sum TOTAL is <0. I've tried filtering by where the Cumulative Sum measure is <0, but this filters the table to only calculate on transactions where the quantity is <0. Does anyone know how to do this?
Hi Anonymous ,
There should be no problem with the method, but the order of the conditions is reversed.
Quantity running total in Date = var _cumm = CALCULATE( SUM(Stock[Quantity]), FILTER( ALLSELECTED(Stock[Date]), ISONORAFTER(Stock[Date], MAX(Stock[Date]), DESC) ) ) ) return IF(_cumm<0,BLANK(),_cumm))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi amitchandak, thank you for the reply, but this formula doesn't seem to get the result I'm looking for. Using this formula, now the table only shows the days with a negative cumulative value and the Total has disappeared from the visual:
Do you have any other ideas on how to achieve this?
Hi Anonymous ,
There should be no problem with the method, but the order of the conditions is reversed.
Quantity running total in Date =
var _cumm =
CALCULATE(
SUM(Stock[Quantity]),
FILTER(
ALLSELECTED(Stock[Date]),
ISONORAFTER(Stock[Date], MAX(Stock[Date]), DESC)
)
)
)
return IF(_cumm<0,BLANK(),_cumm))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.