The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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