Forum Discussion

Vigneshkris's avatar
Vigneshkris
Frequent Visitor
8 years ago
Solved

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 Allo...
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    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

v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

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