Pie Chart visual - combine low values into a single group.

[Anonymous]

Hello all, I'm having difficulty trying to impliment a seemingly easy request. I researched a bit and there are two other posts that are very similar to my problem, but I cannot get it to work with my data 
(https://community.powerbi.com/t5/Desktop/Group-Smaller-Slices-with-condition-in-Pie-Charts/m-p/73655...)

(https://community.powerbi.com/t5/Desktop/Dynamic-Pie-chart-by-grouping-smaller-groups-under-quot-Oth...).

Here is an example of what my data looks like: 

and in pie chart form: 

The values would be the numeric data, not the percentages. (Grand Total%) However, the grouping I would like to do is based off the percentages. So anything less than 5% is grouped into "Others" 

I got this logic working with measures, but was unable to actually put the measures into the legend of the pie chart, as I understand it, that requires columns. However, I'm not sure how to do this logic with a column since the Grand Total % requires a grand total number, which isn't obtained via a column at the row level. 

I'd appreciate any help! 

[amitchandak]

@Anonymous , Binning is one option, but I doubt that will that dynamic.

We can create a bucket on % but merging name and % would be difficult.

we can have a static % based on overall or last year data in dimension and then create a new column

New column = divide(Sumx(realtedTable(Fact), Fact[Value]) , sum(Fact[Value]))

then a new column

Cat=  if([New Column] <.5 , "Other", [Category])

[Anonymous]

@amitchandak appreciate your response. 

Unfortunately, I don't believe that this method will work. When I attempt to make the "New Column" as you have it, the entire column's values is just 100%. I think this is because SUMX and the SUM are grabbing the same value in the row and dividing it by the same number. 

Unless you had any other ideas, I'm trying to just think of alternatives and tell my client this is not possible as requested. 

[amitchandak]

@Anonymous , if they are related, I expect sumx give a different result. We can also try like

Sumx(filter(Fact, Fact [Dim ID] = Dim[Dim ID]), Fact[Value])

we need to use the correct join column

[Anonymous]

@amitchandak I did some playing around with your original formula and I think I understand your logic, here is what I used: 

DIVIDE( value, SUM(value)) 

This divided the row value by the sum of all values, which is close to what I want. The next problem is that I while its correctly taking the total of all rows, the exhibit I am making is only looking at a portion. When I applied this filter to the visual in the report, these numbers are still basing the percents off the total instead of the filtered amount. So I altered the DAX to account specifically for this filter. 

CALCULATE( 
DIVIDE ( SUMX( table, value), SUM(value))
,category = "filter"
)
Logically, this should produce close to the percentage i'm looking for, but again its the SUMX thats messing up. I cannot just put the "value" as the first item inside CALCULATE, I have to wrap it in something.