Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
PBIX file is here: https://drive.google.com/file/d/1YJ7I8PYqqEiap-mPjvprMlsLh9fv5Jkb/view?usp=sharing
In that file are three Matrices:
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 Labels | 12 | 24 | 36 | 48 | 60 | 72 | 84 | 96 | 108 | 120 | 132 | 144 | 156 | 168 | 180 |
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%) |
BBB | 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% | 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% |
BB | 0.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% |
B | 2.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% |
CCC | 28.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!
Solved! Go to Solution.
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
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.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
55 | |
54 | |
37 | |
29 |
User | Count |
---|---|
77 | |
62 | |
45 | |
40 | |
40 |