PowerBI Measure to calculate Sales Goals with Reports To: structure.

[scpournara]

I am trying to use PowerBI to calculate Sales Goals for all employees of a company based on a Reporting structure where most employees have Customer responsibility. One table is an HR Data table that references an employee and who they Report To. The second table is fact data that details the Employees accounts and the units that they re Budgeted to sell. The Output is to calculate an Individual Goal and a Team Goal using the Tables using Power BI. The individual Goal is easy, but the Team goal is difficult as it only sum up the accounts that are based on the Reports To. I found this difficult to write a measure. During the year, we could change the Reports To structure and the Accounts Assigned To and it would by dynamic. Can't seem to wrap my head around this one.

PowerBI.Heirarchy.Problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Inputs							Output
2	HR Data Table			Fact Data Table				Results Table
3	Employee	Reports To:		Account Name	Account Assigned	Units Budget			Individual Goal	Team Goal	Comments	Range
4	Company President			Customer #1	Rainbow Seller - Leader	5		Company President	-	1,450		=SUM(F4:F48)
5	Rainbow Seller - Leader	Company President		Customer #2	Rainbow Seller - Leader	5		Rainbow Seller - Leader	25	1,400	Red, Orange & Green Sellers = 50 + 100 + 350 + 550 + 350	=SUM(F9:F43)
6	Red Sellers - Mgr	Rainbow Seller - Leader		Customer #3	Rainbow Seller - Leader	5		Red Sellers - Mgr	50	350	Red Sellers 1 & 2 = 150 + 200	=SUM(F19:F28)
7	Orange Seller - MGR	Rainbow Seller - Leader		Customer #4	Rainbow Seller - Leader	5		Orange Seller - MGR	100	550	Orange Seller 1 & 2 = 250 + 300	=SUM(F29:F38)
8	Red Seller - 1	Red Sellers - Mgr		Customer #5	Rainbow Seller - Leader	5		Red Seller - 1	150	-	No Reports To, No Team Goal
9	Red Seller - 2	Red Sellers - Mgr		Customer #11	Red Seller - Mgr	10		Red Seller - 2	200	-	No Reports To, No Team Goal
10	Orange Seller - 1	Orange Seller - MGR		Customer #12	Red Seller - Mgr	10		Orange Seller - 1	250	-	No Reports To, No Team Goal
11	Orange Seller - 2	Orange Seller - MGR		Customer #13	Red Seller - Mgr	10		Orange Seller - 2	300	25	Tangerine seller = 25	=SUM(F44:F48)
12	Green Seller - 1	Rainbow Seller - Leader		Customer #14	Red Seller - Mgr	10		Tanerine Seller - 1	25		No Reports To, No Team Goal
13	Tanerine Seller - 1	Orange Seller - 2		Customer #15	Red Seller - Mgr	10		Green Seller - 1	350	-	No Reports To, No Team Goal
14				Customer #21	Orange Seller - MGR	20			1,450
15				Customer #22	Orange Seller - MGR	20
16				Customer #23	Orange Seller - MGR	20
17				Customer #24	Orange Seller - MGR	20
18				Customer #25	Orange Seller - MGR	20
19				Customer #31	Red Seller - 1	30
20				Customer #32	Red Seller - 1	30
21				Customer #33	Red Seller - 1	30
22				Customer #34	Red Seller - 1	30
23				Customer #35	Red Seller - 1	30
24				Customer #41	Red Seller - 2	40
25				Customer #42	Red Seller - 2	40
26				Customer #43	Red Seller - 2	40
27				Customer #44	Red Seller - 2	40
28				Customer #45	Red Seller - 2	40
29				Customer #51	Orange Seller - 1	50
30				Customer #52	Orange Seller - 1	50
31				Customer #53	Orange Seller - 1	50
32				Customer #54	Orange Seller - 1	50
33				Customer #55	Orange Seller - 1	50
34				Customer #61	Orange Seller - 2	60
35				Customer #62	Orange Seller - 2	60
36				Customer #63	Orange Seller - 2	60
37				Customer #64	Orange Seller - 2	60
38				Customer #65	Orange Seller - 2	60
39				Customer #71	Green Seller - 1	70
40				Customer #72	Green Seller - 1	70
41				Customer #73	Green Seller - 1	70
42				Customer #74	Green Seller - 1	70
43				Customer #75	Green Seller - 1	70
44				Customer #76	Tanerine Seller - 1	5
45				Customer #77	Tanerine Seller - 1	5
46				Customer #78	Tanerine Seller - 1	5
47				Customer #79	Tanerine Seller - 1	5
48				Customer #80	Tanerine Seller - 1	5
49				Total		1450
50
Sheet1

Cell Formulas
Range	Formula
J4	J4	=SUM(F4:F48)
J5	J5	=SUM(F9:F43)
J6	J6	=SUM(F19:F28)
J7	J7	=SUM(F29:F38)
J11	J11	=SUM(F44:F48)
I5	I5	=SUM(F4:F8)
I6	I6	=SUM(F9:F13)
I7	I7	=SUM(F14:F18)
I8	I8	=SUM(F19:F23)
I9	I9	=SUM(F24:F28)
I10	I10	=SUM(F29:F33)
I11	I11	=SUM(F34:F38)
I12	I12	=SUM(F44:F48)
I13	I13	=SUM(F39:F43)
I14	I14	=SUM(I5:I13)
F49	F49	=SUM(F4:F48)

 

[Matt Allington]

My guess is it is a SUMX issue (more specifically a lack of SUMX)

When to Use SUMX() vs SUM() in DAX

Learn the main insights of SUMX() vs SUM() in DAX with examples, so you can decide which formula works best to get excellent reports.?

exceleratorbi.com.au