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 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'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.

6 Replies

  • 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.

    • 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.