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 ...
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.
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% dont worry see attached.
you need to model your data differently look at how i have extracted the agent and date.
That worked perfectly! Thank you so much!
i am a bit confused as to how you working out your % please also supply the rule.