Forum Discussion

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago
Solved

Bottom 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   Wh...
Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    7 years ago

    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 

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
7 years ago

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