Create Measure: Average Sales Per Week Per Country

[MichaelG1117]

Hi Everyone,

I need to create a measure "Average Sales Per Week Per Country". 

I created a date table with 'WeekNum'. 

I used the following to create the Sales per week per country and was going to put it through Power Query in order to make it "Average Sales Per Week Per Country" (the SumX function involved is Total Sales): 

Average Sales Per Week Per Country =

CALCULATE(

    Sumx('Orders','Orders'[OrderQuantity]*'Orders'[SalesPrice]), USERELATIONSHIP('Date Table'[Date], Orders[OrderDateKey]),

       Filter (ALL(Territory), Territory[Country] = SELECTEDVALUE(Territory[Country])),

       FILTER(All('Date Table'), 'Date Table'[Weeknum] = SELECTEDVALUE('Date Table'[Weeknum]) - 1)

When I put this into the "Values" of a Matrix Visual, however, it clears all of the data off of it...Even if I have "Countries" in the "Rows" section.  

Any help would be greatly appreciated. I took "User Relationship" out of the equation also and that didn't work. 

THANKS-

[Anonymous]

Hi @MichaelG1117 ,

Since you didn't provide the sample data and the relationship between the various data tables, and I can't manually create a test dataset to replicate your situation based on your description, I can only offer you a possible DAX solution:

Average Sales Per Week Per Country = 
CALCULATE(
    AVERAGEX(
        SUMMARIZE(
            'Orders',
            'Territory'[Country],
            'Date Table'[WeekNum],
            "Total Sales", 'Orders'[OrderQuantity] * 'Orders'[SalesPrice]
        ),
        [Total Sales]
    ),
    USERELATIONSHIP('Date Table'[Date], 'Orders'[OrderDateKey])
)

If this DAX doesn't work, it would also be helpful if you could provide sample data and data model relationships.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MichaelG1117]

Hi @Anonymous 

I used a previous measure that was essentially Order Amount = 'Orders' [Order Quantity] * 'Orders'[Sales Price] 

I then divided this Measure "Order Amount" by the total number of weeks in a new measure titled "Average Amount Per Country Per Week" 

Average Amount Per Country Per Week = 

('Orders'[Order Amount]/55) 

I threw this in the Matrix and got this: 

 These are the correct numbers but as you can see the TOTAL is not averaged. 

I also created a "Total Sales" Column in the date table and used that in your initial table but the numbers still come out wonky: 

I'm wondering if there is a way to use SUMX with [Total Order Amount] above to ge the proper numbers. Or if there's any solution with regard to average the TOTAL AMOUNT in the Matrix. 

Again, thanks - if not, np  ---

[Anonymous · Accepted solution]

Hi @MichaelG1117 ,

Maybe you can try this DAX:

Average Amount Per Country Per Week = 
VAR Total_Order = SUMX ( 'Order', 'Order'[SalesPrice] * 'Order'[OrderQuetity] )
RETURN
IF (
    ISFILTERED ( 'Order'[Country] ),
    Total_Order,
    Total_Order/55
)

And here is my test data:

What this DAX means is that if the Country fields are not filtered, their respective corresponding sum values are returned, and if they are filtered (AVG rows) the average value is returned. It is mainly these codes that play a role:

IF (
    ISFILTERED ( 'Order'[Country] ),
    Total_Order,
    Total_Order/55
)

You can change the rest to suit your needs.


Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MichaelG1117]

THANKS! I tweaked it a little bit so here was the final script but it worked! Thank you!

 "Total Order" has to be divided by 6 because there's 6 countries and "Total Order Amount" has to be divided by 55 because there's 55 total weeks in the period I'm looking for. 

Thanks again for your help, I really appreciate it. 

[MichaelG1117]

Hi @Anonymous 

Thanks for your response.  Everything in that equation is good except it does not allow me to put in: 

 "Total Sales", 'Orders'[OrderQuantity] * 'Orders'[SalesPrice]

I replaced this line with what it did allow, namely a measure I had created previously "Order Amount": 

Order Amount = CALCULATE(Sumx('Orders','Orders'[OrderQuantity]*'Orders'[SalesPrice]), USERELATIONSHIP('Date Table'[Date], Orders[OrderDateKey]))

But when I put this in the numbers came out wrong on the Matrix Visual. 

Here is the Data for the "Orders" Table: 

 

Here are the relationships: 

 

I created the date table. Do I need to connect it with "Territories"? 

Or do I just need to find a way to write 'Orders'[OrderQuantity]*'Orders'[SalesPrice] in your equation in a way that works? 

I am not allowed to created any calculated columns. 

Again, thanks for your help and any further help would be greatly appreciated. 

[kavishchaudhary]

I want to understand how the no. of weeks is 55. According to my calculations the difference between 24 jan 2013 and 27 Jan 2014 is 53 weeks. Can you elaborate? I am solving the same question.