How to Do Percent of Total in Looker Studio

To calculate percent of total in Looker Studio, you need to create a calculated field that takes into account the total value of a dimension. This can be done by using the "SUM" function and referencing the dimension you want to calculate the percentage for.

The formula for percent of total is: (SUM([Dimension]) / SUM([Total Dimension])) * 100. For example, if you have a dimension for sales region and a total dimension for sales, the formula would be: (SUM([Sales Region]) / SUM([Total Sales])) * 100.

To set up a calculated field in Looker Studio, navigate to the "Create" menu, select "Calculated Field", and enter the formula. Make sure to select the correct dimension and total dimension for your specific use case.

Calculating percent of total in Looker Studio can be done in a few ways, but it's essential to understand the differences between queries with and without pivoted dimensions.

You can use table calculations to calculate percent of total for queries without pivoted dimensions by dividing a row's value by the sum of its row.

Using a pie chart visualization is another option for queries without pivoted dimensions, but table calculations provide more flexibility and control.

For queries with pivots, you can use table calculations or the Stacked Percentage visualization setting to calculate percent of total across columns.

The Stacked Percentage visualization setting is a convenient option, but table calculations offer more precision and customization.

To use table calculations for queries with pivots, you can divide a row's value by the sum of its row: sum(pivot_row(${measure}))/sum(sum(pivot_row(${measure})))

This expression works by summing the pivoted values across a row to produce a row total, then dividing that total by the grand total of row values.

Here's a breakdown of the table calculation expression:

To calculate percent of total for each part of the table calculation, you can use the following expressions:

You can also use a simpler expression to calculate percent of total: ${measure}/sum(${measure})

This expression works by dividing a row's value by the sum of its column to get the percent of total for that row.

Here are some key points to keep in mind:

+ sum(pivot_row(${measure}))/sum(sum(pivot_row(${measure})))

+ ${measure}/sum(${measure})

There are two types of percentage pivot tables we can create in Looker Studio that require blending data. One type is percentage of column, where the sum of each column is 100%.

To create a percentage of column pivot table, you need to configure the pivot table with the blended data table as the data source. Any dimension is acceptable for the row, but only add dimensions used as join conditions for the blend as the column.

You should check the box to show the grand total of rows in the totals section. Then, create a metric of SUM(orders) / MAX(orders_denom) with a data type of Percent and a display format of Percent (2).

Here's a summary of the steps to create a percentage of column pivot table in Looker Studio:

To create a subtotal field in Looker Studio, you can follow these steps. First, select Resource > Manage added data sources from the menu.

You'll then need to locate the Pet Store data source and click EDIT to open the data source fields panel. Clicking ADD A FIELD will bring up the calculated field editor.

In the calculated field editor, enter Subtotal in the Field Name field and Price * Qty Sold in the Formula field. The formula editor will autocomplete dimension, metric, or function names as you type.

After saving the field, return to the main data source editor by clicking ALL FIELDS.

To create a subtotal field, start by selecting Resource > Manage added data sources from the menu. From there, locate the Pet Store data source and click EDIT to open the data source fields panel.

You'll then need to click ADD A FIELD to bring up the calculated field editor. In the Field Name field, enter Subtotal.

The formula for the subtotal field is Price * Qty Sold. You can enter this directly into the Formula field or use the formula editor's autocompletion feature to help you.

Drag fields from the Available Fields list into your formula if needed, and then click SAVE to complete the process.

To create a discount field, you'll want to evaluate the subtotal value and apply a discount rate accordingly. This can be achieved using a CASE expression to evaluate the subtotal field.

You'll need to define the discount rates for different subtotal value ranges. For example, a 5% discount for subtotal values between 100 and 499, and a 10% discount for subtotal values of 500 or more.

To express the discount as a decimal value, simply divide the percentage by 100. So, a 5% discount would be 0.05, and a 10% discount would be 0.10.

Here's an example of how you might structure the discount rates in a table:

You'll only be calculating the discount in this step, applying it in the following step by multiplying the subtotal field by the discount field.

In Looker Studio, you can use the Stacked Percentage visualization setting to display the percent of total for various visualization types, including column, bar, scatterplot, line, and area charts.

To enable this feature, select the Stacked Percentage option in the Plot tab of the Edit menu on an Explore's Visualization bar.

The Stacked Percentage method is particularly useful for visualizing data that needs to be compared to a total, such as Order Items Count grouped by Orders Created Year and pivoted by Users State.

Here are the visualization types that support the Stacked Percentage setting:

Pie charts are a great way to visualize data, and you can use them to display the percent of total without creating a table calculation.

Using a pie chart visualization, you can list percentages in the legend alongside the fields to which they belong. This is especially useful when you're only using the visualization, as it won't be included in the Explore data table.

A pie chart can display the percent of total for each value in the data, as seen in the example of Order Items Count grouped by Users City. The percentages are clearly listed in the legend, making it easy to compare the different values.

You can't include a percent of total calculation in the Explore data table if you're using a pie chart visualization, and any downloaded results won't contain the percent of total either.

Stacked visualization is a powerful tool for displaying data. It can automatically display the percent of total for various visualization types.

To use stacked visualization, select the Stacked Percentage option in the Plot tab of the Edit menu on an Explore's Visualization bar. This will automatically display the percent of total for the selected visualization type.

The following visualization types can be used with stacked visualization: Column, Bar, Scatterplot, Line, and Area. This is a useful feature for quickly understanding the proportion of each category in a dataset.

You can hover your cursor over each section of the column to see the percent of total for each order created date and age tier combination in the tooltip. This can be a helpful way to get a quick sense of the data without having to dig deeper.

Note that the information displayed in the tooltip will not be included in any static images or downloads of the chart.