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

RSS 26 Comments…

 Share your views
  1. 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

  2. Hey Ashish!
    Could please elaborate on how to use your excellent solution under Excel 2007 ?

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

  4. […] the % line to appear on a secondary axis. You can find some instructions here for the OLAP part: Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart For the chart: https://support.office.com/en-au/art...3-bacc7430c0a1 As an FYI, when you select to […]

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

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

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

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

  9. Unbelievably clear and useful! THANK YOU!!!!!

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

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

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

    • Hi,

      You are welcome. The names from the graph will not vanish if only 2 of the 4 names are chosen in the slicer.

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

        • Hi,

          I will need to see the file. Share the link from where I can download the file and clearly show the problem along with your expected result.

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

Leave a Comment

Your email address will not be published.

*

*