Hello Mynda,
I unpivoted my data. Please look at the screenshot. Let us imagine that these are not months and they are some categories and if Month July is Fruit Category and I want to see it in %. How do I do that in my Pivot Table? Right now any changes I make in this Pivot table affect all the Months. I just want to make changes to July Month/ Fruit Category. I don't want to touch any other Months/Categories. Only one Category should show values in Percentage. Can you please give me the steps on how do I show values in % in the Month July/Fruit Category?
I attached the file also. First I had put only a screenshot.
Thanks
PB
Hi PB,
That can't be done, sorry. PivotTables don't work that way. They are designed to apply the same calculation to all items in a field. You can't pick and choose. That would need to be done with a table built with formulas.
Mynda
Hello Mynda,
Thank you! If I don't unpivot these fields then I can make changes to any Category. The only difference is then I can't choose those categories individually from a filter. Let us say that they are Fruits, Vegetables, Meat and so on. They will be in different columns. I can make changes to Fruits or Vegetables or Meat in any field. After unpivoting, it is not helping me in any way in my opinion.
It is good to know that it is not possible after unpivoting. I wasted a couple of hours trying to figure it out.
Thanks,
PB
Hi PB,
With your data unpivoted you're not really creating a PivotTable, which is why you can make changes to any category (because each one is it's own field) but you can't use the filters, because the data is already pivoted.
Mynda
Hello Mynda,
Thanks! I decided to try it without unpivoting and now the problem I am seeing is that where there are 0.00% together for 2 Columns in my Chart then they appear so tight which does not look nice. How do I show 0.00 % but they should not fall over each other. Maybe one shows a little higher than the other or any other suggestion, please?
Thanks,
PB
You could try increasing the gap width and reducing the font size. It looks like the labels might also be bold, if so I'd remove that too.
Hello Mynda,
Thanks! I tried Gap Width and this was the best I could get. With a smaller size, you won't be able to even read it. These little details take so much of my time. How do I show a message like 'Nothing to Show" if the percentage is 0.00% or NaN/blank then don't show that customer in the chart? Any other suggestions, please?
I didn't unpivot my tables as I was not able to change values for one Column (Which became a row only) I used OffSet and Match Functions to get the results that I need except these 0.00% falling over each other in my chart.
Thanks,
PB
If you're using regular charts, i.e. not Pivot Charts, you can use this Max trick to set the chart labels at specific heights.
