Different Granularities: Customer and Payer v Sales

[toddpbi]

Hello there PBI community,

I want to display a target vs sales comparison in a table with a variance column. Currently, I have many sales agents that are responsible for specific customers and payers (the person who pays the bills)

I am able to get the sales targets to display correctly, however, I am having trouble with my sales.
The sales are repeating for certain customers that deal with multiple payers.

For example, sales agent A deals with Payer A, but sales agent B also deals with Payer A. When a sale is made to Payer A, it is double counting the sales for both agents since there is not a one-to-one relationship between payer and sales agent.

I have 3 source tables. The first table looks something like this. As you can see, customer A and B both belong to payer Z, and customer E and F belong to payer T. There is therefore a one-to-many relationship payer and customer.

Table 1
Targets       Customer        Payer            Sales Agent
10                    A                  Z                      Bob
15                    B                  Z                      Jim
13                    C                 Y                       Harry
14                    D                 X                      Tim
10                    E                  T                      Sam

11                    F                  T                      Tim
15                    G                 S                      James

As you can see based on this table, when a sale is made to payer Z, both Bob and Jim receive an equal allocation from the sale. The same goes for Sam and Tim, who both share the same payer T.

My other 2 tables look like this

Table 2, Table 3
Targets        Payer            Sales Agent
10                   AA                Jake

3                     BB                 Jake

4                     CC                Rob

5                     DD                Ben

6                     EE                 Luke

7                     FF                  Connor

This table is not a problem because each sales agent shares a one-to-many relationship between payer and sales agent respectively. How would I go about solving this problem with DAX/and or relationships? Currently, my model is connected by unique payers, so I am getting the correct values for Table 2 and Table 3, but not for Table 1.

Here is an image of the relationship view of my current model.
If anyone could help me out I would be so grateful. I tried creating another dimension table "DIM_EmployeeCustomer" table with the list of responsible agents for each customer and then connecting it to the sales table, but I get the following error:

I hope I explained this clear enough for you! I thought of just making two separate models for the different granularities of customer and payer, but I would love to have this integrated into a single solution.




Best,

Ben

[v-yulgu-msft]

Hi @toddpbi,

What is your desired result? What do you want to achieve based on above Table1, Table2 and Table3?

Regards,

Yuliana Gu

[toddpbi]

Anyone have any idea about this?