cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
arindam23 Frequent Visitor
Frequent Visitor

Create filter for Top N in various categories

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

1 REPLY 1
Community Support Team
Community Support Team

Re: Create filter for Top N in various categories

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 )

2.png

 

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], "->" )

3.png

4.png

 

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?