Group-by queries
Group-by queries group the data for one measure by one dimension. You might use this query type to get a breakdown of Sales by Country, Profit by Segment, or COGS by Product. This type of query works with four different aggregation types.
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
Default Time Filter =
Latest monthDefault Currency =
US Dollar (USD)
Group-by with sum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Don't summarize or Sum | sum, total (optional) |
When your measure is sum-aggregated, Arria Answers calculates the sum-total value for each instance of the given dimension, and returns the values in descending order.
Query | Answer |
|---|---|
Tell me about Profit by Segment. | In July 2021, the Profit breakdown by Segment in descending order is Departmental ($1.4 million), Brand Store ($326,000), Channel Partners ($95,000), Chemists ($48,000) and Online (-$111,729). |
Tell me about Sales by Product. | In July 2021, the Sales breakdown by Product in descending order is Glower ($3.27 million), Nutrali ($3.07 million), Retine ($3.03 million), Camoide ($1.47 million), Karateon ($1.2 million) and Ointmo ($0.71 million). |
You can add some dimension instances if they are NOT from the group-by dimension.
Query | Answer |
|---|---|
Tell me about Sales by Product for Online. | In July 2021, the Sales breakdown by Product for Online in descending order is Karateon ($0.95 million), Retine ($0.57 million) and Nutrali ($319,000). |
Tip
You could filter the query further with a time filter or exclusion filter.
You can ask if the calculated sum totals are similar.
Query | Answer |
|---|---|
Are my Sales by Product values similar? | In July 2021, Products are dissimilar in terms of Sales. |
You can ask if the calculated sum totals are identical.
Query | Answer |
|---|---|
Are my Sales by Product values identical? | In July 2021, Products are not identical in terms of Sales. |
You can ask if the results set includes outliers.
Query | Answer |
|---|---|
Are there any outliers for Sales by Product? | In July 2021, no Products are outliers in terms of Sales. |
Tip
The outliers keyword works with sum-aggregated measures only.
Group-by with average aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Average | average, mean (optional) |
For this type of query, you add just one dimension to the Arria Answers custom visual. This can be a time-dimension (e.g. Year) or non-time dimension (e.g. Country). What matters is that you remove all other dimensions from the custom visual.
Assuming that Year is the only dimension:
Query | Answer |
|---|---|
What is Average of Sales by Year? | Overall, the average values for Average of Sales by year are 2020 ($182,000), 2021 ($190,000) and 2022 ($182,000). |
Assuming that Product is the only dimension:
Query | Answer |
|---|---|
What is Average of Sales by Product? | Overall, the average values for Average of Sales by Product are Glower with $232,000, Ointmo with $218,000, Retine with $182,000, Karateon with $182,000, Camoide with $153,000 and Nutrali with $139,000. |
When you group by a time dimension (e.g. Year), the calculated average values appear in chronological order. When you group by a non-time dimension (e.g. Product), the values appear in descending numerical order.
Note that you can also ask if the average values are similar or identical.
Query | Answer |
|---|---|
Are my Average of Sales by Product values similar? | In July 2021, Products are dissimilar in terms of Average of Sales. |
Are my Average of Sales by Product values identical? | In July 2021, Products are not identical in terms of Average of Sales. |
Important
You must set the measure's aggregation type to Average in Power BI. This changes the measure's name — e.g. from Sales to Average of Sales. You can give the measure an alias if you wish.
Group-by with maximum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Maximum | biggest, highest, largest, max, maximum, top (optional) |
When your measure is max-aggregated, Arria Answers finds the maximum value for each instance of the given dimension, and returns the highest of those values.
Query | Answer |
|---|---|
Tell me about Max of Sales by Product. | In July 2021, the maximum entry for Max of Sales by Product is Nutrali ($1.11 million). |
You can also request a specific number of values.
Query | Answer |
|---|---|
Top 3 values for Max of Sales by Product? | In July 2021, the maximum 3 entries for Max of Sales by Product are Nutrali ($1.11 million), Glower ($0.98 million) and Retine ($0.68 million). |
You can add some dimension instances if they are NOT from the group-by dimension.
Query | Answer |
|---|---|
Top 3 values for Max of Sales in Canada by Product? | In July 2021, the maximum 3 entries for Max of Sales in Canada by Product are Ointmo ($490,000), Nutrali ($487,000) and Glower ($398,000). |
Top 3 values for Max of Sales for Online by Product? | In July 2021, the maximum 3 entries for Max of Sales for Online by Product are Karateon ($285,000), Retine ($242,000) and Nutrali ($197,000). |
Tip
You could filter the query further with a time filter or exclusion filter.
You can also ask if the maximum values are similar or identical.
Query | Answer |
|---|---|
Are my Max of Sales by Product values similar? | In July 2021, Products are dissimilar in terms of Max of Sales. |
Are my Max of Sales by Product values identical? | In July 2021, Products are not identical in terms of Max of Sales. |
Important
You must set the measure's aggregation type to Maximum in Power BI. This changes the measure's name — e.g. from Sales to Max of Sales. You can give the measure an alias if you wish.
Group-by with minimum aggregation
Aggregation type for measure | Aggregation words for query |
|---|---|
Minimum | bottom, lowest, min, minimum, smallest (optional) |
When your measure is min-aggregated, Arria Answers finds the minimum value for each instance of the given dimension, and returns the lowest of those values.
Query | Answer |
|---|---|
Tell me about Min of Sales by Country. | In July 2021, the minimum entry for Min of Sales by Country is Mexico ($4,500). |
You can also request a specific number of values.
Query | Answer |
|---|---|
Bottom 3 values for Min of Sales by Country? | In July 2021, the minimum 3 entries for Min on Sales by Country are Mexico ($4,500), Germany ($4,900) and the United States of America ($7,600). |
You can add some dimension instances if they are NOT from the group-by dimension.
Query | Answer |
|---|---|
Bottom 3 values for Min of Sales for Retine by Country? | In July 2021, the minimum 3 entries for Min of Sales for Retine by Country are Canada ($18,000), Germany ($92,000) and Mexico ($236,000). |
Bottom 3 values for Min of Sales for Online by Country? | In July 2021, the minimum 3 entries for Min of Sales for Online by Country are Germany ($92,000), the France ($122,000) and Canada ($197,000). |
Tip
You could filter the query further with a time filter or exclusion filter.
You can also ask if the minimum values are similar or identical.
Query | Answer |
|---|---|
Are my Min of Sales by Country values similar? | In July 2021, Countries are dissimilar in terms of Min of Sales. |
Are my Min of Sales by Country values identical? | In July 2021, Countries are not identical in terms of Min of Sales. |
Important
You must set the measure's aggregation type to Minimum in Power BI. This changes the measure's name — e.g. from Sales to Min of Sales. You can give the measure an alias if you wish.
