cancel
Showing results for
Search instead for
Did you mean:
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
Highlighted
Community Support Team

## Re: Create filter for Top N in various categories

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?