Calculate ranks dynamically based on Slicer selection.

[melvincv]

I have a table with the columns:

Customer No.

Revenue

Product Line

- Class

-- Item

Country

State

City

There is a product hierarchy like this - Product Line > Class > Item

I need to display the list of the top n customers by revenue.

If we select any particular product in the slicer, it should show the top n customers in that product category.

Similarly, for the country, state and city.

We are using a slicer for the no. of top customers - the n value.

Right now, we are trying to do this using RANKX and Index column, but it is not working as expected.

Eg:

We enter 100 in the n value slicer

If we select any product in the hierarchy, it should show the top 100 customers in that category.

It could be a product line / class / item. Or even a Country, State or City.

As of now, we are able to get the top 100 customers using Index and Rank. But while we try to apply the slicers, it filters the customers that are falling under that category from the top 100 selected earlier.

Please help us at the earliest...