cancel
Showing results 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

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_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%
1 ACCEPTED SOLUTION
Community Support

Hi

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] )
```

Jimmy Tao

Community Support

Hi

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] )
```

Jimmy Tao

Announcements

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 Monthly Update - June 2024

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

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.

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors