Forum Discussion
Solved
Many to Many Calculation Measure
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 ...
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])
Hi,
Share the 2 datasets in a format that can be pasted in an Excel file. Also, show the expected result there.
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 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])
