Forum Discussion

MichaelG1117's avatar
MichaelG1117
Helper I
2 years ago
Solved

Create Measure: Average Sales Per Week Per Country

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 wa...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
MichaelG1117
Helper I
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    MichaelG1117
    Helper I
    2 years ago

    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.