Forum Discussion

mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
3 years ago
Solved

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:

 

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!

 

 

Need Help
  • mrothschild's avatar
    mrothschild
    3 years ago

    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

2 Replies

  • ELofstrom3C's avatar
    ELofstrom3C
    Frequent Visitor
    3 years ago

    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.

    • mrothschild's avatar
      mrothschild
      Icon for Continued Contributor rankContinued Contributor
      3 years ago

      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