Need to calculate commissions for recruiters -- but both recruiters in same line

[carterbgardner]

Hi there, 

I have a fact table of a list of transactions that contains total gross profit by customer. However, for each customer there is a provider recruiter and a client recruiter (both are columns in the fact table). The gross profit is split 50/50 between the recruiters to calculate the eligible commissions amount. I am having trouble figuring out the best way to easily assign gross profit because I can only link my clean Employee Name (dim) to one of the columns (currently provider recruiter). Obviously, that's only achieving the intended result for the provider recruiters since that's how the two tables are linked. Below is a screenshot of an example (for the record data shown below is "Dummy data").

I want the output to show the total gross profit eligible for commissions, without respect to the recruiter type. Is there some sort of double bridge key that I could build to achieve this? Or a better all around approach that I'm not considering?

Thanks in advance for your help. Please let me know if there's additional specific information that would be helpful. 

[v-denglli-msft]

Hi @carterbgardner ，

May I ask if you have gotten this issue resolved?

 If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .

Thank you very much for your kind cooperation!

Best Regards,
Dengliang Li

[ToddChitt · Accepted solution]

Here is one possible approach: Investigate the DAX statements of UNION and SELECTEDCOLUMNS. Create a new DAX table that is based on the fact table, but select only one of the email address columns and a literal value to indicate it, also multiply the Gross Profit number by 50%. Wrap that in a UNION statement and union it to a second SELECTEDCOLUMNS statement that grabs the other email address and associated literal value.

The input might look like this:

Customer ID,   Gross Profit,   Provider Recruiter,  Client Recruiter

123,                  $200,             Foo@bar.com  ,      Bar@Foo.com

Result might look like this:

Customer Id,  Gross Profit,  Type,          Email

123,                $100,             Provider,   Foo@Bar.com 

123,                 $100,            Client,      Bar@Foo.com

Now you can sum up the Gross Profit for any one Email, and analyze it by Type (Client Recruiter or Provider Recruiter)

Note that this mght also be accomplished in Power Query but may not be as performant.

Hope that helps.