Creating a measure to sum different columns based on row value

[lmondavi]

Hi, I have a Funnel table with 3 different $ value columns (SM_M1, SM_M2, and SM_M3). I have an employee table which is unrelated to the Funnel table. I want to create one measure that will sum one of the Funnel columns for each person, but which column to sum depends on criteria within the People table. I tried the below formula, however there are many records in the people table that have the same criteria, so this does not work. I think I am missing something simple. Thank you for your help!

HWProjection$ = if(VALUES(People[Function])="Segment Manager" && VALUES(People[Segment Alias])="EA",sum(Funnel[SM_M1]),if (VALUES(People[Function])="Segment Manager",sum(Funnel[SM_M2]),sum(Funnel[SM_M3]))) 

[Ashish_Mathur]

Hi,

Share your data and show the expected result.

[lmondavi]

Thanks, here is the data with expected result

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[lmondavi]

Thank you so much!

[Ashish_Mathur]

You are welcome.