Calculate Percent of Total

Alternative

This is the AQL-centric approach to calculate Percent of Total. Holistics also provides a UI-friendly solution: Percent of Total

Introduction

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

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.

Recommended Prerequisites

• of_all: Exclude your metric from being evaluated against certain dimensions/grains.

Video Tutorial

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.

Measures and Metrics

You may see the use of measure in code snippets throughout this article. In Holistics, metrics defined inside data models are syntactically referred to as measure. We will use the word "metric" outside of code snippets to avoid confusions. Read more about Metrics here

Initial Setup

Model order_items {
  ...
  dimension id {...}
  dimension product {...}
  dimension amount {...}

}

Final Setup (Resuable Components)

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 ;;
  }
}

Final Setup (Single Line)

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 metric 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
  }
}

Open in AMQL playground

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 metric 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
  }
}

Open in AMQL playground

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 metric 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 metric, 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
  }
}

Open in AMQL playground

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
  }
}

Open in AMQL playground

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