Tooltip to return top and bottom 5 results

[Wendeley-North]

Hi, would require some help in getting a tooltip to show the top and bottom 5 results correctly. I have managed to get it working in simpler instances, but it seems like once I require grouping, the complication results in a wrong aggregation. I'll share what I've done so far and hope it helps to move the process along.

Use Case

There is currently a scatter chart which plots the weighted sum of Metric1 on the y-axis, and the weights on the x-axis. Each marker here represents a sector.

To accomplish this, there are 3 measures being used:

Pct1_Weighted_Metric1 = IFERROR(ROUND(SUMX(Table, Table[Pct1] * Table[Metric1]) / SUM(Table[Pct1]),5), 0)

Pct2_Weighted_Metric1 = IFERROR(ROUND(SUMX(Table, Table[Pct2] * Table[Metric1]) / SUM(Table[Pct2]),5), 0)

Plotted_Metric1 = Pct1_Weighted_Metric1 - Pct2_Weighted_Metric1 

What these measures aim to do (since they could be wrong), is to rebase Metric1 based on the sum of Pct1/Pct 2:

• For example, a normal weighted metric could be: 0.80% x 10 + 0.25% x 12 = 8 + 3 = 12 
• Rebased would result in: (0.80% x 10 + 0.25% x 12) / (0.80% + 0.25%) = (8 + 3) / (1.05%) = ~1,048

And then take the difference between the two rebased metrics. Before we continue to the tooltip, here's some dummy data.

Dummy Data

Unfortunately, the actual data is hard to provide an accurate sample of, but I hope what is presented below gives an idea:

Grouping1	Date	ID_Num	Pct_1	ID_Str	Pct_2	Pct_3	Metric1	Country	Grouping3	Sector
Group1_Str1	31-Dec-20	ID_Num_1	2.50%	ID_Str_1	0.24%	2.27%		Country_Str1	Group3_Str1	Sector_Str1
Group1_Str1	31-Dec-20	ID_Num_2	1.60%	ID_Str_2	0.35%	1.24%		Country_Str1	Group3_Str1	Sector_Str2
Group1_Str1	31-Dec-20	ID_Num_3	4.18%	ID_Str_3	5.02%	-0.83%	3.6	Country_Str1	Group3_Str1	Sector_Str2
Group1_Str1	31-Dec-20	ID_Num_4	0.93%	ID_Str_4	6.73%	-5.79%		Country_Str2	Group3_Str1	Sector_Str3
Group1_Str1	31-Dec-20	ID_Num_5	3.88%	ID_Str_5	0.21%	3.67%	4.3	Country_Str3	Group3_Str1	Sector_Str4
Group1_Str1	31-Dec-20	ID_Num_6	0.11%	ID_Str_6	0.62%	-0.51%	3.9	Country_Str1	Group3_Str1	Sector_Str2
Group1_Str1	31-Dec-20	ID_Num_7	1.94%	ID_Str_7	5.59%	-3.66%		Country_Str1	Group3_Str1	Sector_Str5
Group1_Str1	31-Dec-20	ID_Num_8	2.18%	ID_Str_8	5.49%	-3.30%	1.8	Country_Str1	Group3_Str1	Sector_Str6
Group1_Str1	31-Dec-20	ID_Num_9	2.75%	ID_Str_9	3.54%	-0.80%	2.7	Country_Str2	Group3_Str1	Sector_Str7
Group1_Str1	31-Dec-20	ID_Num_10	2.61%	ID_Str_10	0.67%	1.94%	3.5	Country_Str1	Group3_Str1	Sector_Str4
Group1_Str1	31-Dec-20	ID_Num_11	6.46%	ID_Str_11	3.86%	2.60%	4.3	Country_Str1	Group3_Str1	Sector_Str7
Group1_Str1	31-Dec-20	ID_Num_12	3.70%	ID_Str_12	0.29%	3.41%		Country_Str1	Group3_Str1	Sector_Str8
Group1_Str1	31-Dec-20	ID_Num_13	3.85%	ID_Str_13	2.01%	1.84%	4.1	Country_Str1	Group3_Str1	Sector_Str4
Group1_Str1	31-Dec-20	ID_Num_14	2.91%	ID_Str_14	0.90%	2.01%		Country_Str1	Group3_Str1	Sector_Str3
Group1_Str1	31-Dec-20	ID_Num_15	0.52%	ID_Str_15	0.02%	0.50%	3.3	Country_Str2	Group3_Str1	Sector_Str2
Group1_Str1	31-Dec-20	ID_Num_16	6.86%	ID_Str_16	4.42%	2.44%		Country_Str1	Group3_Str1	Sector_Str4
Group1_Str1	31-Dec-20	ID_Num_17	5.75%	ID_Str_17	3.33%	2.42%		Country_Str1	Group3_Str1	Sector_Str5
Group1_Str1	31-Dec-20	ID_Num_18	5.42%	ID_Str_18	0.27%	5.15%	4.5	Country_Str2	Group3_Str1	Sector_Str3
Group1_Str1	31-Dec-20	ID_Num_19	1.56%	ID_Str_19	2.75%	-1.19%		Country_Str1	Group3_Str1	Sector_Str3
Group1_Str1	31-Dec-20	ID_Num_20	3.22%	ID_Str_20	4.64%	-1.42%	4.4	Country_Str1	Group3_Str1	Sector_Str2
Group1_Str1	31-Dec-20	ID_Num_21	5.62%	ID_Str_21	1.45%	4.17%	4.2	Country_Str2	Group3_Str1	Sector_Str9
Group1_Str1	31-Dec-20	ID_Num_22	0.95%	ID_Str_22	3.87%	-2.93%		Country_Str1	Group3_Str1	Sector_Str3
Group1_Str1	31-Dec-20	ID_Num_23	0.78%	ID_Str_23	4.07%	-3.29%		Country_Str1	Group3_Str1	Sector_Str5
Group1_Str1	31-Dec-20	ID_Num_24	5.50%	ID_Str_24	6.51%	-1.01%	4.2	Country_Str1	Group3_Str1	Sector_Str2
Group1_Str1	31-Dec-20	ID_Num_25	3.28%	ID_Str_25	3.19%	0.09%		Country_Str1	Group3_Str1	Sector_Str9
Group1_Str1	31-Dec-20	ID_Num_26	3.90%	ID_Str_26	6.17%	-2.27%	3.4	Country_Str1	Group3_Str1	Sector_Str9
Group1_Str1	31-Dec-20	ID_Num_27	0.51%	ID_Str_27	4.58%	-4.07%	3.1	Country_Str1	Group3_Str1	Sector_Str2
Group1_Str1	31-Dec-20	ID_Num_28	1.65%	ID_Str_28	6.29%	-4.64%	5.3	Country_Str1	Group3_Str1	Sector_Str7
Group1_Str1	31-Dec-20	ID_Num_29	4.70%	ID_Str_29	5.62%	-0.92%	3.7	Country_Str1	Group3_Str1	Sector_Str9
Group1_Str1	31-Dec-20	ID_Num_30	10.19%	ID_Str_30	7.29%	2.89%		Country_Str1	Group3_Str1	Sector_Str4
Group1_Str2	31-Dec-20	ID_Num_27	25.00%	ID_Str_27	4.58%	20.42%	3.1	Country_Str1	Group3_Str1	Sector_Str2
Group1_Str2	31-Dec-20	ID_Num_28	25.00%	ID_Str_28	6.29%	18.71%	5.3	Country_Str1	Group3_Str1	Sector_Str7
Group1_Str2	31-Dec-20	ID_Num_29	25.00%	ID_Str_29	5.62%	19.38%	3.7	Country_Str1	Group3_Str1	Sector_Str9
Group1_Str2	31-Dec-20	ID_Num_30	25.00%	ID_Str_30	-4.16%	29.16%		Country_Str1	Group3_Str1	Sector_Str4

Tooltip

If the measures presented in the use case were correct (they seem to be), then the charts are fine and dandy. But the tooltips are definitely not. I'm trying to get the tooltip to return the top and bottom ID_Str and Pct1 for the sectors upon mouseover. To do this, I use a measure to create flags, and then put it inside the filter:

Top_Ranker = RANKX ( Table, Plotted_Metric1],, DESC )

Bottom_Ranker = RANKX ( Table, Plotted_Metric1],, ASC)

TopNBtm= IF([Top_Ranker ] <= 5 || [Bottom_Ranker ] <= 5, 1, 0 )

The biggest giveaway as to how I know the tooltip isn't working as intended is that it returns, for a start, more than 10 entries in some instances. 

Additional Information

Slicer Filters Present

• Grouping_1
• Grouping_3
• Date

Visual Filter Present

• Sector is not blank
• Metric1 is not blank

Would appreciate any help, thank you.

[Wendeley-North · Accepted solution]

Managed to solve it by changing the filtering options for the measures - now it uses ALLSELECTED instead of just the row context.

Will note for any future users that stumble upon this solution looking for a ranking measure: depending on your requirements, you may use all filters (i.e. the code presented in the original post), or remove all filters (i.e. using ALL(Table)) instead.

Top_Ranker = RANKX ( ALLSELECTED(Table), Plotted_Metric1],, DESC )

Bottom_Ranker = RANKX ( ALLSELECTED(Table), Plotted_Metric1],, ASC)

TopNBtm= IF([Top_Ranker ] <= 5 || [Bottom_Ranker ] <= 5, 1, 0 )