How to calculate a ratio in a Hierarchy table

[dripet]

Hi, 

I'm working on a straight Table with Child as dimension and exp. Sum(amount) and I need to perform the below calculation, Any suggestion on how can I peek up the value of the Parent ( or of any level that I need to perform the calculation)?  Or any other suggestion on how to perform these ratios?

Code Child	CHILD	Code Parent	Amount	Ratio %  ( On Parent)	Ratio % (Child Cat)
A	x	RE	60	A/A (I need this on A not on RE	A/A
AI	x_1	A	60	AI/A	AI/A
AI_1	x_1_1	AI	60	AI_1/AI	AI_1/A
AI_1_1	x_1_1_1	AI_1	20	AI_1_1/AI_1	AI_1_1/A
AI_1_2	x_1_1_2	AI_1	40	AI_1_2/AI_1	AI_1_2/A
B	y	RE	40	B/B	B/B
BI	y_1	B	40	BI/B	BI/B
BI_1	y_1_1	BI	40	BI_1/BI	BI_1/B
BI_1_1	y_1_1_1	BI_1	25	BI_1_1/BI_1	BI_1_1/B
BI_1_2	y_1_1_2	BI_1	15	BI_1_2/BI_1	BI_1_2/B
RE	Z		100	100%

[Saravanan_Desingh]

Are you looking something like this?

H:
Hierarchy([Code Child],[Code Parent], Amount,ParentAmount)
LOAD [Code Child], 
     CHILD, 
     [Code Parent], 
     Amount
FROM
[C:\Users\sarav\Downloads\QVwork\hierqvcomm.xlsx]
(ooxml, embedded labels, table is Sheet2);

tab1:
LOAD *, (Amount/ParentAmount)*100 	As [Ratio % (On Parent)]
	  , (Amount/Amount1)*100		As [Ratio % (Child Cat)]
Resident H
;

Drop Table H;

[Saravanan_Desingh · Accepted solution]

[dripet]

Hi, 

thank you for the response, I preferred to have these ratios as expressions function in the straight table and not as new dimensions, however I can try to implement this solution in my model.

[Saravanan_Desingh]

Please accept it as the solution, if it satisfies your requirement.