# of_all

## Definition

The function `of_all()`

is used when you want your metric to **not be evaluated against certain dimensions/grains**, even when that dimension/grain is present during exploration. **All filters applied on the excluded dimensions are also ignored**.

This is useful when you mix an aggregation of a lower grain with a higher grain, like when calculating Percent of Total

`exclude_grains()`

, `exclude()`

are aliases of `of_all()`

. We recommend using `of_all()`

when writing metrics to express your intent more clearly. E.g. `orders.total_orders | of_all(orders)`

reads more naturally than `orders.total_orders | exclude(orders)`

.

**Syntax**

`of_all(metric, model, ..., dimension, ...)`

`orders.total_orders | of_all(orders) // -> Total orders of all orders`

orders.total_orders | exclude(orders) // -> Total orders of all orders

orders.total_orders | of_all(orders.status) // -> Total orders of all order statuses

orders.total_orders | of_all(orders, keep_filters: true) // -> Total orders of all orders, keeping outer filters

**Input**

`metric`

: An AQL metric`model`

(**optional**,**repeatable**): The model that you want to exclude from the calculation, along with all dimensions within this model and related dimensions through an n-1 relationship`dimension`

(**optional**,**repeatable**): The dimension that you want to exclude from the calculation`keep_filters:`

(**optional**): A boolean value that specifies whether to keep the filters applied on the excluded dimensions. Default is`false`

.

`keep_filters`

is useful when you want the metric to be calculated on the filtered data of the excluded dimensions. For example, to calculate the percentage of each status of orders after certain date:

- You cannot use
`of_all(orders.status)`

because it would return the percentage over orders across all time. - Instead, use
`of_all(orders.status, keep_filters: true)`

to calculate the percentage over the filtered orders.

**Output**

A metric with the specified dimensions excluded from their Level of Detail (LoD) context.

## Sample Usages

Suppose you have an Ecommerce dataset that contains information about order items, and you want to calculate the percentage of sales contribution by each country:

`Model order_items {`

dimension quantity {}

dimension price {}

dimension country {}

dimension order_value {

definition: @aql order_items.price * order_items.quantity

}

measure total_amount {

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

}

}

Your **Percent of Total** metric would be calculated by dividing `total_orders`

of each country by the `total_orders`

of all countries. The definition of that metric will be as follows:

`Dataset ecommerce {`

models: [order_items]

metric percent_of_total {

definition: @aql

order_items.total_amount / (order_items.total_amount | of_all(order_items))

;;

}

}

With `of_all(order_items)`

, the `total_amount`

metric in the divisor will not be grouped or filtered by any dimensions in the `order_items`

model, as well as any other models that have n-1 relationship with it.

Result:

The Percent of Total is still correctly calculated when you include dimensions from `users`

model:

Note that, since we're using `of_all()`

without `keep_filters: true`

, the percentage is calculated over orders of all time. This approach is useful, for instance, when determining the percentage of sales attributed to the top five countries. From the results, we can observe that the top five countries contribute 54% of the total sales, and we can also see the contribution of each individual country to overall sales.

In the case we want to compare the top five countries against each other, we can use `of_all()`

with `keep_filters: true`

to calculate the percentage based on the filtered data.

` metric percent_of_total {`

definition: @aql

order_items.total_amount / (order_items.total_amount | of_all(order_items, keep_filters: true))

;;

}

With that, we can see that, while United States only contributes to 11% of all sales, they contribute to 21% of the sales of the top five countries.