Forum Discussion
Super UserBottom N without blanks and zeros - error
Hi to all,
I was answering the post below related with getting the bottom 5 values with sales greater than 0:
https://community.powerbi.com/t5/Desktop/Bottom-5/m-p/647393#M310363
When put on the the filter on the table for the greater than 0 and bottom 5 for sales the table comes out blank.
If I add a measure with the following code and use it on my bottom N it works correctly:
Sales Positive = CALCULATE(SUM(Sheet1[Sales]); Sheet1[Sales] > 0)
As someone else got this error.
LUCASM added a file with the issue on the post.
Regards,
MFelix
Hi MFelix,
The two conditions in Visual Level Filter operates with "AND" logic. For the first condition, bottom 5 of sum(sales) it returns rows which has sum(sales) equals to 0. While the second condition is return sum(sales) when they are greater than 0. It's obviously that there is no common rows, so it return nothing in the table visual.
To resolve the issue, you need to create a measure like below, then set bottom 5 based on this measure:
Measure = CALCULATE(SUM(Sheet1[Sales]),FILTER('Sheet1','Sheet1'[Sales]>0 &&Sheet1[Sales]<>BLANK()))Best Regards,
Qiuyun Yu
1 Reply
v-qiuyu-msftCommunity Support
Hi MFelix,
The two conditions in Visual Level Filter operates with "AND" logic. For the first condition, bottom 5 of sum(sales) it returns rows which has sum(sales) equals to 0. While the second condition is return sum(sales) when they are greater than 0. It's obviously that there is no common rows, so it return nothing in the table visual.
To resolve the issue, you need to create a measure like below, then set bottom 5 based on this measure:
Measure = CALCULATE(SUM(Sheet1[Sales]),FILTER('Sheet1','Sheet1'[Sales]>0 &&Sheet1[Sales]<>BLANK()))Best Regards,
Qiuyun Yu