Sorting PowerPivot tables for top 10 customers

cvindasi

New Member
Joined
Sep 11, 2014
Messages
2
Hi - I am trying to generate management reports with data rows by insurance comany, revenue, payment and customer count. There are 50+ insurance companies. Is there anyway I can sort by top 10 customers and summarize the rest into one line based on customer count?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Ugh, I know I have seen this somewhere, but don't remember where. Google around a bit.

If you didn't care about "other", you would just use the pivot table filters (there is a drop down where you can choose top 10, by whatever).

Here, I think you are going to end up writing some custom and fancy dax, that uses RANKX() and when that is in the top 10, just shows the result, and uh... when not in the top 10, it uh... somehow magically sums them all together into an "other"... for which you would have to have on Rows somehow... hrmmm... Sounds like you need a fake/dummy row to pull this off.

Ya, find that article... if you can't... I kinda love this problem a little, so I will totally help. I am just crazy busy today...
 
Upvote 0
The easiest way is to go with the fake row if there is no need to drill into the report.

Build your pivot with the standard top 10 from pivot table filters. Build a second report below, move insurance company from rows to the report filter so you get 1 line that shows the real totals. Now add your fake row "Others" between both reports an calculate real totals - top 10 totals. Hide the top 10 totals and everything from the second pivot beside the real grand totals.
 
Upvote 0

Forum statistics

Threads
1,223,226
Messages
6,170,844
Members
452,360
Latest member
abishekjmichael

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