Pivot chart problem

[bjao08]

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-

 

[scottsen]

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?

 

[bjao08]

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!

 

[scottsen]

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.