I have tried everything to exclude a 0 qty in my set analysis but nothing seems to work. this is the last 2 I tried:
1. Sum({<[Purchase Order Line Outstanding Quanity]-={0}>} [Purchase Order Line Outstanding Quanity]) 2. Sum([Purchase Order Line Outstanding Quanity]) >0
Sum({<[Purchase Order Line Outstanding Quanity]-={0}>} [Purchase Order Line Outstanding Quanity]) should be the exact same thing as:
Sum([Purchase Order Line Outstanding Quanity])
Since if this field is 0 or null, it doesn't change the sum in any way.
This doesn't mean you can't come up with a result that's 0, if there are no associated non-zero quanity[sic] or if the values add up to 0 (e.g. 4, -3, -1).
Hiding 0 rows will hide rows where all measures are 0/null, not this specific one. If you want to hide rows where this specific field is null, you can either try using dimension limits (which would require placing this measure in front of the other measures) or repeat the appropriate set for all measures in this object to ensure they wind up as 0 when appropriate.
@Dianne It seems you have many other measures in your table which is why you are not able to exclude the quantity from table. You need to apply quantity>0 condition in all your measures then only it will work
For Quantity Measure =if(Sum([Purchase Order Line Outstanding Quanity]) >0,Sum([Purchase Order Line Outstanding Quanity]) ,0)
For Unit Price Measure =if(Sum([Purchase Order Line Outstanding Quanity]) >0,Sum([Unit Price]) ,0)
likewise apply condition for all measures.
Then go to Add-Ons, and uncheck 'Include zero values' option
It seems that you don´t want to show the line in Dimension when it´s sum is 0, if you use that expression: Aggr(If(Sum( [Purchase Order Line Outstanding Quanity])>0, Dimension), Dimension), you should put it as a Dimension, not as a measure.
I think this expression will exclude the zero values Sum({<[Purchase Order Line Outstanding Quanity] = -{0} >} [Purchase Order Line Outstanding Quanity]).
