Create filter for Top N in various categories

[arindam23]

Hi,

I have created a set of visualizations on a large data set of list of opportunity owners and sales, etc. done by them in various product categories (there can be multiple rows with same opportunity owner and product category). I want to filter these charts to show values applicable only to top 3 opportunity owners in each category (A, B & C). Top 3 is defined by the value of a parameter (say, sum of revenue per opportunity owner within each category). 

The added complexity is that I also have a set of filters/slicers based on which the Top 3 would change. 

Hence, the way I think my requirement can be possibly achieved is by calculating a measure which is true if the opportunity owner in within the top 3 of any of the category -> putting this measure in visualizations where I want to apply it. 

Can anyone suggest if it feasible and if yes, what can be the approach?

Also, will using a dynamic table be possible in this scenario? I understood a static table can be generated but that won't serve my purpose. 

Thanks,

Arindam Mohanta

[v-juanli-msft]

Hi @arindam23

As you said, “filter these charts to show values applicable only to top 3 opportunity owners in each category (A, B & C). Top 3 is defined by the value of a parameter (say, sum of revenue per opportunity owner within each category)”

Please read steps as follows to see if it helps.

1.sum of revenue per opportunity owner within each category:

Measure1 = CALCULATE(SUM(Sheet1[sales]),FILTER(ALLEXCEPT(Sheet1,Sheet1[product categories]),[opportunity owners]=SELECTEDVALUE(Sheet1[opportunity owners])))

2.top N opportunity owners in each category:

Measure2 =
RANKX ( ALLEXCEPT ( Sheet1, Sheet1[product categories] ), [Measure1],, DESC )

3.Top3

Enter data to create a table with number 1, 2,3

Then create a measure in this new table that will hold the value that was chosen, and then we can use it in other measures’ calculations later

Measure 3 =
MIN ( 'Top'[top] )

4.Finally create a measure we can use for displaying on tables, relates to highlighting which are the Top3 based on current selections, by returning an arrow we can use as a field in the table for the Top3

Measure 4 =
IF ( [Measure2] <= [Measure 3], "->" )

As for “will using a dynamic table be possible in this scenario”, what kind of dynamic table you want to add and how to use it?