Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |