Forum Discussion
Top N
Hello:
This is my data:
Facility Products Revenue
A Product 1 5,510K
Product 2 4,185K
Product 3 2,770K
Product 4 1,607K
B Product 1 6,725K
Product 4 1,570K
Product 5 1,360K
Product 6 (890)K
C Product 1 (2,632)K
Product 7 2,267K
Product 8 1,249K
Product 9 5,510K
What I would like to see is -
Each facility sorted A to Z by Facility name.
Within each Product category - Bottom 3, sorted by ascending Revenue / so the one with least revenue is first on the list
Product category can repeat between Facilities. But should be sorted for each facility independently.
I have tried using a table matrix -
Facility and Products in ROWS, and Revenue in Values
Filtered Products by Top N, Bottom 3, By Revenue.
What happens is - if its just one Facility selected - the output is correct
But if all Facilities are selected the ranking is not correct within Product.
Then, I used RANKX - RankProduct = RANKX(all([Product]),calculate(sum[Revenue])),,DESC) - and had the same issue where Product ranking was not correct.
There are a few null values - so the other condition is NOT(ISBLANK) on Product and Revenue.
Any input and help is greatly appreciated.
Thank you
Vadivu.
Hi, Anonymous
try to create a measure like this:Rank Product = IF( OR(SELECTEDVALUE('Table'[Products])=BLANK(),SELECTEDVALUE('Table'[Revenue])=BLANK()), BLANK(), RANKX( FILTER(ALL('Table'),'Table'[Products]<>BLANK()&&'Table'[Revenue]<>BLANK()&&'Table'[Facility]=MAX('Table'[Facility]) ) ,CALCULATE(SUM('Table'[Revenue])),,ASC,Dense) )Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous You're on the right track with RANKX, just need a bit more info. This blog should be exactly what you want, just use Ascending instead of Desc.
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
Hi, Anonymous
try to create a measure like this:Rank Product = IF( OR(SELECTEDVALUE('Table'[Products])=BLANK(),SELECTEDVALUE('Table'[Revenue])=BLANK()), BLANK(), RANKX( FILTER(ALL('Table'),'Table'[Products]<>BLANK()&&'Table'[Revenue]<>BLANK()&&'Table'[Facility]=MAX('Table'[Facility]) ) ,CALCULATE(SUM('Table'[Revenue])),,ASC,Dense) )Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous Can you please let us know if your issue is resolved by marking one of the posts as solution? This ensures others can benefit and it allows us to free up resources to focus on unresolved posts.
Thanks!
