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.

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.