Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Row number over rank column

I need to find top 10 sales as per Brand  and Item.so consider in power bi table i have 3 column Brand ,Item ,sales. Now i have created a rank measure using rankx function,now the problem is 7 record...
General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ，

     

    Create 2 measures as below:

     

    Measure = 
var rank1=RANKX(ALL('Table'),CALCULATE(SUM('Table'[sales])),,DESC,Dense)
Return
RANKX(ALL('Table'),rank1+RANKX(ALL('Table'),CALCULATE(MAXX('Table','Table'[brand]&'Table'[item])),,ASC,Dense)/COUNTROWS(ALL('Table')),,ASC,Dense)
    _TOP 10 = IF('Table'[Measure]<=10,'Table'[Measure],BLANK())

     

    Then you will see:

    For yhe related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,Thank You.. But is it possible without creating any calculated column only we can create measure. because table level filter also need to keep and that time topn wont work.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ，

 

Create 2 measures as below:

 

Measure = 
var rank1=RANKX(ALL('Table'),CALCULATE(SUM('Table'[sales])),,DESC,Dense)
Return
RANKX(ALL('Table'),rank1+RANKX(ALL('Table'),CALCULATE(MAXX('Table','Table'[brand]&'Table'[item])),,ASC,Dense)/COUNTROWS(ALL('Table')),,ASC,Dense)
_TOP 10 = IF('Table'[Measure]<=10,'Table'[Measure],BLANK())

 

Then you will see:

For yhe related .pbix file,pls click here.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous  Thanks.