Calculate average based on Date Table

[Anonymous]

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

Date	Month
6/23/2020	June
6/22/2020	June
6/21/2020	June

Sales Table

SalesId	Date	Sales Amount
1	6/21/2020	10
1	6/22/2020	10
1	6/23/2020	20
2	6/21/2020	0
2	6/22/2020	0
2	6/23/2020	10

Product Table

Sales Id	Date	Product	Amount
1	6/21/2020	B	10
1	6/22/2020	B	10
1	6/23/2020	C	20
2	6/23/2020	A	10

Desired Result: 

SalesId	Avg Sales Amt	Product	Avg Product Amt
1	13.3	B	(10+10)/3=6.67
1	13.3	C	20/3=6.67
2	3.3	A	10/3=3.33

Thanks in advance.

[amitchandak]

@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]

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.

[Anonymous]

Hi @Anonymous ,

Could you please just provide your desired result? Whether the below screen shot is what you want to get?

Best Regards

Rena

[Anonymous]

Hi @Anonymous ,
I have already updated the desired result in the post. Basically, i would like the average over the Date from the Date Table instead of the number of entries in the Product Table. Is that doable?

Thanks

[Anonymous · Accepted solution]

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

[amitchandak]

@Anonymous ,

Sum(Product[Product Amount]) +0