How to display as percentages if Grand total is not being calculated?

Padme

New member
Joined
May 8, 2020
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2010
Hi, I am still rather a newbie to Pivot, so I hope I can manage to explain this sufficiently:

I have a large data set of multiple choice answers which I am trying to evaluate per year. However since the amount of data records per year differs, I want to look at the percentages to make the data comparable.

So I created a pivot table with multiple items in the value field. But this doesn't give me a total for each year.


Excel-Pivot-Example.jpg

Now when I try to activate the option to display the data as percentages of the column total, all cells just show 100%. I assume this is because of the missing column Grand Total.

Excel-Pivot-Example_Column Total.jpg

Excel-Pivot-Example_Column Total_activated.jpg

I attached a sample file where I show the issue. View attachment Pivot Example_Padme.xlsx

Does anyone know how I can solve this?
 
Part of the problem is that your raw data is already pivoted. I used power query to unpivot the data, and then created a pivot table from that new data table. You can now display the values as a percent of column total and it behaves as you expected.
 

Attachments

  • Pivot Example_Padme_PQ.xlsx
    66.2 KB · Views: 3
Wow, that's great! Now I finally understand. Thank you so much. I have been trying to solve this for weeks. Thanks a million.
 
Back
Top