Pivot chart problem

bjao08

New Member
Joined
May 20, 2014
Messages
2
Hi all. How is it possible to show an average of the values in a column, in a pivot chart, in Excel 2010? I want to be able to show an average line through the bars in a pivot bar chart displaying ratios calculated from two fields.

The actual worksheet deals with a power pivot, and therefore the measures needs to be dynamical and not include specific cells. But from my point of view the issue is still the same: How to create a measure(powerpivot) that displays the same, calculated average in all rows of a column. From there on this link, How To Add an Average Value Line to a Bar Chart | Excel Tactics, seems to solve the remaining issues about visualizing the average.

Maybe there is another way around? Thanks-
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Would help to see some specifics, but I suspect that won't be too hard. say a measure that is CALCULATE(AVERAGE(MyTable[MyColumn], ALL(MyTable), ALL(MyCalendar))

Then drop that measure on the table?
 
Upvote 0
Thanks for replying, scottsen! Is it really not possible to upload a test file in a thread, so that it will easily be more specific?

Anyways, there seems to be something wrong with the measure in your post, since AVERAGE only takes one argument. Apart from that I have already tried to solve it by using the AVERAGE function, but it seems not to be willing to take my new and calculated measure since it responds by "Column 'MyColumn' in table 'MyTable' cannot be found or may not be used in this expression.". Might it have to do with the fact that it 'corresponds' with data in an Access database?

Simple example of the issue:

Org $ FTE Ratio
X 4 2 2
Y 6 2 3
Z 20 5 4

The average for the ratio is in the above example the ((2+3+4)/3) = 3. A line representing 3 is what I want to go through bars in a pivot chart, where the bars represent the ratios, which by the way are calculated measures.
Thanks again for helping out!
 
Upvote 0
Most people just include a link to the file on google drive.

So, I had a typo -- should have been a paren after the call to average: CALCULATE(AVERAGE(MyTable[MyColumn]), ALL(MyTable), ALL(MyCalendar))

The idea is that this measure would calculate the average over ALL time, by using CALCULATE+ALL.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top