locked
Power pivot Charts RRS feed

  • Question

  • Hi Team,

    I need your help in solving my problem which i facing & trying from past one week to find a solution.

    About the problem:

    I have a employee database which consists of Managers, Executives,Permanent & Temporary etc. Same data i have kept in pivot table which has given me the desired view there after i have inserted stacked column chart and selected data range. In the chart am able to see at each managerwise how Permanent & Temporary executives are working but not able to find grand totals at each manager level even though it was there (calculated automatically) in pivot table. I tried to select data range but it is disabled. Could you please help to get grand totals in the chart (either stacker or anything other chart) but i want at each manager level how permanent & temporary executive are working along with total executives under him.

    Thanks in advance

    Regards,

    Vamshe

    Thursday, May 24, 2012 7:47 AM

Answers

  • Hi Vamshe -

    One way to accomplish this is to create a calculated measure for Manager totals like this:

    =CALCULATE(SUM(Sales[sales]),ALL(Sales[executiveType]))

    This releases the filter on executiveType (perm or Temp) and calculates the grandtotal for the manager.

    Then, within your stacked column pivot chart, do the following:

    1. plot both your total measure and your Manager's total measure (drop both measures in the Values area for the pivot chart)

    2. format the 2 Manager's total series to:
           - plot on secondary axis
           - change their chart type to column chart (not stacked; this will allow the secondary axis scale to stay in synch with primary)
           - set fill to No fill (make them invisible)
           - change them to 100% overlap and 0% gap (so their labels with be centered & they won't get completely covered up)

    3. add data labels outside the end of 1 of the Managers total series

    With that, you'll be able to overlay the manager series invisibly and get the data label for the grand total as desired like this:

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, May 28, 2012 1:49 AM
    Answerer

All replies

  • Hi Vamshe -

    One way to accomplish this is to create a calculated measure for Manager totals like this:

    =CALCULATE(SUM(Sales[sales]),ALL(Sales[executiveType]))

    This releases the filter on executiveType (perm or Temp) and calculates the grandtotal for the manager.

    Then, within your stacked column pivot chart, do the following:

    1. plot both your total measure and your Manager's total measure (drop both measures in the Values area for the pivot chart)

    2. format the 2 Manager's total series to:
           - plot on secondary axis
           - change their chart type to column chart (not stacked; this will allow the secondary axis scale to stay in synch with primary)
           - set fill to No fill (make them invisible)
           - change them to 100% overlap and 0% gap (so their labels with be centered & they won't get completely covered up)

    3. add data labels outside the end of 1 of the Managers total series

    With that, you'll be able to overlay the manager series invisibly and get the data label for the grand total as desired like this:

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, May 28, 2012 1:49 AM
    Answerer
  • Hi Brent,

    I have tried the way you have explained above but am finding grand totals at each column category (i.e. Permanent & Temporary) similarly in the chart also two grandtotals are shown up. How to fix it, please help me. Plese see the details below:

      Permanent   Temporary  
    Mgr IDs Count of Employee Headcount Total Count of Employee Headcount Total
    VVIDHYA3 39 289 250 289
    MSRINI25 18 81 63 81
    NMUTHUK2 22 77 55 77
    GKIRANKU 25 75 50 75
    PPRASAN2 20 55 35 55
    VVASANT 26 42 16 42
    NRADHIKA 8 13 5 13
    RBALAKR3 8 11 3 11
    NTAMILMA 8 10 2 10
    SSURES35 2 2  
    VKOTESWA 1 1  
    Grand Total 177 656 479 656
    Wednesday, July 11, 2012 12:55 PM