# Calculate Percent of Total

This is part of our beta expression language AQL. Learn more. Request beta.

## Introduction

The Percent of Total analysis is a commonly used reporting technique that helps understand the contribution of each row value to the overall populatiion.

In SQL, you have to divide your calculation into multiple CTEs (Common Table Expressions) and then cross-join those together to perform Percent of Total. Whereas with AQL, you can achieve this calculation in a more concise and reusable way by focusing on the business logic rather than the technical semantics.

## High-level Flow

Suppose we want to see how each product’s sales contribute to global sales.

To illustrate the calculation using AQL, we’ll explore two different ways of performing it. First, we will break down the calculation into manageable steps or components. Then, we'll demonstrate how to write it as a single-line query.

## Setup

To make it simple, we'll be working on the `order_items`

modeling definition only with the following fields.

- Initial Setup
- Final Setup (Resuable Components)
- Final Setup (Single Line)

`Model order_items {`

...

dimension id {...}

dimension product {...}

dimension amount {...}

}

`Model order_items {`

...

dimension id {...}

dimension product {...}

dimension amount {...}

measure total_sales {

label: 'Total Sales'

type: 'number'

definition: @aql sum(order_items.amount) ;;

}

measure _total_sales_of_all {

label: 'Total Sales of All'

definition: @aql sum(order_items.amount) | of_all(order_items.id) ;;

hidden: true

}

measure percent_of_total {

label: "Percent of Total"

type: 'number'

definition: @aql (order_items.total_sales*1.0) / order_items._total_sales_of_all ;;

}

}

`Model order_items {`

...

dimension id {...}

dimension product {...}

dimension amount {...}

measure percent_of_total {

label: "Percent of Total"

type: 'number'

definition: @aql (sum(order_items.amount) * 1.0) / (sum(order_items.amount) | of_all(order_items));;

}

}

## Implementation

**1. Define the aggregation**

Calculate the total sales value for each product by defining the measure `total_sales`

`Model order_items {`

...

measure total_sales {

label: 'Total Sales'

type: 'number'

definition: @aql sum(order_items.amount) ;;

}

}

`explore {`

dimensions {

products.name

}

measures {

order_items.sales

}

}

- Result
- SQL

`SELECT`

products.name as "products->name"

sum(order_items.amount) as "order_items->total_sales"

FROM

order_items

LEFT JOIN

products

ON order_items.product_id = products.id

GROUP BY 1

**2. Define the aggregation with coarser level-of-detail**

Total sales of all order items is also needed to be used as denominator for the percent of total. This measure can be definied by using `of_all()`

metric function.

`Model order_items {`

...

measure total_sales {...}

measure _total_sales_of_all {

label: 'Total Sales of All'

type: 'number'

definition: @aql order_items.total_sales | of_all(order_items) ;;

}

}

`explore {`

dimensions {

products.name

}

measures {

order_items.total_sales,

order_items._total_sales_of_all

}

}

- Result
- SQL

`WITH aql__1 AS (`

SELECT

products.name AS "products->name"

SUM(order_items.amount) AS "sum_order_items->amount"

FROM

order_items

LEFT JOIN

products

ON order_items.product_id = products.id

GROUP BY 1

),

aql__2 AS (

SELECT

SUM(order_items.amount) AS "sum_order_items->amount"

)

SELECT

aql__1."products->name" as "products->name",

aql__1."sum_order_items->amount" as "total_sales",

aql__2."sum_order_items->amount" as "_total_sales_of_all"

FROM

aql__1

CROSS JOIN aql__2

**3. Calculate the percent of total**

Finally, the Percent of Total is determined by dividing the `total_sales`

by `_total_sales_of_all`

. Since `_total_sales_of_all`

remains constant, the percent of total will adjust according to `total_sales`

measure and its corresponding dimension

`Model order_items {`

...

measure total_sales {...}

measure _total_sales_of_all {...}

measure percent_of_total {

label: "Percent of Total"

type: 'number'

definition: @aql (order_items.total_sales*1.0) / order_items._total_sales_of_all ;;

}

}

*To maintain the decimal format for the percent of total measure, multiplying by 1.0 explicitly casts the left side to decimal.*

`explore {`

dimensions {

products.name

}

measures {

order_items.total_sales,

order_items.percent_of_total

}

}

- Result
- SQL

`WITH `

aql__1 AS (

SELECT

SUM(amount) AS total_sales

FROM order_items

),

aql__2 AS (

SELECT

product,

SUM(amount) AS value

FROM order_items

)

SELECT

aql__2.product,

aql__2.value,

(aql__2.value / aql__1.total) AS percent_per_total

FROM aql__2

CROSS JOIN aql__1

**4. Percent of total in one line**

The entire steps described above can also be straightforwardly condensed into one single-line expression.

`Model order_items {`

...

measure percent_of_total {

label: "Percent of Total"

type: 'number'

definition: @aql (sum(order_items.amount) * 1.0) / (sum(order_items.amount) | of_all(order_items));;

}

}

`explore {`

dimensions {

products.name

}

measures {

order_items.total_sales,

order_items.percent_of_total

}

}

- Result
- SQL

`WITH `

aql__1 AS (

SELECT

SUM(amount) AS total_sales

FROM order_items

),

aql__2 AS (

SELECT

product,

SUM(amount) AS value

FROM order_items

)

SELECT

aql__2.product,

aql__2.value,

(aql__2.value / aql__1.total) AS percent_per_total

FROM aql__2

CROSS JOIN aql__1