Pivot table with top X filter and "Other" field

[efofecks]

Hello,

Is there a way to create an "other" field in a pivot table dynamically, which is the difference between the grand total and whatever items are currently shown?

Say for instance my system sales are 100,000 and I filtered using the top 5 regions, the output could look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Region A[/TD]
[TD]40,000[/TD]
[/TR]
[TR]
[TD]Region C[/TD]
[TD]30,000[/TD]
[/TR]
[TR]
[TD]Region J[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]Region U[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]Region O[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Others[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]100,000[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated.

 

[iliace]

The easiest suggestion is to sort the Regions descending order, show the top 5, and group the rest of the regions. Note, the example here shows series of steps I took and I used slightly different techniques to generate each step. In your case, you would only do this to a single PivotTable.

In my sample data set, I have two columns: Region and Amount.

First, I created a PivotTable, putting Region in rows and Amount in Values. I sort them in descending order by amount. (Columns A:B)

Next, I grouped all but the first five items. This created a Group1 (Columns D:E)

Next, I collapsed all the grouped items (right click -> Expand/Collapse/Collapse Entire Field). (Columns G:H)

Finally, I used the move command to move the Group1 all the way to the bottom (right click -> Move/Move Group1 to End). (Columns J:K)

	A	B	C	D	E	F	G	H	I	J	K
Region	Sum of Amount	Region	Sum of Amount	Region	Sum of Amount	Region	Sum of Amount

<tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #000000"][/TD]

[TD="bgcolor: #000000"][/TD]

[TD="bgcolor: #000000"][/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]Region G[/TD]
[TD="align: right"]538,595[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Group1[/TD]

[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Group1[/TD]
[TD="align: right"]2,319,183[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region A[/TD]
[TD="align: right"]459,319[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]Region A[/TD]
[TD="align: right"]459,319[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region B[/TD]
[TD="align: right"]418,785[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region G[/TD]
[TD="align: right"]538,595[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region C[/TD]
[TD="align: right"]450,221[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]Region E[/TD]
[TD="align: right"]450,618[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region K[/TD]
[TD="align: right"]415,544[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region A[/TD]
[TD="align: right"]459,319[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region E[/TD]
[TD="align: right"]450,618[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]Region C[/TD]
[TD="align: right"]450,221[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region I[/TD]
[TD="align: right"]405,332[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region E[/TD]
[TD="align: right"]450,618[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region G[/TD]
[TD="align: right"]538,595[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]Region J[/TD]
[TD="align: right"]435,275[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region F[/TD]
[TD="align: right"]402,107[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region C[/TD]
[TD="align: right"]450,221[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region J[/TD]
[TD="align: right"]435,275[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]Region B[/TD]
[TD="align: right"]418,785[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region D[/TD]
[TD="align: right"]400,573[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region J[/TD]
[TD="align: right"]435,275[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Others[/TD]
[TD="align: right"]2,319,183[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]Region K[/TD]
[TD="align: right"]415,544[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region H[/TD]
[TD="align: right"]276,842[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Grand Total[/TD]
[TD="align: right"]4,653,211[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Grand Total[/TD]
[TD="align: right"]4,653,211[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: left"]Region I[/TD]
[TD="align: right"]405,332[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region G[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]Region F[/TD]
[TD="align: right"]402,107[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region G[/TD]
[TD="align: right"]538,595[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: left"]Region D[/TD]
[TD="align: right"]400,573[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region A[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]Region H[/TD]
[TD="align: right"]276,842[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region A[/TD]
[TD="align: right"]459,319[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: left"]Grand Total[/TD]
[TD="align: right"]4,653,211[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region E[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region E[/TD]
[TD="align: right"]450,618[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region C[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region C[/TD]
[TD="align: right"]450,221[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region J[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Region J[/TD]
[TD="align: right"]435,275[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="bgcolor: #000000"][/TD]
[TD="align: left"]Grand Total[/TD]
[TD="align: right"]4,653,211[/TD]

</tbody>

Hope this helps. It's not difficult to do programmatically using VBA, if you want to fully automate this process.

 

[efofecks]

Thanks, iliace.

My worry is that this data will be updated every month, and if the top 5 changes, the groups will have to change as well. Since your proposal already seems to be the best solution, does this mean that pivot tables and/or DAX have no native solution to this?

Thanks,

 

[iliace]

Thanks, iliace.

My worry is that this data will be updated every month, and if the top 5 changes, the groups will have to change as well. Since your proposal already seems to be the best solution, does this mean that pivot tables and/or DAX have no native solution to this?

Thanks,

I don't know of such functionality in Excel's pivot tables. I can't speak for PowerPivot as I'm not as extensively familiar with it.

You should be able to automate this process in VBA if you need to.

 

[scottsen]

I was leaning towards "Not possible", but with iliace leading the charge...

I can imagine a calculated column that parallels "region". If the Amount is in the top 5, it keeps its region name. If its not in the top 5, it gets "Other". then you use that calculated column on the rows of your pivot table.

It's going to have the calculated column draw backs (like, slicers are not going to impact your Top 5 dynamically) though.