Hierachical rollup

[Anonymous]

hierachyI need to go from this:

Phone	UserID	ManagerID	Cost
(111) 111-1111	1		$    21.00
(111) 111-1112	2	1	$    25.00
(111) 111-1113	3	1	$    24.00
(111) 111-1114	4	1	$    27.00
(111) 111-1115	5	1	$    21.00
(111) 111-1116	6	2	$    24.00
(111) 111-1117	7	2	$    21.00
(111) 111-1118	8	2	$    25.00
(111) 111-1119	9	3	$    24.00
(111) 111-1120	10	3	$    27.00
(111) 111-1121	11	3	$    21.00
(111) 111-1122	12	4	$    24.00
(111) 111-1123	13	4	$    21.00
(111) 111-1124	14	4	$    25.00
(111) 111-1125	15	7	$    24.00
(111) 111-1126	16	7	$    27.00
(111) 111-1127	17	8	$    21.00
(111) 111-1128	18	10	$    24.00
(111) 111-1129	19	10	$    21.00
(111) 111-1130	20	10	$    25.00

to a hiearchical rollup 

L0	L0Cost	L0RollUp	L1	L1Cost	L1RollUp	L2	L2Cost	L2RollUp	L3	L3Cost	L3RollUp
1	$    21.00	$    21.00			$    97.00
			2	$    25.00				$    70.00
						6	$    24.00
						7	$    21.00				$    51.00
									15	$    24.00
									16	$    27.00
						8	$    25.00				$    21.00
									17	$    21.00
			3	$    24.00				$    72.00
						9	$    24.00
						10	$    27.00				$    70.00
									18	$    24.00
									19	$    21.00
									20	$    25.00
						11	$    21.00
			4	$    27.00				$    70.00
						12	$    24.00
						13	$    21.00
						14	$    25.00
			5	$    21.00
Total		$    21.00			$    97.00			$  212.00			$  142.00

I created a SQL view to give me the levels through outer joins but having difficult getting the cost rollup to work.

[ChrisMendoza · Accepted solution]

@Anonymous - Have you tried the pattern found at https://www.daxpatterns.com/parent-child-hierarchies/ ? You'll likely want to reference your data table keeping only [UserID] & [ManagerID] to make your 'hierarchyTable'. I mocked up a file for you however my reply message no longer shows any features like adding attachments or pictures , etc.

[ChrisMendoza · Accepted solution]

@Anonymous - Have you tried the pattern found at https://www.daxpatterns.com/parent-child-hierarchies/ ? You'll likely want to reference your data table keeping only [UserID] & [ManagerID] to make your 'hierarchyTable'. I mocked up a file for you however my reply message no longer shows any features like adding attachments or pictures , etc.

[Anonymous · Accepted solution]

Thanks I will give it a try 🙂 Looks promising!

[v-chuncz-msft]

@Anonymous,

By the way, you may share the solution here once it is solved. Your contribution is highly appreciated.

[Anonymous]

This solution worked great. Key thing is to ensure all the managers are indeed users and the top manager (CEO) does not have a manager.

Stephen