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.
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 )
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?