cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Total percentage difference

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_NameEquity_NameInvestment_TypeQuantityPurchase_RateTotal_Cost % Allocation Cost Indivdual% Overall AllocationDifference
XXXXCASHLiquid0010000 40.00%28.6%11.43%
XXXXBEL                                     Equity50502500 10.00%11.9%-1.90%
XXXXHINDPETRO                               Equity100505000 20.00%23.8%-3.81%
XXXXITC LTD                                 Equity150507500 30.00%35.7%-5.71%
Sub Total X :     25000 100.00%100.0%0.00%
YYYYCASHLiquid0020000 25.00%28.6%-3.57%
YYYYBEL                                     Equity10010010000 12.50%11.9%0.60%
YYYYHINDPETRO                               Equity20010020000 25.00%23.8%1.19%
YYYYITC LTD                                 Equity30010030000 37.50%35.7%1.79%
Sub Total Y :     80000 100.00%100.0%0.00%
          
TABLE B         
TotalCASHLiquid0030000 28.57%  
TotalBEL                                     Equity150149.7981612500 11.90%  
TotalHINDPETRO                               Equity30010025000 23.81%  
TotalITC LTD                                 Equity450287.445237500 35.71%  
     105000 100.00%  
1 ACCEPTED SOLUTION
Community Support
Community Support

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

View solution in original post

1 REPLY 1
Community Support
Community Support

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

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors