Forum Discussion
awitt
Helper III
Helper IIIRANKX with String Filter
Hi,
I am looking to rank the top 5 products based on sales. This below works for all products.
Top 5 Products =
VAR ProductRank = RANKX (ALL('ePOS Bookkeeping Report'[Product]), CALCULATE(sum('ePOS Bookkeeping Report'[NET Sales]),,DESC))
RETURN
IF( ProductRank <= 5, CALCULATE(SUM('ePOS Bookkeeping Report'[NET Sales])),blank())
However, I want to add a filter statment in the calculate function so that only the products of a certain group are shown and not all products. So in the below example, this would ideally return the top 5 "Clothing" products. However all of the results generate the same rank of 1 in this case.
Top 5 Products =
VAR ProductRank = RANKX (ALL('ePOS Bookkeeping Report'[Product]), CALCULATE(sum('ePOS Bookkeeping Report'[NET Sales]),FILTER('ePOS Bookkeeping Report','ePOS Bookkeeping Report'[Category (groups)] = "CLOTHING")),,DESC)
RETURN
IF( ProductRank <= 5, CALCULATE(SUM('ePOS Bookkeeping Report'[NET Sales])),blank())
I've also tried this, which yeilds 0 results.
Top 5 Products =
VAR ProductRank = RANKX (ALL('ePOS Bookkeeping Report'[Product]), CALCULATE(sum('ePOS Bookkeeping Report'[NET Sales])),,DESC)
RETURN
IF( ProductRank <= 5, CALCULATE(SUM('ePOS Bookkeeping Report'[NET Sales]),FILTER('ePOS Bookkeeping Report','ePOS Bookkeeping Report'[Category (groups)] = "Clothing")),blank())
Thoughts?
2 Replies
- Greg_Deckler
Community Champion
awitt This might help: To *Bleep* with RANKX! - Microsoft Power BI Community
- v-easonf-msft
Community Support
Hi, awitt
You can try measure as below:
Top 5 Products of CLOTHING = VAR rank1 = RANKX ( ALLEXCEPT ( 'ePOS Bookkeeping Report', 'ePOS Bookkeeping Report'[Category (groups)] ), CALCULATE ( SUM ( 'ePOS Bookkeeping Report'[NET Sales] ) ), , DESC ) RETURN IF ( MAX ( 'ePOS Bookkeeping Report'[Category (groups)] ) = "CLOTHING" && rank1 <= 5, CALCULATE ( SUM ( 'ePOS Bookkeeping Report'[NET Sales] ) ), BLANK () )Best Regards,
Community Support Team _ Eason
