Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate average based on Date Table

Hi,
Please advise on how to solve this.

I have 3 Tables in my model, 1 Date Table, 1 Sales Table, 1 Product Table 

I am having problem calculating average of a Product sold based on the Date Table instead of averaging over the dates in Product Table. I have a one to many relationship propagating from Date Table to Sales and then to Product Table. When i put them together in a table visual, Sales Id, Avg Sales, Product, Average Product Amount ,how can i get the average amount of product to take into account those with 0 Sales

Date Table

DateMonth
6/23/2020June
6/22/2020June
6/21/2020June

Sales Table

SalesIdDateSales Amount
16/21/202010
16/22/202010
16/23/202020
26/21/20200
26/22/20200
26/23/202010

 

Product Table

Sales IdDateProductAmount
16/21/2020B10
16/22/2020B10
16/23/2020C20
26/23/2020A10

 


Desired Result: 

SalesIdAvg Sales AmtProductAvg Product Amt
113.3B(10+10)/3=6.67
113.3C20/3=6.67
23.3A10/3=3.33



Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    You can create a measure as below to get the average of product amount based on dates:

    Avg Product Amt = 
VAR _sumproAmount =
    CALCULATE (
        SUM ( 'Product'[Amount] ),
        ALLEXCEPT ( 'Product', 'Product'[Product] )
    )
VAR _countofDates =
    CALCULATE ( DISTINCTCOUNT ( 'Date'[Date] ) )
RETURN
    DIVIDE ( _sumproAmount, _countofDates )

    Best Regards

    Rena

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Create a common table sales Id

     

    Sales Id = distinct(union(all(Sales[SalesId]),all(Product[SalesId])))

     

    Join sales and product with date and sales id, Do not join sales and product. Now you can have data together

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak ,
      Thanks for the reply
      I stil don't quite get it, in my current model, my merged the SalesID and Date Column to be the primary key that connects to the Product Table. I also need other column information from the Product Table eg: serial number from the Product Table to be matched to the Sales ID.

      Thanks for your help.