Customise Pivot Table reports

{ 8 Comments }

Assume that someone has created a Pivot Table using the PowerPivot tool.  Now one may want to customize the Pivot Table even further by:

1. Shuffling rows in the Pivot Table; and
2. Recomputing subtotals and Grand Totals after reshuffling rows

A Pivot Table created via the PowerPivot tool can be converted into a normal range via CUBE formulas.  Once each cell carried an individual formula, one can very easily perform the two tasks mentioned above.  You may view my solution in this workbook.

You may watch a short video of my solution here

Leave a Comment

Your email address will not be published.

*

  • Dear Ashish,
    I want to customize the row orders in powerpivot report. By default it is coming alphabetical way like I have some brands a,c,b,d,p, so when I will create a powerpivot / pivot report it will sort by default in a, b, c, d and p order.
    Currently I’m using a custom list to sort in required order, but when I send it to others and they change some filter / slicers then brands order change automatically into alphabetical sequence.
    I tried one more option in power pivot by created a helping table which was concatenated with brand sequence number and brand name like 1-a, 2-c, 3-b and etc, but I don’t want to show concatenated number & characters in my report.
    Can it done by other process or methods, please help.

    Note:- Currently I’m using excel 2010.

    Thanks,Ankur