Tags: CUBEMEMBER

Sort individual columns of a Pivot Table based on a slicer selection

{0 Comments}

Here is a simple four column dataset

Week Team User Codes
1009-1016 Default-LossMit FAST INTL\KOrdillano ATPD/5
1009-1016 Default-LossMit FAST INTL\KOrdillano ATWI/116
1009-1016 Default-LossMit FAST INTL\KOrdillano ATWI/3B
1009-1016 Default-LossMit FAST INTL\ADulnuan ATWI/116
1009-1016 Direct - HSD INTL\JCustodioii S/2
1009-1016 Default-LossMit FAST INTL\abacud ATWI/116
1009-1016 Default-LossMit FAST INTL\SCaparon ATWI/116
1009-1016 Default-LossMit FAST INTL\ADulnuan ATWI/116
1009-1016 Default-LossMit FAST INTL\ADulnuan ATWI/116

A simple Pivot Table (with a slicer) created from this dataset looks like this

Untitled

The objective is to determine the Top 3 users of each week for each slicer selection.  Unfortunately, there is no way to sort multiple columns of a Pivot Table all at once.  Once may either sort by the Grand Total column or by the individual week wise columns.  Since we do not want to sort by the Grand Total column, the only way out is to sort the individual week wise columns.  The expected result should look like this:

Untitled

I have solved this problem by using CUBE formulas.  You may refer to my solution in this workbook.

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

Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart

{26 Comments}

Assume a simple Sales dataset from which a Pivot Table has been created.  The Pivot Table has been sliced by two columns of the dataset.  To represent data graphically, a Stacked Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook).  The Stacked Pivot Chart has Months on the X-axis and each month has stacks for various products sold in that month.  By design, a Pivot Chart never displays data from the Grand Total column of a Pivot Table.  The Select Data button the Pivot Chart Tools button does not allow the user to reselect the Source data to include the Grand Total column.  The only option left in this case is to copy the Pivot Table and paste it as Paste Special > Values in another range and then create a Normal Stacked chart from this Table.  But in doing so, any change in the slicer or Base data will not have any effect on the Stacked Chart because the source of the Stacked Chart is a static range.

This problem can be overcome by using the PowerPivot tool and CUBE functions (available in Excel 2007 + versions).  You may download the solution workbook from here.

You may watch a short video of my solution here