Forum Discussion
Help with bottom N negative results
Ah, ok, my bad. It's evaluating the rank first, then filtering afterwards.
You'll need to apply the filter prior to each rank evaluation, something like this:
VAR _topItem = RANKX( //Filter table rank is evaluated over FILTER( ALL( Part_Sales[Item] ), [Total Sales] >= 0 ), [Total Sales], , DESC )Check whether this one works as required and, if it does, you'll need to apply a similar filter to each evaluation table in your rank variables.
Pete
Cool, thanks.
You should just be able to exclude these values right at the end of the measure, something like this (assuming you also don't want to keep negative values when selecting TOPN):
RETURN
IF(
_RankItemSales <= _TopN && [Total Sales] >= 0,
[Total Sales]
)
Pete
This is great.
Are we able to exclude 0 value as well?
I tried removing the = sign but apparently it does not work.
Hi Chris_68 ,
This should work:
RETURN IF( _RankItemSales <= _TopN && [Total Sales] > 0, // Removed =, like you tried already? [Total Sales] )If removing the = does not work, then I suspect you have very small numbers in your sales data that are getting past the filter e.g. 0.00001 etc.
There's two basic ways to handle this:
1) Truncate/round all of your source sales data to two decimal places IN POWER QUERY (Number.Round), or earlier in the pipeline (SQL Server etc.) if you can. Making this change in the front-end/model will not have the desired effect as this will just change how the values are displayed, not the underlying values themselves.
2) Based on the materiality of these fractional values, you could potentially adjust the filter slightly to && [Total Sales] > 0.001 or similar, but this is not the preferred option.
Pete
Hi Pete,
You're right—it may be working, but it looks like entries with a value of 0 aren't showing in the table. For example, when I select Top 10, nothing appears. But with Top 20, only 8 customers show up.
Would appreciate any suggestions you have.
Ah, ok, my bad. It's evaluating the rank first, then filtering afterwards.
You'll need to apply the filter prior to each rank evaluation, something like this:
VAR _topItem = RANKX( //Filter table rank is evaluated over FILTER( ALL( Part_Sales[Item] ), [Total Sales] >= 0 ), [Total Sales], , DESC )Check whether this one works as required and, if it does, you'll need to apply a similar filter to each evaluation table in your rank variables.
Pete