Google Sheets pivot table percentage of subtotal
When grouping and sum, how to get the percent distribution of the grand total in Google Sheets Query similar to its Pivot Table ‘% of grand total’? Show
This post describes the percent distribution of the grouped items using Query formula. For non-grouped items, there is no requirement of Query. The same I have detailed here – Calculating the Percentage of Total in Google Sheets [How To]. Let me first make you understand how to get/calculate the percent of the grand total with the help of a Pivot Table in Google Sheets. The Query formula follows after that. For my example, I am only taking a dataset with 5 rows including the header row. With 4 steps you can group the data in the array B1:C5 and get the percent of grand total using Pivot Table as below. Pivot Table Steps to Calculate Percent of Grand Total in Sheets
That’s all. Now let’s see how to calculate the percent (distribution) of grand total in Google Sheets Query similar to the Pivot Table above. How to Calculate the Percent of Grand Total of Grouped Items Using a Query FormulaNew to Query? Then please read my Query function tutorial first. Since our topic is on calculating the percent of grand total in Google Sheets Query grouping, it’s clear that we must use the Group by Clause in Query. To group and sum the above data, we can use a Query formula as below.
Want to see what this formula returns?
But what we want is not a group-wise sum. We want percent of grand total similar to Pivot Table as above. Percent distribution of the grand total is actually grand_total/total_of_each_item. For example, the percent of the grand total of the items “apple”, “mango”, and “orange” can be calculated as follows.
In this, the above Query formula returns the individual item totals (grouped totals) which is the first part (before the division operator) of the above 3 formulas. To get the second part, we can use the SUM function as below.
We can include this SUM function within Query as below.
This Query formula will return the percent of the grand total of the grouped items. But it is not my final formula! Why? See the screenshot below. We must add a few more additional clauses with the formula to make it include infinite rows and get a percent formatted output. Format Number to Percent in Query ResultTo format the numbers (Query output) to percent, no need to do any change to the formula. Select the numeric data range in the result I2:I and format it to percent (Format menu, Number, Percent). Note: The below tips are just optional. You can skip it or read on to get more idea about formatting Query result. If you are very much particular to avoid the manual formatting, you can include the percent format within the Query. Here is the updated formula!
Related Reading: How to Format Date, Time, and Number in Google Sheets Query. Adding Percent Grand Total Row to a Query ResultMissing a grand total percent row at the bottom of the Query result, right? Here is the formula.
I have just added the grand total row to the Query result using the string “Grand Total” and 100% within Curly Braces as an array. I think we should make two more changes in the formula, but not necessary though. See the labeling in I1. You can customize that label using the Label Clause in Query formula as below.
Finally, make the formula to cover infinite rows.
See the Where Clause before Group by. This way you can get the percent of grand total in Google Sheets Query that involves grouping. Thanks for the stay, enjoy! How to calculate percentage of subtotal in pivot table Google Sheets?Google Sheets: Display Percentage of Total in Pivot Table. Click Add next to Rows and choose Date.. Then click Add next to Values and click Sales.. Then click Add next to Values and click Sales again.. Then click the dropdown menu under Show as in the second Sales field and choose % of grand total:. How to add percentage calculated field to pivot table Google Sheets?Calculated fields with SUM or a custom formula. On your computer, open a spreadsheet in Google Sheets.. Click the pop-up Edit button underneath the pivot table.. In the side panel, next to "Values," click Add. click Calculated field. ... . On the bottom right, click Add and the new column will appear.. How do I show the percentage of a total in a pivot table?Click on pivot builder the entry Sum of Sales and select Value Field Settings. In the Value Field Settings window, on the Show Values As tab, choose % of Column Total. Click OK.
|