Filter by an item's total

[Anonymous]

Hi all, I have an Inventory Stock/Demand table like this:

That accurately displays the Running Total by Item using a Running Total measure.  How can I filter this table via DAX on items where the Running Total <0?  Currently, if I filter the table by the Running Total measure <0, it filters the table down to individual negative transactions.  What I need is some sort of flag where it only shows items where the Total of the transactions is <0.

[V-pazhen-msft]

@Anonymous 

Try create the measure : 

Measure = 

IF( Calculate([running total measure],Allselected('Table'))<0, [running total measure], Blank())

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.

[Anonymous]

Thanks @V-pazhen-msft, but unfortunately this did not work.

Thinking about this differently, would this work better to create a separate calculated table with just the items where the total <0?  If so, what DAX formula could I use to create this?

[Fowmy]

@Anonymous 

If you do not want to filter the rows for negative values, how do you want to show it? Can you provide and example?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks for the reply - I'm looking to filter the items where the Total is negative, not the individual values.  In the image provided, there are items where the Total (all the way to the right) are <0 - I want to exclude items where the Total is >0 from appearing in the table.

[Fowmy]

@Anonymous 

Modify the below meaure according to your model

Revised Measure = 

IF( NOT ISFILTERED(TABLE[DATES]) , 
    IF( [Measure] <=0 ,[Measure] , BLANK())
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Fowmy]

@Anonymous 

I am not sure how your measures are built and the data model. ca you share a PBIX file with some dummy data?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

[Anonymous]

Thanks again for the reply - unfortunately this measure resulted in all values = null or 0.