Forum Discussion

Matt-G's avatar
Matt-G
New Member
7 years ago
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 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?



 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

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

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

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

    • Matt-G's avatar
      Matt-G
      New Member
      7 years ago

      Here you go.  

              Utilisation                                    Costs                                            Results

      UtilIdAmtVolKey VolKeyCostTypeCostUnit Results 
      1421 1GS0.2 UtilIdValue
      541 1SW1.3 1411.2
      243621 1Lease0.6 522.4
      43211 1Deprec0.1 243611.2
      872 1OnCost1.1 4325.6
      462 1IT2.3 8352.8
      65642 2GS6.7 4302.4
      32458.22 2SW23.1 628425.6
      38631 2Lease8.5 3245413.28
      7691123 2Deprec3.6 38616.8
      987232141 2OnCost7.2 76911383365
          2IT1.3   
          3GS2235.65   
          3SW123.6   
          3Lease73.345   
          3Deprec432.5   
          3OnCost6.645   
          3IT98765.45   
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

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