topic Re: New column formula that matches the target value of sales person vs family product in DAX Commands and Tips

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

Elyblog2009 — Tue, 30 Jul 2024 07:33:31 GMT

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!

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

bhanu_gautam — Tue, 30 Jul 2024 07:37:18 GMT

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"
)
)
)

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

Elyblog2009 — Tue, 30 Jul 2024 14:38:50 GMT

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?

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

Elyblog2009 — Thu, 01 Aug 2024 07:33:03 GMT

Hello,

Any suggestion please?

Thank you!

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

Elyblog2009 — Fri, 02 Aug 2024 09:15:09 GMT

Hello again,

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

Any suggestion from your side?

Thank you!