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!
FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous,

what you are looking for is ranking without ties. Here is my solution with your sample data for a calculated column. In Query editor I added a PrimaryKey column. Whatever values each value in this column is distinct.

 

 

Regards FrankAT

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi FrankAT If I use this ,other filter(slicers) is not slicing this table.So the aim is not creating any table just crate a measure on top of rank and use that in filter.