Total percentage difference

[Vigneshkris]

Hi ,

I have 2 tables as shown in the screen shot below . Table B is aggregation of  table A except it doesent have Investor name. 

I need to calculate % Allocation Cost Indivdual  ,  % Overall Allocation , Difference  as shown in the screen shot below . Can you please help with formula to arrive at this.

Ex :

% Allocation Cost Indivdual  for investor XXXX  and Investment Type Cash is calculated as =   10000/25000 = 40 %

% Overall Allocation for all investor / cash is  calculated as 30000 / 105000 = 28.57 %

TABLE A
Investor_Name	Equity_Name	Investment_Type	Quantity	Purchase_Rate	Total_Cost		% Allocation Cost Indivdual	% Overall Allocation	Difference
XXXX	CASH	Liquid	0	0	10000		40.00%	28.6%	11.43%
XXXX	BEL	Equity	50	50	2500		10.00%	11.9%	-1.90%
XXXX	HINDPETRO	Equity	100	50	5000		20.00%	23.8%	-3.81%
XXXX	ITC LTD	Equity	150	50	7500		30.00%	35.7%	-5.71%
Sub Total X :					25000		100.00%	100.0%	0.00%
YYYY	CASH	Liquid	0	0	20000		25.00%	28.6%	-3.57%
YYYY	BEL	Equity	100	100	10000		12.50%	11.9%	0.60%
YYYY	HINDPETRO	Equity	200	100	20000		25.00%	23.8%	1.19%
YYYY	ITC LTD	Equity	300	100	30000		37.50%	35.7%	1.79%
Sub Total Y :					80000		100.00%	100.0%	0.00%
TABLE B
Total	CASH	Liquid	0	0	30000		28.57%
Total	BEL	Equity	150	149.79816	12500		11.90%
Total	HINDPETRO	Equity	300	100	25000		23.81%
Total	ITC LTD	Equity	450	287.4452	37500		35.71%
					105000		100.00%

[v-yuta-msft · Accepted solution]

Hi Vigneshkris,

Create a relationship between table A and table B based on columns [Investor_Name], [Equity_Name], then create a calculate columns and use DAX formulas like below:

In Table A:

% Allocation Cost Indivdual = TableA[Total_Cost] / SUM(TableA[Total_Cost]) 

% Overall Allocation =
RELATED ( TableB[% Allocation Cost Indivdual] )

Difference =
TableA[% Allocation Cost Indivdual] - TableA[% Overall Allocation]

In Table B:

% Allocation Cost Indivdual =
TableB[Total_Cost] / SUM ( TableB[Total_Cost] )

Hope it's helpful to you.

Jimmy Tao