cancel
Showing results for
Did you mean:
Helper II

split values between not related tables

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

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Frequent Visitor

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

Announcements