# Calculate Percent of Total

Use AQL to do this simpler and faster: Percent of Total

Percent of Total enables you to find the percent distribution of a measure, broken down by one or many dimensions. In this guide, I will walk you through the detailed steps on how to find the percentage of a value in the total with Holistics modeling.

## Context

Let's say we have a table/model called `percentage_of_total_raw`

that contains 6 fields:

`Table orders {`

field order_id integer

field order_created_date datetime

field category varchar

field parent_category varchar

field country_name varchar

field quantity integer

}

Let us assume we want to build 2 reports that can:

- Find out the sales percentage of each
`category`

with respect to the total sales (based on`quantity`

). - Find out the percent of total sales per category and country for each individual day. This will affect the denominator of the percentage that the total sales are grouped by each day.
- Filter the report by any dimension (category, date...) and when using a filter, percent of total does not reflect the percent of filtered records to the total records in the data.

This is how the 1st report would finally appear:

## General Solution

$\large percentage = {Partial\space Amount \over Total\space Amount}$

We perform some SQL transformations to enable granular calculations of the percentage of total with the following steps:

- Create Model 1 or Subquery 1: Calculate the numerator of the percentage in which the total value is grouped by one or many dimensions.
- Create Model 2 or Subquery 2: Determine the whole or total amount of what you want to find a percentage for in the denominator of the percentage.
- Link these models or join these subqueries and add them to the exploration.
- And finally, you need to divide the sub amount by the total using a business calculation to get the percentage.

## Use case 1: The simple percent of total sales per category

In this case, we would like to divide the total sum by only **one attribute** such as `category`

.

Particularly, we need to take the Sum of sale quantities for each category and divide it by the Total sale quantities for all categories.

$\large percentage = { 𝐒𝐮𝐦\space(quantity)\space \space 𝐠𝐫𝐨𝐮𝐩\space 𝐛𝐲\space(category,country,date) \over 𝐒𝐮𝐦\space(quantity)\space \space 𝐠𝐫𝐨𝐮𝐩\space 𝐛𝐲\space(date)}$

### Using query model

First, we build the

`percent_of_total_per_category (category, total_by_cate, total)`

model where`total_by_cate`

measures how many quantities are in each category, and`total`

is the total sales of all categories in the denominator.We do that by creating two subqueries to calculate the numerator

`total_by_cate`

and the denominator`total`

and then, join them**ON TRUE**`with pt_all as (`

select

sum(quantity) as total

from

{{ #percentage_of_total_raw t1}}

) -- to calculate denominator (total)

,

pt_sale_by_cate as (

select

category,

sum(quantity) as total_by_cate

from

{{ #percentage_of_total_raw }}

group by 1

) -- to calculate numerator (total_by_cate)

select

pt_sale_by_cate.category,

pt_sale_by_cate.total_by_cate ,

pt_all.total

from pt_all join pt_sale_by_cate on truePut the data model in a data set and calculate percentage with business calculation

To get the

**percent of total per category**, we will create a new measure called**% per category**which dividesby`total_by_cate`

`total`

`sum(total_by_cate) / max(total)`

### Building the chart

#### Using Pivot Table

Drag your `category`

to "Rows" and `percentage_of_total_raw.quantity`

to "Values" with setting to Sum.

Add the calculation `% per category`

to "Values".

Go to "Styles" to enable your Column Totals and Sub Total. Now you have all your percent of total per category as the out put we showned in Context part.

#### Using Pie/Donut Chart

To display a set of categories’ proportions or percentages of the total per **one attribute**, you can just simply use a pie chart or donut chart. There is no need to use the query model to first work out the percentage of the pie chart that each category should occupy. You can just let the visualization do the percent of total calculation by itself.

The percentages will be listed in the legend, alongside the records to which they belong. Under "Styles", enable "Show percentage" to display the percentage that the sectors represent instead of the raw value.

A pie chart is often used to compare each group’s contribution to the whole, as opposed to comparing groups to each other.Note:

## Use case 2: The percent of total per category and country for each day

The result will show:

- The order's country and category
- Total orders (sale quantities) of each category for each certain day
- Percentage of that category over total sales of all categories for each certain day

$\large percentage = { 𝐒𝐮𝐦\space(quantity)\space \space 𝐠𝐫𝐨𝐮𝐩\space 𝐛𝐲\space(category) \over 𝐒𝐮𝐦\space(quantity)}$

### Overall data transformation

Using the base model `percentage_of_total_raw`

, you can simply measure the numerator using **aggregate function SUM** in the exploration UI.

You will need an `orders_total_not_broke_down_by_category`

model to calculate the total sales of all categories on each day in the denominator.

In order to get the single entry point for 2 models, you need a date dimension model (`date_dim`

model) and join it with `percentage_of_total_raw`

and `orders_total_not_broke_down_by_category`

using a created common `date`

field in all 3 models

In this case, the denominator number is the total sum grouped by each date. That's why we choseNote:`date_dim`

model as a junction model. In other quarters, we use`date_dim`

model to compare metrics from different models by date.

### Create Orders Total Not Broken Down By Category model

`select`

{{ #t1.order_created_date }},

{{ #t1.country_name }},

-- **We remove the category column here** {{ #t1.category }},

sum({{ #t1.quantity }}) as quantity

from {{ #percentage_of_total_raw t1}}

group by 1,2

### Joining the models

To add the `orders_total_not_broke_down_by_category`

model to the same exploration with `percentage_of_total_raw`

, we have to link them somehow.

An experienced modeler might notice that we can create a ‘1 - n’ relationship from `orders_total_not_broke_down_by_category`

to `percentage_of_total_raw`

by creating a `join_key`

to both the original model and the total model like `country_name || '-' || CAST(date as text)`

. The problem with that approach is that only the total from countries that exist in a category would show up.

The recommended way to deal with this is to create a `[date_dim`

model](https://docs.holistics.io/guides/using-date-dim-model).

### Create Date Dims model

`date_dim`

- a created common model to be the base of JOIN operations

`select`

generate_series( min({{ #a.order_created_date }}), max({{ #a.order_created_date }}),'1d')::date as dates

from

{{ #percentage_of_total_raw a}}

Date in `date_dim`

model is unique so we can create two ‘1 - n’ relationships to `percentage_of_total_raw`

and `orders_total_not_broke_down_by_category`

.

Now `orders_total_not_broke_down_by_category`

returns the total for the day regardless of category.

### Create the dataset and calculate the percentage

Now, create the dataset, add the `orders_total_not_broke_down_by_category`

, `percentage_of_total_raw`

and `date_dim`

model to the dataset and it will be ready to be explored.

Calculate percentage `% for each day`

with business calculation as below:

`sum(percentage_of_total_raw.quantity) / sum(orders_total_not_broke_down_by_category.quantity)`

## Building the chart

When you only need the percent of the total in the visualization, you can use one of the following chart types to do the percent of total calculation:

- Column
- Bar
- Scatter chart
- Line
- Area
- Pie chart and Donut chart

When you need both the partial amount and the percent of the total in the visualization, you can use:

- Table and Pivot Table
- Combination Chart.

### Displaying using Column chart

Here is an example to show how the chart looks and acts using Column chart with "**Stack Series**" option.

### Displaying using Pivot Tables

Drag your `category`

to "Rows", **Day** `order_created_date`

to "Columns" and `percentage_of_total_raw.quantity`

, `orders_total_not_broke_down_by_category.quantity`

to "Values" set to Sum.

Add the calculation `% for each day`

to "Values".

Go to "Styles" to enable your Column Totals and Sub Total. Now you have all your percent of total per category for each day.

### Dashboard filters

Right now, the filter can only be mapped to one field in one data model of the dataset so you'll have to create two filters, one to filter the numerator, one to filter the denominator.

## Other Notes

### Percentage options

Computing a percentage involves specifying a total on which the percentage is based. With this query model, you can choose many different options: based on the entire table, a column, and a row.

For example, in use case 2, each measure on the table is expressed as a percentage of the total for the column. The values within the "percentage" column add up to 100%. Based on the ways that you build the query models and link them, you can also set each measure on the worksheet so that it is expressed as a percentage of the total for the row or for the entire table.

### Preserve “Percent of Total” when using dashboard filter

If you want to filter out one or more of your dimensions, the ‘percent of total figure’ changes because the ‘total’ which is used in the denominator computation changes too to reflect the loss of the dimension members.

In some cases, you will want to keep the original percentage (of the whole underlying data) while just displaying the dimension members you are interested in. or example, we have a table for Sales per category.

When we set a dashboard filter to only show Category = Category1, then the percent will be changed to 100%, as all other categories were excluded from the total.

Suppose that you want to keep the original percent of Category1 (25%).

What you can do is set up a filter that only filters the numerator. So the % of the total calculation will not change when you adjust the filter. This is due to the ‘total’ computed in the denominator isn't being changed.