Forum Discussion
How to filter a Matrix Visual based on a Cumulative Sum
Hi all, I have a table of data like this called 'Stock':
| ID | Item | Date | Site | Type | Quantity |
| Inventory | 1003706 | 09/14/20 | WARM1 | Inventory | 14,636 |
| 3010209974 | 1003706 | 09/14/20 | WARM1 | Sales Order | -10,000 |
| 100017032 | 1003706 | 09/23/20 | WARM1 | Production Orders | 49,000 |
| 3010210118 | 1003706 | 09/14/20 | WARM1 | Sales Order | 1,000 |
| 3010210277 | 1003706 | 09/15/20 | WARM1 | Sales Order | 1,500 |
| 3010210355 | 1003706 | 09/15/20 | WARM1 | Sales Order | 12,000 |
And a measure to calculate the cumulative sum of this data:
Quantity running total in Date = CALCULATE( SUM(Stock[Quantity]), FILTER( ALLSELECTED(Stock[Date]), ISONORAFTER(Stock[Date], MAX(Stock[Date]), DESC) ) ) ) |
Which is loaded into a matrix table like this:
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.
4 Replies
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?
Anonymous ,
Try like
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 , then _cumm else blank())
If it works if may have some impact on the grand total so switch formula based on isfiltered or hasonevalue
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.
