Making X & Y Axes Scales Dependent

[kitjosh1050]

Hello All,

I am using PowerPivot V2 in Excel 2010.

I am trying to create a chart which compares performance for a certain measure against the overall dimension average. For each dimension, it should be displayed as a column while the overall is displayed as a line.

When I move the overall average to the secondary axis, the scale will shift. This is the intended purpose of the secondary axis, but in this case I need them to remain consistent.

I can change the scale manually, but the ideal is for the x-axis to auto-adjust and the y-axis scale to match it. Is there a way to do this? Ideally without VBA. And I'm curious for 2013 users, is there an alternate in that version?

Here is a picture of the ideal: https://drive.google.com/file/d/0B28JaEoX1COwcWk5LUZFYUY5NHc/view?usp=sharing

Thanks,

 

[pgc01]

Hi

This works with a regular chart and so maybe you can adapt it to work with the one you are generating from your Power Pivot.

Assuming a regular chart with the 2 series, the first one type column in the primary axis and the second one type line in the secondary axis,

I added 1 other series with just 1 point to the secondary axis. The value of the point is the maximum of the values in series in the primary axis (=MAX()). Without a marker you won't see the point.

Because in both axis the maximum point is the same both axes adjust automatically to the same scale (I'm assuming both initial series have the same minimum value of 0, or else add another point with the minimum).

Please try

 

[ImkeF]

In Excel 2013 you can set for each line/series name individually:
1) which chart type
2) if to be shown on the secondary axis

http://www.mrexcel.com/forum/power-bi/845055-stacked-column-chart-line-combo.html

 

[kitjosh1050]

Hi pgc01, I used your idea but ended up creating a copy of the first measure called "Avg TAT Histo Acc to SO for Chart" and put it on the second axis. Now it works, but this is an awkward solution.

Maybe I'm missing something simple, but I can't seem to figure out a simple solution to calculating the max of another measure (which is an average). If you have any ideas that would be great.

My two formulas are:

[SIZE=1]Avg TAT Histo Acc to SO=AVERAGE('Fact TAT Histology'[TAT_ACCESSION_TO_SIGNOUT])[/SIZE]
[SIZE=1]Avg TAT Histo Acc to SO - Dim Total=IF(ISBLANK([Avg TAT Histo Acc to SO]),BLANK()
    ,CALCULATE([Avg TAT Histo Acc to SO]    ,ALLSELECTED('Fact TAT Histology')))
[/SIZE]

Here is a picture of my chart before and after hiding the "for Chart" measure. https://drive.google.com/open?id=0B28JaEoX1COwNWZiWjFhVGk2Yjg&authuser=0

I do really wish we could use Excel 2013. We have the license, there are just other applications that are not compatable.

 

[scottsen]

I can't seem to figure out a simple solution to calculating the max of another measure

Generally that is :
=MAXX(SomeTable, [SomeExpr])

Say...
=MAXX(VALUES(Calendar[Year]), [Avg Sales])
is going to eval the Avg Sales for each Year, and return the MAX one.

 

[kitjosh1050]

Thanks scottsen. I kept trying different MAXX formulas but was never using the right table expression for my case. This helped.