calculate the value of a lookup column

(0) Share

Report

Posted on by Community Power Pla...

I have a list in SharePoint Online that has 4 Choice Columns comprised of 4 options each for the user to select. I also have a Lookup column for each Choice Column that returns a numberical value based on their selection from another list on the same subsite. I need a Calculated column that will total the 4 numerical values in my Lookup columns but Calculated columns do not support Lookups.

I have read that a workaround to this is to build a flow that copies the values from the Lookup column into a Numerical column and then use the Numerical columns as the source for the Calulated columns but I cannot get the flow figured out. Any assistance with building a flow to do this (or an alternative solution) would be greatly appreciated.

All responses (1)

Answers (1)

Verified answer

v-xida-msft on at

Like (0)

Report
Re: calculate the value of a lookup column

Hi @Anonymous,

Could you please share a bit more about your SharePoint list?

I have created a SharePoint list on my side and the data structure of it as below:

Note: Choice1, Choice2, Choice3 and Choice4 are all Choice type column with 4 options. Lookup1, Lookup2, Lookup3 and Lookup4 are all Lookup type column. Number1, Number2, Number3 and Number4 are all Number type column. Calculated1 is a Calculated type column, the calculated expression of Calculated1 column as below:

I have made a test on my side and please take a try with the following workaround:

Add a "When an item is created" trigger.

Add a "Update item" action, Number1 field set to following formula:

triggerBody()?['Lookup1']?['Value']

Number2 field set to following formula:

triggerBody()?['Lookup2']?['Value']

Number3 field set to following formula:

triggerBody()?['Lookup3']?['Value']

Number4 field set to following formula:

triggerBody()?['Lookup4']?['Value']

Image reference:

The flow works successfully as below:

Best regards,

Kris