New column formula that matches the target value of sales person vs family product

[Elyblog2009]

Hello all,

Please I need your help relating a column that I need to add with a formula that matches the sales target based on family product.Below I have the first table that contain sales data (name) and target based on family product and in the second table I have the same sales data(name) and the family product name connections. In the second table I need to add a formula that reflect the target value (as it it in the first table) of each sales name based on family product.

Below the target per family and per sales person.

Sales Name	Mobility	Beyond Connectivity	Fixed Connectivity
Jane	164,000	381,000	395,000
Ina	40,000	98,800	90,000
Mar	60,000	108,000	135,000
Ves	32,000	87,100	85,000
Irena	32,000	87,100	85,000

I need that in the other table I have Customer Profile to add another column that matches the name and family and input the target value.

Customer profile table: Target per sales person should be autopopulated based on the above table info.

Sales Person	Family	Target per sales person.
Jane	Beyond Connectivity	98,800
Ina	Fixed Connectivity	135,000
Mar	Mobility	32,000
Ves	Mobility	32,000

Thank you!

[bhanu_gautam]

@Elyblog2009 , You can create a calculated column for this using LOOKUPVALUE function

Target per sales person =
LOOKUPVALUE(
'Sales Targets'[Mobility],
'Sales Targets'[Sales Name], 'Customer Profile'[Sales Person],
'Customer Profile'[Family], "Mobility",
LOOKUPVALUE(
'Sales Targets'[Beyond Connectivity],
'Sales Targets'[Sales Name], 'Customer Profile'[Sales Person],
'Customer Profile'[Family], "Beyond Connectivity",
LOOKUPVALUE(
'Sales Targets'[Fixed Connectivity],
'Sales Targets'[Sales Name], 'Customer Profile'[Sales Person],
'Customer Profile'[Family], "Fixed Connectivity"
)
)
)

[Elyblog2009]

Hello again,

I have tried the formula but is not working due to missing relationships .

Any suggestion from your side?

Thank you!

[Elyblog2009]

Hello bhanu_gautam,

Thank you for your response. I have tried the proposed solution, but it is being rejected with the below reason. I have tried to relationship the Family column in Customer Profile with Direct Sales target tab with one of the columns with Many to Many relationship but still isn't working. Any proposal from your side?

[Elyblog2009]

Hello,

Any suggestion please?

Thank you!