I have a Percent of Parent Column field that works fine. But in the axis of values, I only want one shown to make the table much more compact. Essentially imagine if you had a table showing the percent of different animal types in various water bodies. You had Mammals, Fish, Reptiles, etc. I have several other dimensions involved in the pivot tables, so the Percent of Parent Column is the one that always gives the correct value.
However, I only want to display the percentage for Mammals. So I filtered the animal types down to just mammals. Great, except now the percentage is only a percent of what is shown, meaning it is always 100%, since there are no other animal types displayed.
In Access there used to be a way to include hidden values when calculating percentages which is similar to what I need in Excel, but no such option have I been able to find in Excel. Any suggestions?