Filtering a value in pie chart without changing other value's percentages

[jlankford]

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.

[Smauro · Accepted 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.

[Smauro · Accepted 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.

[jlankford · Accepted solution]

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.