# Power pivot Charts

### Pregunta

• Hi Team,

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

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.

Regards,

Vamshe

jueves, 24 de mayo de 2012 7:47

### Respuestas

• 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
http://brentgreenwood.blogspot.com

lunes, 28 de mayo de 2012 1:49

### Todas las respuestas

• 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