split values between not related tables

[paolomint]

Hi,

The sales department set some goal for each ITEM 

ITEM	GOAL
Shoes	500
Shirt	300
Tie	600

I need to split the goal for each agent based on the % of the clients they sold to last year

From my sales table by dax measures:

I calculated the total number of clients of 2020 

I calculated the number of clients for each agent in 2020

Then I calculated the % of client of any agent...

TOTAL CLIENTS 2020 = 200

CLIENTS of John 2020 = 20

CLIENTS of Alfred 2020 = 50 

% of Client of Jhon = (20/200)*100 = 10%

% of Client of Jhon = (50/200)*100 = 25%

So far no problem, 

now How can I split the goal by the % of clients of the agents?

There is no relation between goal table and agent.

This is the results I need to obtain:

AGENT	ITEM	%CLIENTS	GOAL
Jhon	Shoes	10	50
Jhon	Shirt	10	30
Jhon	Tie	10	60
Alfred	Shoes	25	125
Alfred	Shirt	25	75
Alfred	Tie	25	150

I hope everything is clear

Thank you very much

Paolo

[v-xiaotang · Accepted solution]

Hi @paolomint 

Not clear about your data model and table structure, so I create a sample, you may take steps bellow for reference.

-

1.create the GoalTable.

GoalTable = SUMMARIZECOLUMNS(ClientsTable[Agent],ItemGoalTable[ITEM])

Result:

2.create the two measures.

Measure_%CLIENTS =
VAR _total =
    CALCULATE ( SUM ( ClientsTable[CLIENTS 2020] ), ALL ( ClientsTable ) )
RETURN
    DIVIDE ( SELECTEDVALUE ( ClientsTable[CLIENTS 2020] ), _total )

Measure_GOAL =
VAR _CLIENTS =
    CALCULATE (
        [Measure_%CLIENTS],
        FILTER (
            ClientsTable,
            ClientsTable[Agent] = SELECTEDVALUE ( GoalTable[Agent] )
        )
    )
VAR _num =
    CALCULATE (
        SELECTEDVALUE ( ItemGoalTable[GOAL] ),
        FILTER (
            ItemGoalTable,
            ItemGoalTable[ITEM] = SELECTEDVALUE ( GoalTable[ITEM] )
        )
    )
RETURN
    _CLIENTS * _num

Result:

You can check the sample file I attached below.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[joshyT7]

Hi Paolo, 

If I understand correctly, you have a sales targets table, and then a table of agents and the number of clients from last year? 

If the tables aren't connected, you can use a measure to filter the table to a selected value and then calculate your goal. 

For example, FILTER ( 'TableName', 'TableName'['Item'] = "Shoes" )

You can use the item in your agent table, if you have one, to filter the target table. 

Let me know if that makes sense and I can help further if needed! 

Thanks

Josh