Create a DAX Measure that Calculates the Difference Between Various Portfolio Holding Weights and that of a Reference Portfolio

I would appreciate assistance in creating a DAX Measure that calculates the difference in security weights of several portfolios relative to a reference portfolio (which is one of the portfolios being evaluated and selected through a slicer.). The end-use will be able to select which portfolio they want as the reference portfolio.

I have a data (Fact) table called "Holdings" that has the following fields: Date, Portfolio Name, Security Ticker, and Portfolio Weight.

There are three lookup (DIM) tables linked to the "Holdings" table, but I believe I only need the Calendar Table for this specific scenario: Calendar Table, Security, and Portfolio Name.

If I choose "Portfolio A" in the Slicer, I would like to see the following for each security:

For Portfolio A: Portfolio A weight of that security minus Portfolio A weight of that security
For Portfolio B: Portfolio B weight of that security minus Portfolio A weight of that security
For Portfolio C: Portfolio C weight of that security minus Portfolio A weight of that security etc.

Holdings Table :

DATE    PORTFOLIO   SECURITY  WEIGHT
1/31/24 Portfolio A IBM       0.25
1/31/24 Portfolio A APPL      0.25
1/31/24 Portfolio A MSFT      0.25
1/31/24 Portfolio A C         0.25
1/31/24 Portfolio B IBM       0.15
1/31/24 Portfolio B APPL      0.35
1/31/24 Portfolio B MSFT      0.25
1/31/24 Portfolio B C         0.25
1/31/24 Portfolio C IBM       0.25
1/31/24 Portfolio C APPL      0.15
1/31/24 Portfolio C MSFT      0.35
1/31/24 Portfolio C C         0.25
1/31/24 Portfolio D IBM       -
1/31/24 Portfolio D APPL      0.25
1/31/24 Portfolio D MSFT      0.50
1/31/24 Portfolio D C         0.25
1/31/24 Portfolio E IBM       0.25
1/31/24 Portfolio E APPL      0.35
1/31/24 Portfolio E MSFT      0.15
1/31/24 Portfolio E C         0.25

Table Summarizing the Holding Weights :

PORTFOLIO   IBM     APPL    MSFT    C
Portfolio A 0.25    0.25    0.25    0.25
Portfolio B 0.15    0.35    0.25    0.25
Portfolio C 0.25    0.15    0.35    0.25
Portfolio D -       0.25    0.50    0.25
Portfolio E 0.25    0.35    0.15    0.25

What I want to display as a matrix table in Power BI :

Difference between Portfolio and Reference Portfolio :

PORTFOLIO   IBM    APPL     MSFT    C
Portfolio A   -     -         -     -
Portfolio B (0.10) 0.10       -     -
Portfolio C   -   (0.10)    0.10    -
Portfolio D (0.25)   -      0.25    -
Portfolio E   -    0.10    (0.10)   -

Additional Information:

In the original post, I changed the weight of Microsoft in Portfolio C enter code here to be 0.35 so that it foots to 100.

In the model, I have four measures:

'Security Weight = SUM(Holdings[Weight])'

`Selected Portfolio = SELECTEDVALUE(Holdings[PORTFOLIO])`

'Reference Portfolio (SELECTED) = 
CALCULATE(
[Security Weight],
FILTER(Holdings, Holdings[PORTFOLIO] = [Selected Portfolio]))'

'Weight Difference = [Security Weight] - 
[Reference Portfolio(SELECTED)]'

The Matrix table is set up as follows:

'Rows: Holdings[PORTFOLIO]'
'Columns:  Holdings[SECURITY]'
'Values:  [Weight Difference]'

When I set the matrix table up using the "Weight Difference" measure and select a reference portfolio through the Slicer, the table only returns that particular portfolio (collapsing to a single row) and returns the security weights; not the difference. For Example, if I choose Portfolio B in the Slicer to be the reference portfolio, it only shows Portfolio B and returns its actual weights, not the weight of the portfolio minus the weight of the reference portfolio.

Solution

First create a new table for all the distinct portfolio values:

Portfolios = DISTINCT(Holdings[PORTFOLIO])

This table will be used as your slicer.

Then try this measure:

Measure = 

VAR thisPortfolio = SELECTEDVALUE(Holdings[PORTFOLIO])
VAR thisSecurity = SELECTEDVALUE(Holdings[SECURITY])
VAR thisWeight = SELECTEDVALUE(Holdings[WEIGHT])

VAR selectedPortfolio = SELECTEDVALUE(Portfolios[PORTFOLIO])

VAR weightPortA = CALCULATE(
                    MINX(Holdings, [WEIGHT])
                    , FILTER(ALL(Holdings)
                        , Holdings[PORTFOLIO] = selectedPortfolio
                        && Holdings[SECURITY] = thisSecurity
                        )
    ) + 0

VAR diff = thisWeight - weightPortA

RETURN diff

Output example 1:

Output example 2: