Sorting PowerPivot tables for top 10 customers

[cvindasi]

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?

 

[scottsen]

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...

 

[Tianbas]

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.

 

[cvindasi]

Excellent - thanks! I will give that a try.. and see if I can be more specific in my google search!