Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 Sa...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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?

  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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.

amitchandak's avatar
amitchandak
Super User
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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?

    • V-lianl-msft's avatar
      V-lianl-msft
      Community Support
      5 years ago

      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.