Forum Discussion

leamsi-alvarado's avatar
leamsi-alvarado
Regular Visitor
2 years ago
Solved

Multiply Measure and column, before sum

Hello, i need to multiply a measure with a column, the problem is that it sums first, and then it does de mutliplication, this is the dax code im currently using: 

Variance per volume= 'PBI_ASN_Volume_Budget'[Variance Budget] * sumx('PBI_ASN_Fixed_Cost', 'PBI_ASN_Fixed_Cost'[FixedCost])

here is an example of the problem:
variance budget is for row  with ID 1 = 50 and for row with ID 2 = 100, the i have in fixed cost the fixed cost for the ID 1 = 0.52 and for the ID 2 = 0.75, what ia want is  (50 * 0.52) + (100*0.75) =  101, and what the code is doing right now is (50+100) * (0.52 + 0.75) = 190.5

Any ideas, thank you!  
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I would try it this way, 

     

    Variance per volume = SUMX(
    'PBI_ASN_Fixed_Cost',
    'PBI_ASN_Fixed_Cost'[FixedCost] * 'PBI_ASN_Volume_Budget'[Variance Budget]
)

     

    This could also help 

    Variance per volume = SUMX(
    'PBI_ASN_Fixed_Cost',
    'PBI_ASN_Fixed_Cost'[FixedCost] * RELATED('PBI_ASN_Volume_Budget'[Variance Budget])
)

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    leamsi-alvarado the solution Anonymous  provided will work assuming there is a relationship between the tables, and also an important part is how these tables are related. Is it many to many or one to many relationship and which side is the one side?

     

    Just having DAX will not do anything until there is a clear understanding of the relationship. My 2 cents.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I would try it this way, 

     

    Variance per volume = SUMX(
    'PBI_ASN_Fixed_Cost',
    'PBI_ASN_Fixed_Cost'[FixedCost] * 'PBI_ASN_Volume_Budget'[Variance Budget]
)

     

    This could also help 

    Variance per volume = SUMX(
    'PBI_ASN_Fixed_Cost',
    'PBI_ASN_Fixed_Cost'[FixedCost] * RELATED('PBI_ASN_Volume_Budget'[Variance Budget])
)
    • leamsi-alvarado's avatar
      leamsi-alvarado
      Regular Visitor
      2 years ago

      This works perfectly, i needed to make a better relationship but the dax code worked, thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Assuming the data is laid out like this: 

    IDFixedID2Fixed2
    50.52100.75



    I used these measures: 

    Multiply ID 1 and Fixed 1 = SUMX('Table', 'Table'[Fixed] * 'Table'[ID])
    Multiply ID 2 and Fixed 2 = SUMX('Table', 'Table'[Fixed2] * 'Table'[ID2])
    Add Values = SUMX('Table', [Multiply ID 1 and Fixed 1] + [Multiply ID 2 and Fixed 2])



  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Anonymous if that is the structure of the data which is very less likely, I will unpivot and then work on it, this is not the right shape of the data for the Power BI.