Getting a value based on relationship between two dimensions

[zagzebski]

I need to get "Margin" based on the relationship between Salesperson and Customer Name. If the sales person has revenue from a client then I need to associate the margin with that salesperson(s) in an expression - even though the salesperson dimension is not in the margin table (not part of the margin records).

DATA:
Salesperson	Customer Name	Revenue	Margin
Amanda	ZAP Marketing	280.26	0.00
Brandon	ZAP Marketing	101.42	0.00
	ZAP Marketing		3.00
Judy	7UPS	9,877.75	0.00
	7UPS		15.00
Lora	AB Co	1,113.15	0.00
Stacey	AB Co	194.47	0.00
	AB Co		37.50
CHART:
Salesperson	Customer Name	Margin
Amanda	ZAP Marketing	3.00
Brandon	ZAP Marketing	3.00
Judy	7UPS	15.00
Lora	AB Co	37.50
Stacey	AB Co	37.50

[#2 · Accepted solution]

Hi,

Can you check if this is what you're looking for

[sunny_talwar]

This can be another way to do this without needing to create another table:

If(Len(Trim(Salesperson)) > 0, Sum(TOTAL <[Customer Name]> Margin))

[zagzebski]

Thanks! Looks like the best place to do it is in script. Not possible on front end?

[sunny_talwar]

Did you look at the solution I offered above

[#6]

Try Sunny solution for the front end solution

[zagzebski]

Thanks Sunny!