Forum Discussion
Divide single row values by a count
I have two tables - one contains an agent's weekly production goals & the other table contains the records of the agents actual production. I want to create a matrix visual showing each agent's % of goal each week (production count / goal). I've only been using Power BI a short while & have had no luck trying to find an answer. Is this possible? Below are two example tables and the matrix I'm trying to Any help would be greatly appreciated.
Regards,
Ernie
| Table 1 | Table 2 | % of Goal Matrix | ||||||||
| Week of | Agent | Production Goal | Production Date | Agent | Product Serial # | Agent | 7/31/2022 | 8/7/2022 | ||
| 7/31/2022 | John | 5 | 7/31/2022 | John | 12 | John | 60% | 100% | ||
| 7/31/2022 | Bill | 4 | 7/31/2022 | Bill | 32 | Bill | 125% | 67% | ||
| 7/31/2022 | Suzy | 3 | 7/31/2022 | Suzy | 11 | Suzy | 100% | 33% | ||
| 7/31/2022 | Jill | 2 | 8/1/2022 | John | 45 | Jill | 50% | 40% | ||
| 8/7/2022 | John | 2 | 8/2/2022 | Bill | 87 | |||||
| 8/7/2022 | Bill | 3 | 8/3/2022 | John | 78 | |||||
| 8/7/2022 | Suzy | 3 | 8/3/2022 | Jill | 39 | |||||
| 8/7/2022 | Jill | 5 | 8/3/2022 | Suzy | 64 | |||||
| 8/3/2022 | Bill | 99 | ||||||||
| 8/3/2022 | Bill | 22 | ||||||||
| 8/3/2022 | Suzy | 74 | ||||||||
| 8/4/2022 | John | 98 | ||||||||
| 8/4/2022 | Bill | 33 | ||||||||
| 8/7/2022 | Suzy | 19 | ||||||||
| 8/7/2022 | Jill | 26 | ||||||||
| 8/8/2022 | Bill | 93 | ||||||||
| 8/9/2022 | Bill | 10 | ||||||||
| 8/10/2022 | John | 13 | ||||||||
| 8/10/2022 | John | 69 | ||||||||
| 8/11/2022 | Jill | 73 |
Maybe this is better...
Table 1
Week of Agent Production Goal 7/31/2022 John 5 7/31/2022 Bill 4 7/31/2022 Suzy 3 7/31/2022 Jill 2 8/7/2022 John 2 8/7/2022 Bill 3 8/7/2022 Suzy 3 8/7/2022 Jill 5 Table 2
Production Date Agent Product Serial # 7/31/2022 John 12 7/31/2022 Bill 32 7/31/2022 Suzy 11 8/1/2022 John 45 8/2/2022 Bill 87 8/3/2022 John 78 8/3/2022 Jill 39 8/3/2022 Suzy 64 8/3/2022 Bill 99 8/3/2022 Bill 22 8/3/2022 Suzy 74 8/4/2022 John 98 8/4/2022 Bill 33 8/7/2022 Suzy 19 8/7/2022 Jill 26 8/8/2022 Bill 93 8/9/2022 Bill 10 8/10/2022 John 13 8/10/2022 John 69 8/11/2022 Jill 73 Desired Matrix
Agent 7/31/2022 8/7/2022 John 60% 100% Bill 125% 67% Suzy 100% 33% Jill 50% 40% dont worry see attached.
you need to model your data differently look at how i have extracted the agent and date.
6 Replies
can you separate out the tables properly in text, in 3 separate chunks.
Sorry about that. Does this work? Thank you.
Table 1 Week of Agent Production Goal 7/31/2022 John 5 7/31/2022 Bill 4 7/31/2022 Suzy 3 7/31/2022 Jill 2 8/7/2022 John 2 8/7/2022 Bill 3 8/7/2022 Suzy 3 8/7/2022 Jill 5 Table 2 Production Date Agent Product Serial # 7/31/2022 John 12 7/31/2022 Bill 32 7/31/2022 Suzy 11 8/1/2022 John 45 8/2/2022 Bill 87 8/3/2022 John 78 8/3/2022 Jill 39 8/3/2022 Suzy 64 8/3/2022 Bill 99 8/3/2022 Bill 22 8/3/2022 Suzy 74 8/4/2022 John 98 8/4/2022 Bill 33 8/7/2022 Suzy 19 8/7/2022 Jill 26 8/8/2022 Bill 93 8/9/2022 Bill 10 8/10/2022 John 13 8/10/2022 John 69 8/11/2022 Jill 73 % of Goal Matrix (Desired Output) Agent 7/31/2022 8/7/2022 John 60% 100% Bill 125% 67% Suzy 100% 33% Jill 50% 40% Maybe this is better...
Table 1
Week of Agent Production Goal 7/31/2022 John 5 7/31/2022 Bill 4 7/31/2022 Suzy 3 7/31/2022 Jill 2 8/7/2022 John 2 8/7/2022 Bill 3 8/7/2022 Suzy 3 8/7/2022 Jill 5 Table 2
Production Date Agent Product Serial # 7/31/2022 John 12 7/31/2022 Bill 32 7/31/2022 Suzy 11 8/1/2022 John 45 8/2/2022 Bill 87 8/3/2022 John 78 8/3/2022 Jill 39 8/3/2022 Suzy 64 8/3/2022 Bill 99 8/3/2022 Bill 22 8/3/2022 Suzy 74 8/4/2022 John 98 8/4/2022 Bill 33 8/7/2022 Suzy 19 8/7/2022 Jill 26 8/8/2022 Bill 93 8/9/2022 Bill 10 8/10/2022 John 13 8/10/2022 John 69 8/11/2022 Jill 73 Desired Matrix
Agent 7/31/2022 8/7/2022 John 60% 100% Bill 125% 67% Suzy 100% 33% Jill 50% 40%