Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jo1245
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 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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

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

Anonymous
Not applicable

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
JamesFr06_0-1652874331159.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors