Divide single row values by a count

[EBurkett]

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

[EBurkett · Accepted solution]

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%

[vanessafvg]

can you separate out the tables properly in text, in 3 separate chunks.

[EBurkett]

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%

[vanessafvg]

i am a bit confused as to how you working out your % please also supply the rule.

[EBurkett · Accepted solution]

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%

[vanessafvg · Accepted solution]

dont worry see attached.

you need to model your data differently look at how i have extracted the agent and date.

[EBurkett]

That worked perfectly! Thank you so much!