DAX to limit the listing to 5 top entries

0
votes

I intend to show top 5 customers by revenue and have created the following two measures.

Total Revenue = ROUND(SUMX(Data,  Data[Revenue])/1000000,2)

Rank of Customers = IF(NOT(ISBLANK([Total Revenue])),RANKX(FILTER(ALL(Data[Customer]),[Total Revenue]),[Total Revenue],,,Skip), BLANK())

Rank of Customers <= 5 is applied as filter in Power BI report.

While I m able to rank customers correctly, number of entries shown exceed 5 when the total revenue is same.

I m new to DAX and need help to correct this expression in order to restrict number of entries to 5. CustomerName may be used for ordering after ranking

Thanks in advance.

1
excelexcel-formulapowerbidaxpowerquery

1 Answers

0
votes

The number of entries shown exceeds 5 because that's exactly how RANKX is supposed to work. It only has control over the rank but not the number of rows.

If you want to limit the number of rows, you can use the TOPN function instead.

Top 5 Customers = TOPN(5, Data, [Total Revenue], DESC, Data[CustomerName], ASC)

Note that this will return a table, instead of a value.

You can then display the table as you like: result