Basic aggregation queries
Basic aggregation queries return a single aggregated value for one measure.
There are five aggregation types — Sum, Average, Maximum, Minimum, and Mode — and this topic covers each.
This topic is organized as follows:
Starting assumptions
For each example in this topic:
The data is the sample dataset
The date is July 31, 2021
Aggregation type for measures =
Don't summarizeDefault Time Filter =
Latest monthDefault Currency =
US Dollar (USD)
Sum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Don't summarize or Sum | sum, total (optional) |
This query type returns the sum-aggregated total for one measure.
Query | Answer |
|---|---|
What are my Sales? | In July 2021, Sales are $12.75 million. |
You can make the query more precise by adding dimension instances.
Query | Answer |
|---|---|
What are my Sales of Retine? | In July 2021, Sales for Retine are $3.03 million. |
What are my Sales of Retine in Canada? | In July 2021, Sales in Canada for Retine are $358,000. |
Tip
You could filter the query further with a time filter or exclusion filter.
Important
DEFAULT TIME FILTERING — ALL AGGREGATION TYPES
When the query has no time filter, Arria Answers uses the default time filter. Per the starting assumptions, the Default Time Filter = Latest month and the date is 31st July 2021. Therefore, each answer returns data for July 2021.
The default time filter works only because the Month and Year fields are selected. If the relevant date fields weren't selected, the query What are my Sales? would return the sum total for all sales figures in your dataset.
Average aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Don't summarize or Average | average, mean |
This query type returns the average value for one measure. For average aggregation, you add just one dimension to the Arria Answers custom visual. This can be a time dimension (e.g. Year) or a non-time dimension (e.g. Country). You must remove the other dimensions.
Assuming that Country is the only dimension:
Query | Answer |
|---|---|
What are average Sales for France ? | Overall, the only value for average Sales in France is $189,000. |
What are average Sales for Canada ? | Overall, the only value for average Sales in Canada is $185,000. |
Important
The query must include one dimension instance — that is, one instance of the selected dimension.
Assuming that Year is the only dimension:
Query | Answer |
|---|---|
What are average Sales for 2020? | In 2020, the only value for average Sales is $182,000. |
What are average Sales for 2021? | In 2021, the only value for average Sales is $190,000. |
Tip
You could filter the query further with a time filter or exclusion filter.
Important
You must set the measure's aggregation type to Don't summarize or Average in Power BI. Setting the type to Average changes the measure's name — e.g. from Sales to Average of Sales.
Maximum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Don't summarize or Maximum | biggest, highest, largest, max, maximum, top |
This query type returns the maximum value for one measure.
Query | Answer |
|---|---|
What are my highest Sales? | In July 2021, the maximum value of Sales is $1.11 million. |
What are the largest Sales of Glower? | In July 2021, the maximum value of Sales for Glower is $0.98 million. |
What are the top Sales of Glower in France? | In July 2021, the maximum value of Sales in France for Glower is $0.62 million. |
Tip
You could filter the query further with a time filter or exclusion filter.
Important
You must set the measure's aggregation type to Don't summarize or Maximum in Power BI. Setting the type to Maximum changes the measure's name — e.g. from Sales to Max of Sales.
Minimum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Don't summarize or Minimum | bottom, lowest, min, minimum, smallest |
This query type returns the minimum value for one measure.
Query | Answer |
|---|---|
What are my lowest Sales? | In July 2021, the minimum value of Sales is $4,500. |
What are my smallest Sales of Ointmo? | In July 2021, the minimum value of Sales for Ointmo is $4,500. |
What are my bottom Sales of Ointmo in France? | In July 2021, the minimum value of Sales for Ointmo in France is $12,000. |
Tip
You could filter the query further with a time filter or exclusion filter.
Important
You must set the measure's aggregation type to Don't summarize or Minimum in Power BI. Setting the type to Minimum changes the measure's name — e.g. from Sales to Min of Sales.
Mode aggregation
Aggregation type for measure | Aggregation word |
|---|---|
Don't summarize | mode |
This query type returns the mode — i.e. the most common value within the queried dataset. The first example below uses mode in the query. The second and third examples show different ways of phrasing the same query.
Query | Answer |
|---|---|
What is the mode of Sales? | In July 2021, the mode values of Sales are $17,000 and $19,000. |
What is the most common value of Sales? | In July 2021, the mode values of Sales are $17,000 and $19,000. |
What value of Sales occurs the most? | In July 2021, the mode values of Sales are $17,000 and $19,000. |
You can make the query more precise by adding dimension instances and/or a time filter.
Query | Answer |
|---|---|
Mode for Sales in France last year? | In 2020, the mode values of Sales in France were $10,000 and $14,000. |
Mode for Sales of Retine in France last year? | In 2020, the mode value of Sales for Retine in France was $125,000. |
Important
There can be multiple mode values. Also, the mode calculation is performed on rounded values — i.e. those displayed in the answers to queries — not the raw values in your dataset.
