Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |