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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.