In my base data I have a flag ObjectiveMet with value 1 or 0, to say whether for that line the objective was achieved.
In my pivot table, I want to show what percentage of objectives where achieved. To do this, I use a COUNT of my flag, and "show values as" % of parent row total. This works so long as I include the 0's and the 1's from my flag. So I might have 87% objectives achieved and 13% not achieved. In effect the pivot formula is count(1's) as a % of count(1's and 0's).
The thing is that I'm not interested in seeing the figures for 'not achieved', as it's always 100-achieved. If I remove them from the pivot, then the achieved shoots up to 100% (makes sense, I guess).
It's even worse, when I try to chart the results, because I get a mirror image of each line.
Is there a way I can get correct percentages for the 'achieved' but not see the 'not achieved'?
I know I can hide rows / columns, and also set data series to 'no line' but is there a better cleaner way, that I'm not seeing?
Many thanks.
In my pivot table, I want to show what percentage of objectives where achieved. To do this, I use a COUNT of my flag, and "show values as" % of parent row total. This works so long as I include the 0's and the 1's from my flag. So I might have 87% objectives achieved and 13% not achieved. In effect the pivot formula is count(1's) as a % of count(1's and 0's).
The thing is that I'm not interested in seeing the figures for 'not achieved', as it's always 100-achieved. If I remove them from the pivot, then the achieved shoots up to 100% (makes sense, I guess).
It's even worse, when I try to chart the results, because I get a mirror image of each line.
Is there a way I can get correct percentages for the 'achieved' but not see the 'not achieved'?
I know I can hide rows / columns, and also set data series to 'no line' but is there a better cleaner way, that I'm not seeing?
Many thanks.
not sure if that changes anything.
