Grouping small values

Kramfors

New Member
Joined
Mar 3, 2013
Messages
4
Hi all,

I have the following Pivot with a filter on the Region and Slicers on the Year and Product Category:

Region
Sales
% of Sales
Europe60,00059.6%
North America30,00029.8%
Asia10,0009.9%
South America5000.5%
Africa1000.1%
Grand Total
100,000100%

<tbody>
</tbody>

I am trying to group the small values into a Region called "Other" using DAX, so I can create a pie chart with only the largest regions.

I tried the following DAX formula without success:
Code:
=IF(DimSales[% of Sales]<10%,"Other",DimRegion[Region])

I cannot just add another column in my database as I use slicers to filter my Pivot, so I need the "Other" Region to be created on the fly, and not statically stored in the database.

Do you have any ideas of how I could perform this?
Thanks for your help!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
This needs to be done with a disconnected table to make it dynamic. Add a table to you PowerPivot model with 2 columns. One that lists every Region plus "Other" and a second that has "1" for every region name and "0" for the "Other" row. I will just refer to this table as 'Table2'. Do not relate it to anything.

Region
Above 10 Pct
Europe
1
North America
1
Asia
1
South America
1
Africa
1
Other
0

<tbody>
</tbody>


After that you need 3 measures:

[10 Pct Test]:= IF([% of Sales]>=.1,1,0)

This just checks to see if the [% of Sales] is above or below 10%.

[Region Above 10 Pct]:=IF(HASONEVALUE('Table2'[Region]), CALCULATE([% of Sales], FILTER('DimRegion', [10 Pct Test] = MIN('Table2'[Above 10 Pct]) && 'DimRegion'[Region] = VALUES('Table2'[Region]))),BLANK())

This calculates just the [% of Sales] for Regions above 10%.

[Final % of Sales]:=IF(HASONEVALUE('Table2'[Region]), IF(VALUES('Table2'[Region]) = "Other", CALCULATE([% of Sales], FILTER('DimRegion', [10 Pct Test]=MIN('Table2'[Above 10 Pct]))), [Region Above 10 Pct]), BLANK())

The first part of this measure groups everything under 10% into "Other" and if it is over 10%, it returns the value of [Region Above 10 Pct]. You can replace [%of Sales] with [Sales] (or any other measure) in the 2nd 2 measures and get the same type of grouping.

The final result looks like this (use the Region field from the new disconnected table in Rows):

Row Labels
Final Sales
Final % of Sales
Europe
$60,000.00
59.60 %
North America
$30,000.00
29.80 %
Other
$10,600.00
10.50 %

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top