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

[technolog]

Why the table visual breaks
Agreement is a dimension. Load and Cost are both many side tables at different grains. When you drop Load and Cost Group into the same table, Power BI would need to materialize an N by M join inside each Agreement. That join does not exist in the model, so the visual throws the cannot determine the relationship error. The diagram on page two shows exactly this shape Agreement in the middle with separate one to many links to Load and to Cost.

How to show rows like Load by Cost Group
Either do not put both child tables in the same visual and drive everything with measures, or create a calculated table that explicitly builds the product per Agreement. Example for a calc table

LOAD COST
VAR vLoad =
SUMMARIZE( Load, Agreement[Agreement ID], Load[Load], "Volume", SUM( Load[Volume] ) )
VAR vCost =
SUMMARIZE( Cost, Agreement[Agreement ID], Cost[Cost Group], "Cost", MAX( Cost[Cost] ) )
RETURN
NATURALINNERJOIN( vLoad, vCost )

Use this table for row level visuals only. Keep the relationships from Agreement to both original facts single direction. This avoids ambiguous paths and keeps calculations predictable.

Weighted cost measures that work with the original two fact tables
The safest pattern is to aggregate each table to the Agreement grain, join them virtually inside a measure, and then compute totals and averages.

Total Extended Cost
VAR VolByAgreement =
SUMMARIZE( Load, Agreement[Agreement ID], "Vol", SUM( Load[Volume] ) )
VAR CostByAgreementGroup =
SUMMARIZE( Cost, Agreement[Agreement ID], Cost[Cost Group], "Cost", MAX( Cost[Cost] ) )
VAR J = NATURALINNERJOIN( CostByAgreementGroup, VolByAgreement )
RETURN
SUMX( J, [Cost] * [Vol] )

Weighted Average composite cost across all groups
This is the average of the full cost per unit when the group components are additive.

WAC total
DIVIDE( [Total Extended Cost], SUM( Load[Volume] ) )

Weighted Average by a selected Cost Group
Denominator uses only the Agreements where that group exists. This reproduces the per group numbers shown in the screenshots page three and page four.

WAC by group
VAR VolByAgreement =
SUMMARIZE( Load, Agreement[Agreement ID], "Vol", SUM( Load[Volume] ) )
VAR ThisGroupCosts =
SUMMARIZE(
FILTER( Cost, Cost[Cost Group] = SELECTEDVALUE( Cost[Cost Group] ) ),
Agreement[Agreement ID],
"Cost", MAX( Cost[Cost] )
)
VAR J = NATURALINNERJOIN( ThisGroupCosts, VolByAgreement )
RETURN
DIVIDE( SUMX( J, [Vol] * [Cost] ), SUMX( J, [Vol] ) )

If you also want the sum of all group level weighted averages as a single figure

WAC sum of groups
SUMX( VALUES( Cost[Cost Group] ), [WAC by group] )

About bi directional filters
Turning the Agreement to Cost relationship to bidirectional will let filters flow between the two fact tables and can make a simple table work, and a short answer on the forum suggests exactly that together with a compact measure

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