How can I show aggregate dimension weights across multiple grouping levels.

Question

Sorry for how long/complex this is. I've been trying to solve this for months and am hitting my head against a wall.

Overview

To make sense of this, I have to explain the whole story.

• I have a bunch of applications
• Each application has a manager
• Each application has a target score
• Each application is scored on 3 dimensions
• The aggregate of those 3 dimensions is the application's current score
• Each application's current score should be under the target score

(In my real world data I have 200+ dimensions. I am using 3 here for the sake of simplicity.)

Simple Example

Knowing the above, I can show the weight of each individual application + dimension combination. By weight I mean how much weight a specific dimension carries in bringing the current score to under the target score.

For example:

• Application's target score is 10
• dimension 1 score = 1
• dimension 2 score = 5
• dimension 3 score = 15
• Application's current score is = 1 + 5 + 15 = 21
• The application needs to lower it's current score by 11: current score - target score => 21 - 10
  • I'll call this the amount to drop by score

Ergo:

• dimension 1 has a weight of 0.09: dimension 1 score / amount to drop by score => 1 / 0.09
• dimension 2 has a weight of 0.45: dimension 2 score / amount to drop by score => 5 / 0.45
• dimension 3 has a weight of 1.36: dimension 3 score / amount to drop by score => 15 / 1.36

So What?

This data tells me which dimension carries the most weight for fixing. In the above example, it's obvious fixing dimension 3 is the best option cause it has the highest weight.

Problem

Using the above method, I can show the weight for each application + dimension combination. Meaning, the weight for 1 dimension for 1 application.

What I am trying to figure out how is how to calculate the weight by aggregating across managers?

For example:

• What is the weight of dimension 1 for manager?
• What is the weight of dimension 2 for manager?
• What is the weight of dimension 3 for manager?

Complex Example

Say I have data like this:

app ID	app name	l1 manager	target score	dimension 1	dimension 2	dimension 3	current score	amount to drop by score
1	one	a	49	35	9	15	59	10
2	two	a	32	50	22	30	102	70
3	three	a	46	45	18	50	113	67
4	four	a	43	19	41	13	73	30
5	five	b	38	1	38	16	55	17
6	six	b	37	48	38	24	110	73
7	seven	b	36	5	3	36	44	8
8	eight	b	45	16	44	41	101	56
9	nine	c	44	6	50	43	99	55
10	ten	c	37	13	29	25	67	30

I can restructure the data so each row is 1 application and 1 dimension to show it's weight:

app ID	app name	l1 manager	target score	amount to drop by score	dimension name	dimension value	weight
1	one	a	49	10	1	35	3.50
1	one	a	49	10	2	9	0.90
1	one	a	49	10	3	15	1.50
2	two	a	32	70	1	50	0.71
2	two	a	32	70	2	22	0.31
2	two	a	32	70	3	30	0.43
3	three	a	46	67	1	45	0.67
3	three	a	46	67	2	18	0.27
3	three	a	46	67	3	50	0.75
4	four	a	43	30	1	19	0.63
4	four	a	43	30	2	41	1.37
4	four	a	43	30	3	13	0.43
5	five	b	38	17	1	1	0.06
5	five	b	38	17	2	38	2.24
5	five	b	38	17	3	16	0.94
6	six	b	37	73	1	48	0.66
6	six	b	37	73	2	38	0.52
6	six	b	37	73	3	24	0.33
7	seven	b	36	8	1	5	0.63
7	seven	b	36	8	2	3	0.38
7	seven	b	36	8	3	36	4.50
8	eight	b	45	56	1	16	0.29
8	eight	b	45	56	2	44	0.79
8	eight	b	45	56	3	41	0.73
9	nine	c	44	55	1	6	0.11
9	nine	c	44	55	2	50	0.91
9	nine	c	44	55	3	43	0.78
10	ten	c	37	30	1	13	0.43
10	ten	c	37	30	2	29	0.97
10	ten	c	37	30	3	25	0.83

Problem Details

But now, how can I create an aggregate table that shows weight by l1 manager?

l1 manager	dimension name	weight
a	1	?
a	2	?
a	3	?
b	1	?
b	2	?
b	3	?
c	1	?
c	2	?
c	3	?

I can sum the dimension value to get the total value for that dimension for that l1 manager.

But what do I divide it by? I can't divide by summing target score or current score cause that'll essentially be multiplying the target score or current score.

I'm stuck/lost.