# 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 summarize

• Default Time Filter = Latest month

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

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.

### Tip

You could filter the query further with a time filter or exclusion filter.

Top

## 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

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

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?

### 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

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.

Top

### 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

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

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