Totaling a Hierarchy Split Across Multiple Data Sets

Question

I am reporting on hierarchical data. The hierarchy has five levels.&nbsp; I have five data files, one for each level of the hierarchy.&nbsp; Each line of data has a parent id which connects that line of data to the parent item which is one level higher in the hierarchy.&nbsp; Each line of data also has a point value which is the sum of its child point values.&nbsp; I'm trying to find the point value of the items at the top level of the hierarchy by summing the total of all child (and all grandchild) items.&nbsp; Something like this:&nbsp;&nbsp;&nbsp;Level 1 Item&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;24&nbsp;Level 2 Item&nbsp;&nbsp;&nbsp;&nbsp;12&nbsp;&nbsp;Level 3 Item&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;Level 3 Item&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;Level 2 Item&nbsp;&nbsp;&nbsp;&nbsp;12&nbsp;&nbsp;Level 3 Item&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;Level 3 Item&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;&nbsp;&nbsp;Level 4 Item&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Level 5 Item1&nbsp;What is the best way to accomplish this?&nbsp;&nbsp;

v-frfei-msft · Accepted Answer

Hi &nbsp;Anonymous ,
&nbsp;
You can use matrix visual to work on it.

Level 1 Item					24
	Level 2 Item				12
		Level 3 Item			6
			Level 4 Item		1
				Level 5 Item	1
			Level 4 Item		1
				Level 5 Item	1
			Level 4 Item		1
				Level 5 Item	1
		Level 3 Item			6
			Level 4 Item		1
				Level 5 Item	1
			Level 4 Item		1
				Level 5 Item	1
			Level 4 Item		1
				Level 5 Item	1
	Level 2 Item				12
		Level 3 Item			6
			Level 4 Item		1
				Level 5 Item	1
			Level 4 Item		1
				Level 5 Item	1
			Level 4 Item		1
				Level 5 Item	1
		Level 3 Item			6
			Level 4 Item		1
				Level 5 Item	1
			Level 4 Item		1
				Level 5 Item	1
			Level 4 Item		1
				Level 5 Item	1

Forum Discussion

Totaling a Hierarchy Split Across Multiple Data Sets

2 Replies

Related content

Date hierarchy

Date Hierarchy

Total of multiple measures

Show total with Hierarchy RLS

Multiple level Hierarchy/Drilldown