Forum Discussion
Bottom N showing more than specified value
Hi,
I'm using the bottom N filter on a bar graph to show the least preforming entities(shops/profit).
However I only want to filter bottom N by shops that is not 0.
I tried using a filter that says must be above 0 but it only shows 6 entites of shops and filters out the other 4 due to their value being 0.
For example in the below table i would not like to show the shop that is 0 on profit but would still want the bottom 3
E.g
| shop id | profit |
| shop 1 | £500 |
| shop 2 | £400 |
| shop 3 | £100 |
| shop 4 | 0 |
OsmanEmi , Create measure like
profit measure =sumx(values(Table[shop id]), if(calculate(sum(Table[profit]))>0,calculate(sum(Table[profit])), blank()))
bottom profit measure = CALCULATE([profit measure],TOPN(10,all(Table[shop id]),[profit measure],asc),VALUES(Table[shop id]))
1 Reply
- amitchandak
Super User
OsmanEmi , Create measure like
profit measure =sumx(values(Table[shop id]), if(calculate(sum(Table[profit]))>0,calculate(sum(Table[profit])), blank()))
bottom profit measure = CALCULATE([profit measure],TOPN(10,all(Table[shop id]),[profit measure],asc),VALUES(Table[shop id]))