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 ,

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

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's avatar
    kavishchaudhary
    New Member
    2 years ago

    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.