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])
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])