Variance queries
Arria Answers supports several kinds of variance query.
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)
Target-based variance queries
Required aggregation type | Aggregation words for query |
|---|---|
Don't summarize or Sum | sum, total (optional) |
These queries compare two measures over a given time period. These queries return four types of responses: (1) the first measure is higher, (2) the first measure is lower, (3) the measures are nearly equal, and (4) the measures are equal.
Query | Answer |
|---|---|
Tell me about Sales vs COGS. | In July 2021, Sales are higher than COGS by $2.29 million (18%). |
Tell me about Sales compared to Profit. | In July 2021, Sales are higher than Profit by $10.99 million (86%). |
Tell me about Sales versus Target Sales. | In July 2021, Sales are lower than Target Sales by $0.53 million (4%). |
Important
You must set the aggregation type for BOTH measures to Don't Summarize or Sum in Power BI.
You can make the query more precise by adding dimension instances.
Query | Answer |
|---|---|
Tell me about Sales versus Target Sales for Retine in Canada? | In July 2021, Sales in Canada for Retine were higher than Target Sales by $43,000 (12%). |
Tip
You could filter the query further with a time filter or exclusion filter.
Time-based variance queries
Required aggregation type | Aggregation words for query |
|---|---|
Don't summarize or Sum | sum, total (optional) |
These queries ask how much one measure has changed over the given time period. These queries return three types of responses: (1) measure increased, (2) measure decreased, and (3) measure remained stable.
There are three types of time-based variance:
Single-period variance
These queries ask how much a measure has changed within a single time period. The time period must be a single month, quarter, or year.
The query type works with aggregated totals. If you ask how Sales changed in a specific month (e.g. June 2021), Arria Answers aggregates by day, then compares the first day's total to the last day's total. Similarly, if you ask how Sales changed in a specific quarter or year, Arria Answers aggregates by month, then compares the first month's total to the last month's total.
You must include the keyword change or vary — as shown below.
Query | Answer |
|---|---|
How did my Sales change in June 2021? | Sales increased by $34,000 (97%) during June 2021. |
How did my Sales vary during the last quarter? | Sales decreased by $6.22 million (50%) during Q2 2021. |
How did my Sales change in 2020? | Sales decreased by $357,000 (5%) during 2020. |
You can make the query more precise by adding dimension instances.
Query | Answer |
|---|---|
How did Sales of Retine in France change in 2020? | French Sales for Retine decreased by $470,000 (91%) during 2020. |
Tip
You could filter the query further with a time filter or exclusion filter.
Period-on-period variance
These queries ask how a measure has changed period-on-period, where period is month, quarter or year.
These queries must include the keyword change, changed, vary, or varied. In addition, they require a "period on period" or "period over period" phrase (where period = month, quarter, or year). Note that neither "period to period" nor "period-on-period" work.
Query | Answer |
|---|---|
How have my Sales changed month on month? | Sales increased by $6.1 million (100%) this month compared to last month. |
How have my Sales changed quarter on quarter? | Sales decreased by $610,000 (5%) this quarter compared to last quarter. |
How have my Sales changed year on year? | Sales decreased by $3.83 million (7%) this year compared to last year. |
Important
Period-on-period queries use equivalent values for the given time period. If the date is 31st July 2021 and you ask how Sales have changed year on year, Arria Answers compares the total for 1st January 2021 through 31st July 2021 to the total for the equivalent period during the previous year (1st January 2020 through 31st July 2020).
You can make the query more precise by adding dimension instances.
Query | Answer |
|---|---|
How have my Sales of Karateon in the United States of America changed year on year? | Sales in the United States of America for Karateon increased by $193,000 (7%) this year compared to last year. |
Tip
You could filter the query further with an exclusion filter.
Period year-on-year variance
These queries ask how one measure has changed for a specific month or quarter on a year-on-year basis.
For this type of query you must include a "P year on year" phrase, where P is either a specific month (e.g. July) or a specific quarter (e.g. Q1). You can use the phrase "year over year" instead of "year on year" if preferred.
Query | Answer |
|---|---|
How did Sales of Karateon change in June year on year? | Sales for Karateon decreased by $37,000 (13%) in June this year compared to the same period last year. |
How did Sales of Retine in France change in Q1 year on year? | Sales for Retine in France decreased by $0.51 million (92%) in Q1 this year compared to the same period last year. |
Tip
You could filter the query further with an exclusion filter.
Drilldown variance queries
Required aggregation type | Aggregation words for query |
|---|---|
Don't summarize or Sum | sum, total (optional) |
This is a more sophisticated type of variance query. It doesn't just identify a variance trend — it drills down into the data and provides more detailed information about drivers and offsets of the trend.
Tip
Drilldown works with both target-based variance and time-based variance queries. For target-based queries, you must set the aggregation type for BOTH measures to Don't Summarize or Sum in Power BI.
Here is a standard target-based variance query:
Query | Answer |
|---|---|
Tell me about Sales versus Target Sales in Canada. | In July 2021, Sales in Canada were lower than Target Sales by $111,000 (5%). |
With slight rephrasing, you can ask for drilldown analysis.
Query | Answer |
|---|---|
What drove Sales versus Target Sales in Canada. | In July 2021, Sales in Canada were lower than Target Sales by $111,000 (5%), driven by Ointmo, Karateon and Nutrali and offset by Retine and Camoide. |
The answer identifies the variance trend AND identifies which product sales drove or offset the trend. Ointmo, Karateon and Nutrali are drivers for the parent variance (their sales are also below target), while Retine and Camoide are offsets.
Tip
Other valid ways of phrasing the above query:
What caused the Sales versus Target Sales variance in Canada?
Why the Sales versus Target Sales trend in Canada?
You could include another dimension instance in the query to drill down further. For example:
Query | Answer |
|---|---|
What drove the Sales versus Target Sales trend for Retine in Canada? | In July 2021, Sales in Canada for Retine were higher than Target Sales by $43,000 (12%), driven by Brand Store and offset by Channel Partners. |
Tip
You could filter the query further with an exclusion filter.
Order of drilldown dimensions
Drilldown variance queries identify a parent variance trend, then perform a drilldown analysis using one dimension.
The drilldown dimension cannot be a dimension already represented in your query. For example, the following query includes France, which is an instance of Country dimension. Therefore, Arria Answers cannot use that dimension for breakdown.
Query | Answer |
|---|---|
Why did Sales in France change in Q1 year on year? | Sales in France decreased by $0.5 million (14%) in Q1 this year compared to the same period last year, driven by Retine, Camoide and Nutrali and offset by Karateon and Glower. |
There are two other dimensions — Product and Segment — in the dataset. Why is Product the drilldown dimension rather than Segment?
At the data configuration stage, the dimension fields were prioritized as follows: (1) Country, (2) Product, (3) Segment. The drilldown analysis doesn't use Country because a specific country is in the query. Therefore, it uses the next dimension in line — Product.
You can change the priority order in the Arria Answers wizard. To use Segment as the drilldown dimension, you would change the priority order to (1) Country, (2) Segment, (3) Product.
Query | Answer |
|---|---|
Why did Sales in France change in Q1 year on year? | Sales in France decreased by $0.5 million (14%) in Q1 this year compared to the same period last year, driven by Brand Store, Online and Chemists and offset by Departmental and Channel Partners. |
