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.
Hello.
This issue is very simple to describe, but I've exhausted all I know on how to address it within Query or Dax.
I have a dataset. For simplicity, let's say it looks like this:
APPLES, $25
ORANGES, $23
GRAPEFRUIT, $11
BANANAS, $10
PEACHES, $5
DRAGONFRUIT, $4
ALL OTHER, $22
(Notice they all add up to 100, thus, each number happens to also be its percent share).
So if you throw all these into a pie chart (or for me, the custom visual "Drill Down Donut Chart" becuase I like how it presents data), as expected, you get the aforementioned items, with the proper percentages.
"All other" as a category is unhelpful for reporting. It crowds the other values and unfortunately, our data source cannot break down "all other" any further. It is what it is.
If I take "All other" out with a filter, I'm faced with the problem of the percentages changing. Instead of 100 as a denominator, 78 is now the denominator. All the percentages go up.
I need to take "All other" out without the percentages changing.
How do I do this?
1.) If I could split "all other" into a number of small categories like "All Other A" and "All Other B"...."All Other Z" it would make them all very small and I wouldn't need to filter out the value. I'm not sure how to split a row and rename one field in this way via Power Query.
2.) Perhaps there is a way through dax or query to recalculate ALL the values so that when I filter out "All Other" the percentages are consistent. I've tried a few things both on paper and in Dax & Power Query, and I'm not sure this is mathematically possible. If I could split the value of "All Other" into a number of dummy data rows, this may work, but it's similar to what I postulate in #1.
Thank you.
Solved! Go to Solution.
Hey @jlankford I have just checked, and --at least in the free version of-- Donut Chart by ZoomCharts there is no option to select how the Data Labels are displayed.
In a normal donut / pie chart though, there is.
I have tested this measure:
CostPerc = VAR y = SUM ( [Value] ) + 0 VAR x = CALCULATE ( SUM ( [Value] ), ALL ( Table ) ) RETURN DIVIDE ( y, x, 0 )
In modeling, you can choose to display it as a percentage. This measure always calculates the complete total value, thus even if you filter out one or more IDs the other IDs' percentage won't change.
Thank you very much! I just realized that I was making this WAY harder than it had to be. By calculating share of total, and then using that value independently of the "percentage" calculated by the pie, I simply display values.
Hey @jlankford I have just checked, and --at least in the free version of-- Donut Chart by ZoomCharts there is no option to select how the Data Labels are displayed.
In a normal donut / pie chart though, there is.
I have tested this measure:
CostPerc = VAR y = SUM ( [Value] ) + 0 VAR x = CALCULATE ( SUM ( [Value] ), ALL ( Table ) ) RETURN DIVIDE ( y, x, 0 )
In modeling, you can choose to display it as a percentage. This measure always calculates the complete total value, thus even if you filter out one or more IDs the other IDs' percentage won't change.
Thank you very much! I just realized that I was making this WAY harder than it had to be. By calculating share of total, and then using that value independently of the "percentage" calculated by the pie, I simply display values.
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 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |