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

{ 31 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

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Ashish,

    Thank you very much for posting this solution to displaying grand totals in a pivot chart, while maintaining a dynamic view of the data. Brilliant!

    Steve

  • I’m following along with your video on “Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart” and everything seems to be working fine except after I copy my table and convert to formulas my new table isn’t dynamic. When I click on the slicer from the original table above the grand total from the new table doesn’t change to match the main power pivot table. I can’t figure out what I’m doing wrong.

  • Hi Ashish,

    Thank you for sharing this with us.

    Could you please let me know if someone who has Microsoft Office 2010 but does not have power pivot installed, all be able to use a dashboard created with a power (that uses the grand total as in the video presented.

    Many thanks,

    Nicole

  • I did it with GETPIVOTDATA function, in Excel 2007. Quite easy, actually.
    Start the same – create the same structure as the original pivot without any filters, (Copy>Paste Values) but then delete the Values area and habits this measure with the GETPIVOTDATA function. The syntax I used was:
    =IFERROR(GETPIVOTDATA(“Amount”,$A$8,”Date of Sale”,1 ,”Product”,”Product A”),0)

    $A$8 is the information used to determine which PivotTable report contains the data that you want to retrieve.
    In our case, point it to pivot table cell where it says “Sum of Amount”.
    1 indicate the row number, Jan, in our case. You probably want to change it to 2, 3 etc. every time you drag the function down.
    The last argument, “Product A”, also needs changing when dragging across columns, I pointed it to my GETPIVOTDATA table header.

    Then create Grand totals as you like, and create chart.
    Last thing- regarding changes in Base data: You need to pay attention that row data and columns header remain static even if Base table expand with new products and new dates. to partially solve it I decided to create the structure with 12 months rows (not only Jan-Apr but Jan-Dec). And to I keep in mind that when new product (Product F) will be add to Base data, then I’ll have to insert a new column for it manually. If new products add often, I’ll search the web for code to do that, or consider some kind of multivariable Retrieving using array formula or whatever.

  • Hi there. I cannot get the slicer references when I copy the PowerPivot (the formula looks like this: =CUBEVALUE(“ThisWorkbookDataModel”,$B$25,$B27,G$26), It should look something like this =CUBEVALUE(“ThisWorkbookDataModel”,$B$25,$B27,G$26,Slicer_Indicators1,Slicer_Month1).What am I doing wrong please?

      • Hi Ashish, I would be grateful if you could let me know whether you are able to help me or not please. If not then I fully understand. Thanks.

        • Hi,

          Yes, I am willing to help you. Upload your workbook to OneDrive and share the download link here. Please also indicate the cells where you are facing a problem.

  • Asish, is your PowerPivot/OLAP solution dynamic? Will the chart be updated as the number of rows and columns change in the base table?

    • Hi,

      Since my solution uses CUBE functions, when you add data by rows to your source data, you will have to copy and paste the CUBE functions as well. You cannot refresh unlike a Pivot Table.

  • Hi Ashish,

    Thank you! I do have one question:

    If you select a slicer, and a column label has no data, is it possible to not include the column label in the chart legend?

    Thanks.

  • Thanks for nice walkthrough Ashish.
    Found another way of able to add Grand totals in Pivot chart , without using any power pivot for my current requirement.
    It requires adding few additional rows of aggregated data for Grand totals
    Thanks again !
    Girish

  • Thanks for this tutorial! One question, which has been referred here before but I guess no solution yet. I do have people’s names where you have those four month names. I do have a slicer for those names. If I use that slicer it does not update the values in newly created table from powerpivot table. The numerical values, like you have the sales values, do update from my slicer. And clue what might be the solution? Slicers work normally in the regular pivot table.

      • Hello. Thank you for your reply.

        I do have around 15 names in the tables. It doesn’t matter how many names I select, it still doesn’t update the amounts to newly created table. For the pivot table it updates the values based on which names I select. So the problem is only in the table which was converted to functions (CUBEVALUE).

          • Hello.

            Sorry I can’t share the file, classified info. But I found a workaround which isn’t perfect but it’ll do. I made a copy of original pivot table and used paste special – paste links.