Many to Many Calculation Measure

[Matt-G]

Hey, I'm new to power bi and trying to replicate a report i built in Qlikview.

I've got two tables with a many to many relationship.

The first table is a utilisation table, and the second table is a costs table.  

They are linked by a key called VolKey.

Each record on the utilisation table has a multiplier value called Amt and will link to multiple records on the cost table, since for VolKey 1 there might be 12 resulting values on the cost table as the cost is broken down into components.

The basic formula is Cost = Amt*CostUnit giving me the cost per utilisation record, that can then be restricted or pivoted on the cost component.

Utilisation                Costs

UtilId                        VolKey

Amt                          CostType

VolKey                      CostUnit

Date

etc...

I did this using a calculated column, but that doesn't change based on CostType filters

CalColumn = CALCULATE(sum(Costs[CostUnit]))*Utilisatoin[Amt]

Can anyone help here?

[Ashish_Mathur]

Hi,

Share the 2 datasets in a format that can be pasted in an Excel file.  Also, show the expected result there.

[Matt-G]

Here you go.  

        Utilisation                                    Costs                                            Results

UtilId	Amt	VolKey		VolKey	CostType	CostUnit		Results
14	2	1		1	GS	0.2		UtilId	Value
5	4	1		1	SW	1.3		14	11.2
2436	2	1		1	Lease	0.6		5	22.4
432	1	1		1	Deprec	0.1		2436	11.2
8	7	2		1	OnCost	1.1		432	5.6
4	6	2		1	IT	2.3		8	352.8
6	564	2		2	GS	6.7		4	302.4
3245	8.2	2		2	SW	23.1		6	28425.6
386	3	1		2	Lease	8.5		3245	413.28
769	112	3		2	Deprec	3.6		386	16.8
987	23214	1		2	OnCost	7.2		769	11383365
				2	IT	1.3
				3	GS	2235.65
				3	SW	123.6
				3	Lease	73.345
				3	Deprec	432.5
				3	OnCost	6.645
				3	IT	98765.45

[Ashish_Mathur · Accepted solution]

Hi,

Do not create any relationship between the 2 tables.  In the Utilisation Table, write this calculated column formula.  Rename the column as Value

=CALCULATE(SUM(Costs[CostUnit]),FILTER(Costs,Costs[VolKey]=EARLIER(Utilisation[VolKey])))

To your visual, drag Utild from the Utilisation Table.  Write this measure

=SUMX(Utilisation,Utilisation[Amt]*Utilisation[Value])