Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Issue with TOPN and RANKX with no ties

I have a table visual and it has multiple columns present in it. I am trying to get the TOPN based on a particular column, even after there are no ties, it shows more than the required number. I have...
DAX
POWER BI Dax rules
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    The field [E] is a fact field in the table or a measure? It is a measure, could you please provide its formula? Please create a measure as below and check if you can get the correct rank values...

     

    RANKING = RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( 'Table'[E] ) ),,, DENSE )

     

    In addition, you can refer the following threads to solve your problem.

    RANKX Always Returns 1

    Rank Product =
RANKX (
    ALL ( MyData[Product] ),
    CALCULATE ( SUM ( MyData[Quantity] ), ALLEXCEPT ( MyData, MyData[Product] ) )
)

    Rankx all return 1

    Total Sales = SUM ( sales[sales amount] )

SalesRank = RANKX ( ALLSELECTED ( sales[employee number] ), [Total Sales] )

     

    If the above one can't help you get the desired result, please provide some raw(fake) data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Sorry was unable to reply due to max limits on private message.

 

Please find the screenshot below for the measure I have generated.

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous ,

The field [E] is a fact field in the table or a measure? It is a measure, could you please provide its formula? Please create a measure as below and check if you can get the correct rank values...

 

RANKING = RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( 'Table'[E] ) ),,, DENSE )

 

In addition, you can refer the following threads to solve your problem.

RANKX Always Returns 1

Rank Product =
RANKX (
    ALL ( MyData[Product] ),
    CALCULATE ( SUM ( MyData[Quantity] ), ALLEXCEPT ( MyData, MyData[Product] ) )
)

Rankx all return 1

Total Sales = SUM ( sales[sales amount] )

SalesRank = RANKX ( ALLSELECTED ( sales[employee number] ), [Total Sales] )

 

If the above one can't help you get the desired result, please provide some raw(fake) data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards