Trying to get the size (in %) of subcategories in a pivot

user_330271 0
Viewed 875

I am keeping track of my (stock) portfolio in Google Sheets, as follows:

category     subcategory    company  amount
-------------------------------------------
health care  diagnostics     AA       100
health care  diagnostics     AB        50
materials    mining          BA        75
financials   banks           CA        30
financials   insurers        CB        35
financials   banks           CC        10
financials   banks           CD        40
financials   hedge fund      CE         5
health care  equipment       DA        50

But now I want to extract some statistics from this, and I'm using a Pivot. Specifically, I want to see:

  • the relative size of each category in the portfolio
  • the relative size of each subcategory in the portfolio
  • the relative size of each company in their subcategory

The first two I get done:

enter image description here

For instance, I can see that:

  • category financials has a relative size of 30% in the portfolio
  • subcategory diagnostics has a relative size of 37.97% in the portfolio

What is missing however, is the third column, see mockup below:

enter image description here

I can now see in the last column what the relative size of each company is in its subcategory:

  • Company CD is 50% of the subcategory banks
  • Company AB is 300.33% of the subcategory diagnostics

That last column however, is not calculated but added manually to show what output I am trying to get but am unable to.

Does anyone know how to have this last column as part of the Pivot?

Here is a link to the Google Sheet used: Pivot

google-sheets
2 Answers

Currently, there is no % by subtotal option in Google Sheets to show percentages against subtotals for the whole Pivot Table:

enter image description here

As a workaround, you can display the subtotal percentages by filtering the Pivot Table to display only one subcategory, for example:

Add Filter here and select subcategory:

enter image description here

Then select the desired field, for example, banks, and click OK.:

enter image description here

This should display the subtotal percentages like this:

enter image description here

Note: You can delete category field in Rows to make the Pivot Table cleaner.

You can also submit a feature idea request to Google Workspace, please see instructions on this site: Submit ideas for Google Workspace and Cloud Identity

user_146060490

So I found out there are two possible solution using calculated column and another using arrayformula.

Option 1: Use a duplicate pivot table. One issue of getPivotData function is that it will run in the table calculations stage so it will return empty result. So the solution was to have two clones of the pivot table. One will be used to retrieve the data and the other to retrieve the subtotal.

The formula to use would be

=amount/getpivotdata("amount",'Copy of Pivot'!$F$10, "category", category, "subcategory", subcategory)

Where amount, category, subcategory without the double quotes are values (references) filled by Google Sheets. While the double quoted ones are column names.

You also need to set summarize by to custom for it to work.

output

The only issue I found here is that I couldn't find a way to identify subtotal or total rows, they do have company value equal to the first record in the group. If anyone know how to know if I am rendering a subtotal, please let me know.

The second option is to use one pivot table only but reference original data.

=amount/SUM(QUERY($A$1:$D,"Select D Where A='"&category&"' AND B='"&subcategory&"'",false))

It still shares the same total rows though.

The last option will be the least robust. It would be using arrayformula but I don't have that ready at the moment. It is main issue is that changing the column orders or adding new columns will require to keep the position of the refenced columns and move the arrayformula columns. Not to mention, handling the empty cells in a category since data is grouped.

user_33995640
Related