Skip to main content

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 month

  • Default 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.

 

Top

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.

 

Top

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.

 

Top

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.

 

Top

×