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

A new Data Days event is coming soon! This time we’re going bigger than ever. Fabric, Power BI, SQL, AI and more. Don't miss out.

Reply
mrothschild
Continued Contributor
Continued Contributor

Subtract a SELECTEDVALUE row from a Matrix from all other Matrix Rows

PBIX file is here: https://drive.google.com/file/d/1YJ7I8PYqqEiap-mPjvprMlsLh9fv5Jkb/view?usp=sharing

 

In that file are three Matrices: 

  • Cumulative Probability of Default - Selection
  • Cumulative Probability of Default - All Ratings
  • Cumulative Probability of Default - All Ratings minus Selection

The interaction  (via /Format/Edit Interactions) has been turned off for the Matrix, ". . ..All Ratings".  

 

My desired output is to subtract the figures in the ". . . Selection" from the figures in the ". . . All Ratings" so that the result is as follows:

 

Row Labels1224364860728496108120132144156168180
AAA(0.15%)(0.34%)(0.46%)(0.69%)(0.93%)(1.17%)(1.43%)(1.65%)(1.92%)(2.18%)(2.50%)(2.74%)(2.94%)(2.98%)(3.13%)
AA+(0.15%)(0.32%)(0.54%)(0.83%)(1.12%)(1.42%)(1.69%)(1.93%)(2.20%)(2.46%)(2.74%)(2.96%)(3.13%)(3.16%)(3.31%)
AA(0.13%)(0.34%)(0.51%)(0.72%)(0.92%)(1.15%)(1.35%)(1.54%)(1.78%)(2.01%)(2.27%)(2.49%)(2.62%)(2.66%)(2.81%)
AA-(0.12%)(0.29%)(0.43%)(0.70%)(0.96%)(1.21%)(1.47%)(1.72%)(1.99%)(2.26%)(2.54%)(2.76%)(2.97%)(3.02%)(3.19%)
A+(0.10%)(0.28%)(0.40%)(0.62%)(0.86%)(1.12%)(1.34%)(1.53%)(1.73%)(1.92%)(2.14%)(2.28%)(2.36%)(2.28%)(2.35%)
A(0.10%)(0.23%)(0.38%)(0.61%)(0.83%)(1.01%)(1.16%)(1.30%)(1.45%)(1.56%)(1.74%)(1.89%)(2.00%)(2.03%)(2.08%)
A-(0.09%)(0.21%)(0.34%)(0.57%)(0.76%)(0.96%)(1.07%)(1.21%)(1.41%)(1.61%)(1.85%)(1.99%)(2.10%)(2.08%)(2.19%)
BBB+(0.06%)(0.11%)(0.12%)(0.26%)(0.37%)(0.47%)(0.59%)(0.68%)(0.74%)(0.81%)(0.92%)(1.03%)(1.08%)(0.95%)(0.91%)
BBB0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
BBB-0.09%0.32%0.68%1.00%1.36%1.62%1.84%2.04%2.13%2.16%2.21%2.26%2.33%2.69%2.83%
BB+0.17%0.60%1.17%1.62%2.08%2.52%2.88%3.08%3.36%3.64%3.71%3.93%4.20%4.45%4.79%
BB0.33%1.15%2.37%3.41%4.49%5.26%5.98%6.57%7.11%7.55%8.03%8.37%8.62%8.86%9.05%
BB-0.81%2.55%4.42%6.22%7.76%9.21%10.40%11.54%12.36%13.04%13.46%13.97%14.49%15.12%15.59%
B+1.83%5.05%8.23%10.80%12.75%14.18%15.49%16.62%17.61%18.49%19.19%19.65%20.20%20.83%21.32%
B2.98%6.98%10.52%13.26%15.42%17.35%18.68%19.63%20.51%21.38%21.80%22.29%22.65%23.07%23.41%
B-6.37%13.32%18.69%22.23%24.70%26.45%27.69%28.62%29.16%29.57%30.39%30.83%31.17%31.64%31.85%
CCC28.15%37.96%42.83%45.43%47.31%47.99%48.81%49.25%49.60%49.88%49.99%50.19%50.51%50.87%50.73%

 

Screenshots of the above-referenced Matrices with the one at the bottom not working as intended:

mrothschild_0-1683058012236.png

 

I suspect I need to "clear" filter selections using ALL or ALLSELECTED, but I'm not familiar with the proper coding protocol for those.

 

THanks in advance!

 

 

1 ACCEPTED SOLUTION

@ELofstrom3C 

 

Thanks.  Your output looks like it resulted in the same, but started me down the path I needed.  Disconnected slicer was the key.  Here's the code that worked for my solution:

 

Probability of Default Delta = 

VAR _SelectedRows = 
    FILTER(
        ALL('_Loss Given Default'),
        '_Loss Given Default'[S&P Rating] = SELECTEDVALUE('S&P Ratings'[S&P Rating])
    )

VAR _ReferenceRatingProbDefault = 
    SUMX(
        ADDCOLUMNS(
            VALUES('_Loss Given Default'[Month]),
            "Probability of Default", CALCULATE(
                SUM('_Loss Given Default'[Probability of Default]),
                _SelectedRows,
                '_Loss Given Default'[Month] = EARLIER('_Loss Given Default'[Month])
            )
        ),
        [Probability of Default]
    )

VAR _UnreferencedProbDefault = 
    SUMX(
        GROUPBY('_Loss Given Default','_Loss Given Default'[Month],'_Loss Given Default'[Probability of Default]),
            '_Loss Given Default'[Probability of Default]
    )

VAR _Output = 
    _UnreferencedProbDefault - _ReferenceRatingProbDefault

RETURN 
    _Output

View solution in original post

2 REPLIES 2
ELofstrom3C
Frequent Visitor

ELofstrom3C_0-1683059167805.png

Not sure if this is what you're looking for but here's what I did:
1.) Create a calculated table (I called mine "Ratings") with the code "Ratings = DISTINCT('_Loss Given Default'[S&P Rating])".  This gives me a disconnected table with all the S&P values.
2.) Create a calculated measure in the new Ratings table with the code "Selected Rating = SELECTEDVALUE(Ratings[S&P Rating])".  This traps the value the user selects.
3.) Create a slicer with the Ratings[S&P Rating] field.  The trick here is that there are no relationships from the Ratings table to any other tables.

4.) Create a calculated measure in the Default table "Filter Me = IF(MAX('_Loss Given Default'[S&P Rating]) = [Selected Rating], 1, 0).
5.) In the first table filter on the "Filter Me" measure where the value is >= 1.

6.) In the bottom table filter on the "Filter Me" measure where the value is 0.

@ELofstrom3C 

 

Thanks.  Your output looks like it resulted in the same, but started me down the path I needed.  Disconnected slicer was the key.  Here's the code that worked for my solution:

 

Probability of Default Delta = 

VAR _SelectedRows = 
    FILTER(
        ALL('_Loss Given Default'),
        '_Loss Given Default'[S&P Rating] = SELECTEDVALUE('S&P Ratings'[S&P Rating])
    )

VAR _ReferenceRatingProbDefault = 
    SUMX(
        ADDCOLUMNS(
            VALUES('_Loss Given Default'[Month]),
            "Probability of Default", CALCULATE(
                SUM('_Loss Given Default'[Probability of Default]),
                _SelectedRows,
                '_Loss Given Default'[Month] = EARLIER('_Loss Given Default'[Month])
            )
        ),
        [Probability of Default]
    )

VAR _UnreferencedProbDefault = 
    SUMX(
        GROUPBY('_Loss Given Default','_Loss Given Default'[Month],'_Loss Given Default'[Probability of Default]),
            '_Loss Given Default'[Probability of Default]
    )

VAR _Output = 
    _UnreferencedProbDefault - _ReferenceRatingProbDefault

RETURN 
    _Output

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.