Total percentage difference

Question

Hi ,I have 2 tables as shown in the screen shot below . Table B is aggregation of&nbsp; table A except it doesent have Investor name.&nbsp;I need to calculate&nbsp;% Allocation Cost Indivdual&nbsp; ,&nbsp; % Overall Allocation , Difference&nbsp; as shown in the screen shot below . Can you please help with formula to arrive at this.Ex :% Allocation Cost Indivdual&nbsp; for investor XXXX&nbsp; and Investment Type Cash is calculated as =&nbsp; &nbsp;10000/25000 = 40 %% Overall Allocation for all investor / cash is&nbsp; calculated as 30000 / 105000 = 28.57 %&nbsp;TABLE A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Investor_NameEquity_NameInvestment_TypeQuantityPurchase_RateTotal_Cost&nbsp;% Allocation Cost Indivdual% Overall AllocationDifferenceXXXXCASHLiquid0010000&nbsp;40.00%28.6%11.43%XXXXBEL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity50502500&nbsp;10.00%11.9%-1.90%XXXXHINDPETRO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity100505000&nbsp;20.00%23.8%-3.81%XXXXITC LTD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity150507500&nbsp;30.00%35.7%-5.71%Sub Total X :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;25000&nbsp;100.00%100.0%0.00%YYYYCASHLiquid0020000&nbsp;25.00%28.6%-3.57%YYYYBEL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity10010010000&nbsp;12.50%11.9%0.60%YYYYHINDPETRO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity20010020000&nbsp;25.00%23.8%1.19%YYYYITC LTD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity30010030000&nbsp;37.50%35.7%1.79%Sub Total Y :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;80000&nbsp;100.00%100.0%0.00%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TABLE B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TotalCASHLiquid0030000&nbsp;28.57%&nbsp;&nbsp;TotalBEL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity150149.7981612500&nbsp;11.90%&nbsp;&nbsp;TotalHINDPETRO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity30010025000&nbsp;23.81%&nbsp;&nbsp;TotalITC LTD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Equity450287.445237500&nbsp;35.71%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;105000&nbsp;100.00%&nbsp;&nbsp;

v-yuta-msft · Accepted Answer

Hi&nbsp;Vigneshkris,
&nbsp;
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:
&nbsp;
In Table A:
% Allocation Cost Indivdual = TableA[Total_Cost] / SUM(TableA[Total_Cost])&nbsp;% Overall Allocation =RELATED ( TableB[% Allocation Cost Indivdual] )Difference =TableA[% Allocation Cost Indivdual] - TableA[% Overall Allocation]
&nbsp;
In Table B:
% Allocation Cost Indivdual =
TableB[Total_Cost] / SUM ( TableB[Total_Cost] )

Hope it's helpful to you.
&nbsp;
Jimmy Tao

Forum Discussion

Total percentage difference

1 Reply

Related content

Percentage difference on total

Percentage Difference

Different total

Percentage Total is not accurate

Percentage of parent total in matrix visual

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%