The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
78 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
104 | |
93 | |
52 | |
50 | |
46 |