Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Vigneshkris
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
v-yuta-msft
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
v-yuta-msft
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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.