locked
average sales of the non test branches in a test period RRS feed

  • 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 John.C_ Saturday, July 21, 2018 5:21 PM
    Saturday, July 21, 2018 5:18 PM

Answers

  • Hi John.C_,

    Thanks for your response.

    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 for your question.

    >>>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 John.C_ Monday, July 23, 2018 7:04 AM
    Monday, July 23, 2018 7:01 AM
  • Hi John.C_,

    Thanks for your response.

    >>>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 John.C_ Monday, July 23, 2018 8:20 AM
    Monday, July 23, 2018 8:18 AM
  • Hi John.C_,

    Thanks for your response.

    >>>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_,

    Thanks for your response.

    >>>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_,

    Thanks for your response.

    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