Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Visual Filter Present
Would appreciate any help, thank you.
Solved! Go to 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 )
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |