Disable Dynamic Axis?

[knotty150]

Hi There

I'm having an issue with axis dynamically changing when applying a value filter to a PivotChart:

I've got 100 names in PowerPivot, each name has a single value against it. I'm displaying that value as a percentage of the grand total in a PivotChart but only want to display the top 20 (in descending order of value). When I apply the value to restrict the display to the top 20, the Y axis automatically re-scales to calculate and display the values % of the top 20 - not the values % of the grand total.

Is there a way I can disable this?

Thanks in advance.

Rich

 

[BrianMH]

Should just be able to go into the axis options and set it to fixed.

 

[knotty150]

Hi Brian

Thanks for your response. I'm using version 2013; I'm in 'Format Axis > Axis Options' and can't see any option to fix the axis.

Am I in the wrong place?

Thanks

Rich

 

[BrianMH]

I'm not using 2013 but in the axis options you should have minimum and maximum. You can click the fixed radio button and type the values you want there instead of it being dynamic.

 

[knotty150]

Hi Brian

I'm not sure if I've explained myself properly. The problem I'm having isn't the axis scale (which I think your solution is trying to address), it's that the %'age values are recalculating based on the value filter I'm applying to the PivotChart.

If there's no value filter, the values (%'age figures) calculate/display perfectly fine. The problem with this though is that there are far too many names to display in the PivotChart (1 bar per name, 100 names = too many) - hence why I'm using a value filter to display only the top 20 names based on their value (which is a %'age figure). When I apply this filter, instead of displaying the values as %'age of the total 100 names, it displays the values as a %'age of the top 20 names (which is completely different).

I'd like to disable this automatic recalculation, so that the top 20 names I'm trying to display in the PivotChart display their values as a %'age of the total 100 names, NOT the value as a %'age of the top 20 names.

Does that make sense? Am I misinterpreting you?

Thanks again!

Rich

 

[Tianbas]

try a calculated measure that always calculates the % from your displayed value vs. the grand total

 

[BrianMH]

Oh now I get what you mean. Those will change based on the filter. What you want to do instead is build your table of data outside of the pivot table. Just point your formulas at the first 20 rows in the pivot table and next to that your values and so you get the names and the % and build your graph off of that. The good thing with not using a pivot chart is they are more customizable and you don't get those ugly buttons.

 

[Tianbas]

this is also an option but if you want to use your pivot chart change your measure to something like

=SUM(Table1[Whatever]) / CALCULATE(SUM(Table1[Whatever]),ALL(Table1))