Weighted average of columns in different tables

[jabrouni1]

Hi,

I have a table called Products and another table called Sales. They are related by productId. I need to find the weighted average for a selected list of products in Products table. The formula is (Product1.UnitCost * Sales[ProductId1].ItemsSold + Product2.UnitCost * Sales[ProductId2].ItemsSold) + So on/ Total sum of items sold of the chos products. How can i derive the DAX formula for this?

Sample data

Products
			
ProductId   |	Name  |	Description   |	UnitItemCost
------------|---------|---------------|----------------
id1	    |	Name1 |	Description1  |	10
id2	    |	Name2 |	Description2  |	20
id3	    |	Name3 |	Description3  |	30
			
			
Sales	
		
ProductId  |	ItemsSold
-----------|--------------		1714.126984
Id1	   |	20		
id2	   |	30		
id1	   |	10		
id2        |    40		
id3	   |	50		
id3	   |	39		

Average unit cost = 1714.126984    (10*30+20*70+30*89)/189

[v-juanli-msft · Accepted solution]

Hi @jabrouni1

Average unit cost = 1714.126984    (10*30+20*70+30*89)/189

It is werid, the result of this formula should be 23, is anything missing here?

If the result of this formula should be 23, I have a solution as below.

1. Create relationships between two tables

Products(one) to Sales(many), Cross filter direction is Both.

2. In Products table, create columns

Sales.ItemsSold = CALCULATE(SUM(Sales[ItemsSold]),ALLEXCEPT(Products,Products[ProductId]))

sum = [UnitItemCost]*[Sales.ItemsSold]

total = SUM(Sales[ItemsSold])

weighted average = SUM(Products[sum])/[total]

Best Regards

Maggie

[v-juanli-msft]

Hi @jabrouni1

Could you share an example data?

The formula is (Product1.UnitCost * Sales[ProductId1].ItemsSold + Product2.UnitCost * Sales[ProductId2].ItemsSold) 

Do "Product1.UnitCost" and "Sales[ProductId1].ItemsSold" refer to columns in  Sales table?

find the weighted average for a selected list of products in Products table

Do you mean when you put column "products" from Products table in a slicer, then select some products, the measure should calculate weighted average for these selected products?

Also, I can't see your formula have any relation with weighted average.

Best Regards

Maggie

[jabrouni1]

Hello,

I have added sample data

[jabrouni1]

Hi,

I have a table called Products and another table called Sales. They are related by productId. I need to find the weighted average for a selected list of products in Products table. The formula is (Product1.UnitCost * Sales[ProductId1].ItemsSold + Product2.UnitCost * Sales[ProductId2].ItemsSold) + So on/ Total sum of items sold of the chos products. How can i derive the DAX formula for this?