Forum Discussion

Jo1245's avatar
Jo1245
Frequent Visitor
4 years ago
Solved

Sumifs in Calculated Column

Good day

I am wanting to sum the the columns from two table into a new column in a third table. I have to make sure that it only sums the data matching in both tables from two other columns.

Therefore:

if 'tEE'[Sold to Party] is equal to 'External Revenue'[Sold to] , if 'tEE[Material] is equal to 'External Revenue'[Generic Material] and if 'IAS'[Sold to Party] is equal to 'Exernal Revenue'[Sold to], if 'IAS'[Eqpt] is equal to 'External Revenue'[Generic Material] then sum the 'tEE[month]+'IAS[month] in a new column in the External Revenue table.

 

Table : External Revenue
Sold ToGeneric Material
250006524055
250007783
250003421
250007423
250004211

 

Table : tEE  
Sold to ParyMaterialMonth
25000342125.00
25000742332.50
25000652405544.50
25000421130.00
25000778315.00

 

Table IAS  
Sold to ParyEqptMonth
25000342142.00
25000742315.00
25000652405533.40
25000421115.35
25000778355.00

 

Result of what new column should look like:

Table External Revenue 
Sold ToGeneric MaterialInbound FY
25000652405577.90
25000778370.00
25000342167.00
25000742347.50
25000421145.35

 

I hope that makes sense. Thank you in advance for your help.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    o this is variable to store the fields values. After you can use it as criteria in a measure

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    o this is variable to store the fields values. After you can use it as criteria in a measure

    • Jo1245's avatar
      Jo1245
      Frequent Visitor
      4 years ago

      Please could you advise how you calculate the fields value for the variable if you dont mind. thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI

     

    Please find the code 

    Total =
    var matos='Final IAS TEE'[Material]
    var STP='Final IAS TEE'[Sold to Pary]
    var amtIAS=LOOKUPVALUE(IAS[Month],'Final IAS TEE'[Material],matos,IAS[Sold to Pary],STP)
    var amtTEE=LOOKUPVALUE(TEE[Month],'Final IAS TEE'[Material],matos,IAS[Sold to Pary],STP)
    var result=amtIAS+amtTEE
    return
    result

     

    • Jo1245's avatar
      Jo1245
      Frequent Visitor
      4 years ago

      Thank you for your response. 
      I am very very new to power bi and so have a few questions if you dont mind.

      have new tables been created for these two steps:

      var matos='Final IAS TEE'[Material]
      var STP='Final IAS TEE'[Sold to Pary]
      thank you