# average sales of the non test branches in a test period

• ### Question

• Hi everybody,

I would be very happy, if somebody can help me. I'm quite lost. Thanks a lot in advanced. Hope, this question is interesting for you as well!

I work at a retail company, which has 5-10 branches in different cities. My company implemented a new store concept in a few test branches. Now I would like to compare the "average sales amount of the test branches" with "the average sales amount of the non test branches" in the test period and the city in order to evalute, if the new store concept increases the sales.

I have three tables:

Dim_Calendar with all Dates

Dim_Test_Branches

This table contains only the test branches with the new store concept

 City BranchNr ID Teststart Testend New York 2 New York2 2018-02-02 2018-03-04 New York 4 New York4 2018-03-03 2018-04-05 Boston 1 Boston1 2018-02-04 2018-05-05 Boston 5 Boston5 2018-02-05 2018-05-05 Chicago 3 Chicago3 2018-01-02 2018-04-03

Fact_Sales

This table contains all sales of all branches.

 City Branch Date Sales ID New York 5 2018-02-20 336 New York5 New York 5 2018-02-21 239 New York5 New York 5 2018-02-22 370 New York5 New York 5 2018-02-24 394 New York5 New York 5 2018-02-25 343 New York5 New York 5 2018-02-26 265 New York5 New York 5 2018-02-27 156 New York5 New York 5 2018-02-28 393 New York5 Boston 1 2018-01-02 310 Boston1 Boston 1 2018-01-03 291 Boston1 Boston 1 2018-01-04 151 Boston1 Boston 1 2018-01-07 179 Boston1 Boston 1 2018-01-08 381 Boston1 Boston 1 2018-01-09 136 Boston1 Boston 1 2018-01-10 316 Boston1 Boston 1 2018-01-11 218 Boston1

My suggestion for the DAX measure for the average sales amount of the test branches in the test period would be

Sales_AVG_Test=CALCULATE(AVERAGE([Sales]);FILTER(fact_Sales;

AND([Date]>=RELATED(Dim_Test_Branches[Teststart]);

[Date]<=RELATED(Dim_Test_Branches[Testend]))))

But I struggle with the DAX Measure for the "Average Sales of the non test branches in the test period".

My idea of the Pivot-Table would be:

 City Branch Teststart Testend AVG Sales current test Branch AVG Sales of all Test Branch in the City New York 2 2018-02-02 2018-03-04 23454 22987 New York 4 2018-03-03 2018-04-05 18756 16534 Boston 1 2018-02-04 2018-05-05 19856 18764 Boston 5 2018-02-05 2018-05-05 22434 20395 Chicago 3 2018-01-02 2018-04-03 25498 22345

Explanation:

In New York we have 5 Branches. Branch Nr. 2 and 4 are Test branches.

In the red cell I would like to have the average sales amount of Branch 1, 3 and 5 in the Test period of Nr. 2, which is from 2018-02-02 to 2018-03-04.

Thanks again for any suggestion, how I can solve this task.

John

• Edited by Saturday, July 21, 2018 5:21 PM
Saturday, July 21, 2018 5:18 PM

• Hi John.C_,

Please delete the relationship between table dim_Branches and fact_sales. See below screenshot:

Then you can create the measures as below DAX formula：

```AVG_TagesSales_Testfiliale_TZ :=
VAR StartDate =
IF (
HASONEVALUE ( dim_Branches[Teststart] ),
VALUES ( dim_Branches[Teststart] )
)
VAR EndDate =
IF ( HASONEVALUE ( dim_Branches[Testend] ), VALUES ( dim_Branches[Testend] ) )
VAR City1 =
IF ( HASONEVALUE ( dim_Branches[City] ), VALUES ( dim_Branches[City] ) )
VAR BranchNr1 =
IF ( HASONEVALUE ( dim_Branches[BranchNr] ), VALUES ( dim_Branches[BranchNr] ) )
RETURN
CALCULATE (
AVERAGE ( 'fact_sales'[Sales] ),
FILTER (
'fact_sales',
fact_sales[City] = City1
&& fact_sales[BranchNr] = BranchNr1
&& fact_sales[Salesdate] >= StartDate
&& fact_sales[Salesdate] <= EndDate
)
)

AVG sales of all NON test :=
VAR StartDate =
IF (
HASONEVALUE ( dim_Branches[Teststart] ),
VALUES ( dim_Branches[Teststart] )
)
VAR EndDate =
IF ( HASONEVALUE ( dim_Branches[Testend] ), VALUES ( dim_Branches[Testend] ) )
VAR City1 =
IF ( HASONEVALUE ( dim_Branches[City] ), VALUES ( dim_Branches[City] ) )
RETURN
CALCULATE (
AVERAGE ( 'fact_sales'[Sales] ),
FILTER (
'fact_sales',
fact_sales[City] = City1
&& fact_sales[Salesdate] >= StartDate
&& fact_sales[Salesdate] <= EndDate
&& NOT (
CONTAINS (
FILTER (
dim_Branches,
dim_Branches[City] = City1
&& dim_Branches[Test Branch?] = "yes"
),
[BranchNr], fact_sales[BranchNr]
)
)
)
)```

After that, please drag the fields City, BranchNr, Teststart and Testend from table dim_Branches, this is very important. Otherwise, this might not work. See below screenshot tested with your sample data:

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Tuesday, July 24, 2018 7:52 AM

### All replies

• Hi John.C_,

>>>Thanks again for any suggestion, how I can solve this task.

If I understand you correctly, you might want to change the design of your Dim_Test_Branches, add all the city and BranchNr to this table, if it is a test branch, then add teststard and testend, if it is not a test branch, then leave it as blank. Then you can write a measure called [AVG Sales] as below:

```[AVG Sales] =
IF (
ISBLANK ( Dim_Test_Branches[Teststart] )
&& ISBLANK ( Dim_Test_Branches[Testsend] ),
AVERAGE ( [Sales] ),
CALCULATE (
AVERAGE ( [Sales] ),
FILTER (
fact_Sales,
AND (
[Date] >= RELATED ( Dim_Test_Branches[Teststart] ),
[Date] <= RELATED ( Dim_Test_Branches[Testend] )
)
)
)
)```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, July 23, 2018 5:25 AM
• Hi Willson,

thank you so much for your answer! Your answer helps a lot, but unfortunately in your solution we  calculate the average sales amount of all years for the NON test branches. But I would like to have the average sales amount for the non test branches in the same period like the current test branch.

Example:

In New York we have 5 Branches. Branch Nr. 2 and 4 are Test branches.

 City Branch Teststart Testend AVG Sales of current test Branch AVG sales of all NON test branch in the city in the test period of the current test branch New York 2 2018-02-02 2018-03-04 23454 22987 New York 4 2018-03-03 2018-04-05 18756 16534 Boston 1 2018-02-04 2018-05-05 19856 18764 Boston 5 2018-02-05 2018-05-05 22434 20395 Chicago 3 2018-01-02 2018-04-03 25498 22345

In the
red cell I would like to have the average sales amount of the NON test branch 1, 3 and 5 in the Test period of Nr. 2, which is from 2018-02-02 to 2018-03-04.

In the orange cell I would like to have the average sales amount of NON test Branch 1, 3 and 5 in the Test period of Nr. 4, which is from 2018-03-03 to 2018-04-05.

Thanks again for your time and effort! :-)

John

P.S. And thanks a lot for you suggestion regarding my dim_branches. I changed it to

 City Branch Test Branch? ID Teststart Testend New York 1 no New York1 New York 2 yes New York2 2018-02-02 2018-03-04 New York 3 no New York3 New York 4 yes New York4 2018-03-03 2018-04-05 New York 5 no New York5 Boston 1 yes Boston1 2018-02-04 2018-05-05 Boston 2 no Boston2 Boston 3 no Boston3 Boston 4 no Boston4 Boston 5 yes Boston5 2018-02-05 2018-05-05 Chicago 1 no Chicago1 Chicago 2 no Chicago2 Chicago 3 yes Chicago3 2018-01-02 2018-04-03 Chicago 4 no Chicago4 Chicago 5 no Chicago5

• Edited by Monday, July 23, 2018 7:04 AM
Monday, July 23, 2018 7:01 AM
• Hi John.C_,

>>>In the red cell I would like to have the average sales amount of the NON test branch 1, 3 and 5 in the Test period of Nr. 2, which is from 2018-02-02 to 2018-03-04.
In the orange cell I would like to have the average sales amount of NON test Branch 1, 3 and 5 in the Test period of Nr. 4, which is from 2018-03-03 to 2018-04-05.

In this scenario, you just need to add several condition for AVG sales of all NON test branch in the city in the test period of the current test branch. See below DAX formula:

```[AVG sales of all NON test branch in the city in the test period of the current test branch] =
VAR City1 =
MAX ( Dim_Test_Branches[City] )
VAR Branch1 =
MAX ( Dim_Test_Branches[BranchNr] )
RETURN
CALCULATE (
AVERAGE ( [Sales] ),
FILTER (
fact_Sales,
[Date] >= RELATED ( Dim_Test_Branches[Teststart] )
&& [Date] <= RELATED ( Dim_Test_Branches[Testend] )
&& [City] = City1
&& [Branch] <> Branch1
&& RELATED ( Dim_Test_Branches[Test Branch?] ) = "no"
)
)
```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, July 23, 2018 7:32 AM
• Hi Willson,

thanks a lot again! We are getting closer :-) But with MAX ( Dim_Test_Branches[BranchNr] ) I get the max-number of the count of branches and not the current test branch. Or do I miss something?

Unfortunately it doesn't work :-( The measure is empty :-(    Sorry :-(

 City Branch Teststart Testend AVG_TagesSales_Testfiliale_TZ AVG sales of all NON test Boston 1 04.02.2018 05.05.2018 164,71 Boston 5 05.02.2018 05.05.2018 162,85 Chicago 3 02.01.2018 03.04.2018 172,56 New York 2 02.02.2018 04.03.2018 186,04 New York 4 03.03.2018 05.04.2018 169,91 Gesamtergebnis 168,48

• Edited by Monday, July 23, 2018 8:20 AM
Monday, July 23, 2018 8:18 AM
• Hi John.C_,

>>>thanks a lot again! We are getting closer :-) But with MAX ( Dim_Test_Branches[BranchNr] ) I get the max-number of the count of branches and not the current test branch. Or do I miss something?
In this scenario, please try below DAX formula:

```[AVG sales of all NON test branch in the city in the test period of the current test branch] =
CALCULATE (
AVERAGE ( [Sales] ),
FILTER (
fact_Sales,
[Date] >= RELATED ( Dim_Test_Branches[Teststart] )
&& [Date] <= RELATED ( Dim_Test_Branches[Testend] )
&& [City] = MAX ( Dim_Test_Branches[City] )
&& [Branch] <> MAX ( Dim_Test_Branches[BranchNr] )
&& RELATED ( Dim_Test_Branches[Test Branch?] ) = "no"
)
)```
OR
```[AVG sales of all NON test branch in the city in the test period of the current test branch] =
CALCULATE (
AVERAGE ( [Sales] ),
FILTER (
fact_Sales,
[Date] >= RELATED ( Dim_Test_Branches[Teststart] )
&& [Date] <= RELATED ( Dim_Test_Branches[Testend] )
&& [City] = RELATED ( Dim_Test_Branches[City] )
&& [Branch] <> RELATED ( Dim_Test_Branches[BranchNr] )
&& RELATED ( Dim_Test_Branches[Test Branch?] ) = "no"
)
)```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, July 23, 2018 8:24 AM
• Hi Willson,

thanks again :-)

I tried the version with Related(), but no results as well ... :-( Sorry.

Any other ideas? Would be great, because I'm still lost :-( Thanks!

John

Monday, July 23, 2018 9:00 AM
• Hi John.C_,

>>>Any other ideas? Would be great, because I'm still lost :-( Thanks!
Please try AVERAGEX, see if it will work.

```[AVG sales of all NON test branch in the city in the test period of the current test branch] =
AVERAGEX (
FILTER (
fact_Sales,
[Date] >= RELATED ( Dim_Test_Branches[Teststart] )
&& [Date] <= RELATED ( Dim_Test_Branches[Testend] )
&& [City] = RELATED ( Dim_Test_Branches[City] )
&& [Branch] <> RELATED ( Dim_Test_Branches[BranchNr] )
&& RELATED ( Dim_Test_Branches[Test Branch?] ) = "no"
),
SUM ( fact_Sales[Sales] )
)```
If it is not work for you, would you mind typing out 5-10 rows of example data for these table, then showing what results you are expecting based on those sample data? It is much better if you can share the EXCEL work book. Do mask sensitive data before uploading.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, July 23, 2018 9:15 AM
• Hi Willson,

what a service! Thanks a lot!

But how can I verify my account in order to insert a link into my messages?

Thanks a lot again!

John

Monday, July 23, 2018 9:42 AM
• Or does this work? https://1drv.ms/x/s!Alr5uy5VvtShtSYLo4aettBVHAa8
Monday, July 23, 2018 9:42 AM
• Excel 2016 Pro Plus with Power Query (aka Get & Transform)
Compare change in sales in different stores in same date range.
No PivotTables used.
http://www.mediafire.com/file/kw2tw8ttj9so1i1/07_23_18.xlsx/file

Tuesday, July 24, 2018 12:22 AM
• Hi John.C_,

Please delete the relationship between table dim_Branches and fact_sales. See below screenshot:

Then you can create the measures as below DAX formula：

```AVG_TagesSales_Testfiliale_TZ :=
VAR StartDate =
IF (
HASONEVALUE ( dim_Branches[Teststart] ),
VALUES ( dim_Branches[Teststart] )
)
VAR EndDate =
IF ( HASONEVALUE ( dim_Branches[Testend] ), VALUES ( dim_Branches[Testend] ) )
VAR City1 =
IF ( HASONEVALUE ( dim_Branches[City] ), VALUES ( dim_Branches[City] ) )
VAR BranchNr1 =
IF ( HASONEVALUE ( dim_Branches[BranchNr] ), VALUES ( dim_Branches[BranchNr] ) )
RETURN
CALCULATE (
AVERAGE ( 'fact_sales'[Sales] ),
FILTER (
'fact_sales',
fact_sales[City] = City1
&& fact_sales[BranchNr] = BranchNr1
&& fact_sales[Salesdate] >= StartDate
&& fact_sales[Salesdate] <= EndDate
)
)

AVG sales of all NON test :=
VAR StartDate =
IF (
HASONEVALUE ( dim_Branches[Teststart] ),
VALUES ( dim_Branches[Teststart] )
)
VAR EndDate =
IF ( HASONEVALUE ( dim_Branches[Testend] ), VALUES ( dim_Branches[Testend] ) )
VAR City1 =
IF ( HASONEVALUE ( dim_Branches[City] ), VALUES ( dim_Branches[City] ) )
RETURN
CALCULATE (
AVERAGE ( 'fact_sales'[Sales] ),
FILTER (
'fact_sales',
fact_sales[City] = City1
&& fact_sales[Salesdate] >= StartDate
&& fact_sales[Salesdate] <= EndDate
&& NOT (
CONTAINS (
FILTER (
dim_Branches,
dim_Branches[City] = City1
&& dim_Branches[Test Branch?] = "yes"
),
[BranchNr], fact_sales[BranchNr]
)
)
)
)```

After that, please drag the fields City, BranchNr, Teststart and Testend from table dim_Branches, this is very important. Otherwise, this might not work. See below screenshot tested with your sample data:

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Tuesday, July 24, 2018 7:52 AM
• Hi Herbert,

sorry for my late reply and thank you so much for your time, effort and you suggestion.

But in reality we have 9.000 branches and about 1.800 test branches all over the world. So your solution would be possible for sure, but would be quite an effort ... But it is an interessting approach!

Thanks again!
John

Wednesday, July 25, 2018 7:15 PM
• Hi Willson,

So great! Thanks again a lot for your time and your work!

I also had "Values()" in mind for a solution approach, but didn't get so far like you! Therefore thanks a lot again!

John

Wednesday, July 25, 2018 7:30 PM