Rank function: How to Display Only Specific Ranks row

[gyong]

Hello everyone,

I want to create a function that assigns ranks to the quantity of categories purchased by customers and displays only the highest quantity (1st rank) among them.

I have calculated the rank of the quantity of categories purchased by customers using the following function.

=IF(HASONEVALUE('tab1'[category code]),
RANKX(ALL('tab1'[category name]), 'tab1'[sum of quantity]),
BLANK()
)

Is there a way to display only the 1st rank? The ultimate goal is to use this measurement to display the customer's order quantity and which categories they mainly purchase in a pivot table.

thank you all.

[amitchandak · Accepted solution]

@gyong , use index function

calculate( Sum(Table['tab1'[sum of quantity]), keepfilters( INDEX(1, ALLSELECTED('tab1'[category name],'tab1'[Customer name]), ORDERBY(Sum(Table['tab1'[sum of quantity]),desc),, partitionBy ('tab1'[Customer name]))))

refer

Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

[rajendraongole1]

Hi @gyong - To calculate the ranks the quantities of categories purchased by customers and then displays only the highest quantity (1st rank) in a pivot table

create below measure for the rank of the categories, you can replace the table and columns names as per your model.

CategoryRank = IF(
HASONEVALUE('tab1'[category code]),
RANKX(
ALL('tab1'[category name]),
[SumOfQuantity],
,
DESC,
DENSE
),
BLANK()
)

create another measure to find the highest category rank

TopCategoryQuantity = IF(
[CategoryRank] = 1,
[SumOfQuantity],
BLANK()
)

the above approach helps to displaying the customer's order quantity and the main categories they purchase in a pivot table with hightest category wise.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!