Weighted Average Cost where levels of granularity exist between multiple tables

[Anonymous]

I have a data model that looks like this.

The two lower tables are connected with using a one-to-many relationship with the field called Agreement ID.

I have two levels of granularity in both lower tables, however they are not the same things.  The load table's lowest level of granularity is the load, and the cost table's lowest level of granularity is the cost group.

Here I show the contents of both tables.  I want to be able to calculate the total cost, average cost, and most importantly the weighted average cost. 

As you can see, Agreement 1, Load 1 needs to be calculated against the three Cost Groups of Agreement 1.

and so forth.  Ultimately ending with Agreement 2, Load 3 being calculated against the four Cost Croups of Agreement 2.

Summarizing it would like like this:

Hard pasted to avoid having to create data.

Agreement ID	Load	Volume	Cost Group	Cost	Formula	Extended Cost		Weighted Avg
1	1	50	A	$4.80	50*4.80	$240.00		$0.54
1	1	50	B	$7.20	50*7.20	$360.00		$0.81
1	1	50	C	$2.06	50*2.06	$103.00		$0.23
1	2	75	A	$4.80	75*4.80	$360.00		$0.81
1	2	75	B	$7.20	75*4.80	$540.00		$1.21
1	2	75	C	$2.06	75*4.80	$154.50		$0.35
1	3	100	A	$4.80	100*4.80	$480.00		$1.08
1	3	100	B	$7.20	100*4.80	$720.00		$1.62
1	3	100	C	$2.06	100*4.80	$206.00		$0.46
2	1	40	A	$5.60	40*5.60	$224.00		$0.50
2	1	40	B	$8.12	40*8.120	$324.80		$0.73
2	1	40	C	$1.50	40*1.50	$60.00		$0.13
2	1	40	D	$6.34	40*6.340	$253.60		$1.15
2	2	100	A	$5.60	100*5.60	$560.00		$1.26
2	2	100	B	$8.12	100*8.120	$812.00		$1.82
2	2	100	C	$1.50	100*1.50	$150.00		$0.34
2	2	100	D	$6.34	100*6.340	$634.00		$2.88
2	3	80	A	$5.60	80*5.60	$448.00		$1.01
2	3	80	B	$8.12	80*8.120	$649.60		$1.46
2	3	80	C	$1.50	80*1.50	$120.00		$0.27
2	3	80	D	$6.34	80*6.340	$507.20		$2.31
	Sum	1555			Sum	$7,906.70		$20.97
					Average	$376.51
	Cost Group	Volume	Cost Group	Average	Weighted Avg	Ext. Weighted
	A	445	A	$5.20	$5.20	$385.33
	B	445	B	$7.66	$7.65	$132.25
	C	395	C	$1.78	$1.78	$518.56
	D	180	D	$6.34	$6.34	$215.50

This is what it would look breoken down.

Agreement ID	Load	Volume	Cost Group	Cost	Formula	Extended Cost	Weighted Average
1	1	50	A	$4.80	50*4.80	$240.00	$0.54
1	2	75	A	$4.80	75*4.80	$360.00	$0.81
1	3	100	A	$4.80	100*4.80	$480.00	$1.08
2	1	40	A	$5.60	40*5.60	$224.00	$0.50
2	2	100	A	$5.60	100*5.60	$560.00	$1.26
2	3	80	A	$5.60	80*5.60	$448.00	$1.01
							$5.20
Agreement ID	Load	Volume	Cost Group	Cost	Formula	Extended Cost	Weighted Average
1	1	50	B	$7.20	50*7.20	$360.00	$0.81
1	2	75	B	$7.20	75*4.80	$540.00	$1.21
1	3	100	B	$7.20	100*4.80	$720.00	$1.62
2	1	40	B	$8.12	40*8.120	$324.80	$0.73
2	2	100	B	$8.12	100*8.120	$812.00	$1.82
2	3	80	B	$8.12	80*8.120	$649.60	$1.46
							$7.65
Agreement ID	Load	Volume	Cost Group	Cost	Formula	Extended Cost	Weighted Average
1	1	50	C	$2.06	50*2.06	$103.00	$0.23
1	2	75	C	$2.06	75*4.80	$154.50	$0.35
1	3	100	C	$2.06	100*4.80	$206.00	$0.46
2	1	40	C	$1.50	40*1.50	$60.00	$0.13
2	2	100	C	$1.50	100*1.50	$150.00	$0.34
2	3	80	C	$1.50	80*1.50	$120.00	$0.27
							$1.78
Agreement ID	Load	Volume	Cost Group	Cost	Formula	Extended Cost	Weighted Average
2	1	40	D	$6.34	40*6.340	$253.60	$1.15
2	2	100	D	$6.34	100*6.340	$634.00	$2.88
2	3	80	D	$6.34	80*6.340	$507.20	$2.31
							$6.34

So there are two issues that I am having and need help with.

First, in Power BI I create a table and bring in Agreement ID from the Agreement table.

Then I bring in The Load and Volume Columns from the Load table.

Then when I try to bring in the Cost Group from the Cost table it errs

The second problem I have is writing the DAX expression to get the weighted average cost.

WAC = Cost * ( Volume / Total Volume)

The only problem is my cost is in one table and my volume is in the other table, and PBI can't determine the relationship between these fields either.

I was thinking, I might be able to summarize the volume to get rid of the load level details, but I would still have the cost groups with worry about.  Any ideas. Thank you.

[Jos_Woolley]

Hi,

Sounds like the relationship between Cost and Agreement is unidirectional. You'll need to change it to bidirectional.

For your measure, try:

WAC =
DIVIDE(
    SUM( Cost[Cost] ) * SUM( Load[Volume] ),
    CALCULATE( SUM( Load[Volume] ), ALL() )
)

Regards