cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 To Generic Material 25000652 4055 25000778 3 25000342 1 25000742 3 25000421 1

 Table : tEE Sold to Pary Material Month 25000342 1 25.00 25000742 3 32.50 25000652 4055 44.50 25000421 1 30.00 25000778 3 15.00

 Table IAS Sold to Pary Eqpt Month 25000342 1 42.00 25000742 3 15.00 25000652 4055 33.40 25000421 1 15.35 25000778 3 55.00

Result of what new column should look like:

 Table External Revenue Sold To Generic Material Inbound FY 25000652 4055 77.90 25000778 3 70.00 25000342 1 67.00 25000742 3 47.50 25000421 1 45.35

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

1 ACCEPTED SOLUTION
Solution Supplier

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

4 REPLIES 4
Solution Supplier

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

Frequent Visitor

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

Solution Supplier

HI

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

Frequent Visitor

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

Announcements