Getting fact table data based on traversing a Flat hierarcy table

[Anonymous]

HI all,

I have put a lot of work in building a Flattend hierarchy table in my data model but now I want to use it in certain ways but I cannot figure out how to make it work. 

My data looks a bit like this

id         Name

1	Captain america
2	Falcon
3	Ant Man
4	Thor
5	Ironman
6	Vision
7	Wolverine

Avengers Expenses
ID	Expence
1	200
1	250
2	500
3	100
4	200
5	300
5	150
6	200

ID	ParentID	%	Path	depth	Level 1	Level 2	Level 3
1			1	1	Captain America
2	1	100	1|2	2	Captain America	Falcon
3	1	100	1|3	2	Captain America	Ant man
4	2	50	2|4	3	Captain America	Falcon	Thor
4	5	50	5|4	2	Ironman	Thor
5			5	1	Ironman
6	5	100	5|6	2	Ironman	vision
7			7	1	Wolverine

My data model like this:

In my dataset are alot of avenger teams and a lot more fact tables but this is de general design. 

I want to be able to traverse the hierarcy and use it to filter the fact table

For Example by a slicer on [name] from the [Avengers] table.  I want all the expenses from Avengers who are one level Above say Thor (950) or every parent above thor (including Cap 1400).

Also I would want everybody below eg, Iron Man multiplied by the % of ownership, so only 50% of the cost of Thor can be atributed to Iron Man. 

I would also like to be able to by slicing on a name get all avengers which are they are linked. The result should return all avengers exept Wolverine because he is not in any way linked to the others. 

I hope you can help me, thanks in advance!

[Anonymous]

I have included a Sample file. Maybe thats more handy

Sample file