I want to have one column in a pivot table that is as a calculated item from the total count to display the share of total in that category. I am able to 'hack' pivot tables in a way that I get a total count for a field (which consists of variable values) and can calculate the share for a category. Custom field: count / (total_count / count). Total_count contains the total count for each category on each row of the input data.
Now, I am but having issues to calculate the share for a subtotal. As the hack is specifically designed to aid in calculating the share of a category, it breaks for the subtotal.
Highlighted in red the fields that are incorrect:
ztmSb.png
Below is a mock-up concept with correct output:
q4qX6.png
But the end result needs to be 'simple', so it can be broken down by each week:
VMgHA.png
The difference between 2 weeks can be a simple formula outside the pivot table, or can probably be a calculated item. That is not in focus here.
Thought starters are welcome! What is the direction for a proper, unhacked Excel method?
Bookmarks