Help with Matrix table visual

Hi harshadrokade,

I looked this up in AI and got this:

✅ Prerequisites (Data Model)
1️⃣ Fact Table (your data)
Fields:

MIS_date
Dept
Category1
Rating Old
Rating New

2️⃣ Rating Master (dimension)
You already have this ✅

RatingWeightR1100R299R398R497R596R695R794R893D(lowest – optional set like 0)
✅ Relationships

Rating Master[Rating] → Fact[Rating Old]
Rating Master[Rating] → Fact[Rating New]
(second relationship inactive)

✅ Core Measure (Movement Count)
This measure powers every rating column (R1…R8, D).

Movement Count :=
VAR OldRating =
SELECTEDVALUE ( 'Fact'[Rating Old] )

VAR NewRating =
SELECTEDVALUE ( 'Rating Master'[Rating] )

RETURN
CALCULATE (
DISTINCTCOUNT ( 'Fact'[Category1] ),
'Fact'[Rating Old] = OldRating,
'Fact'[Rating New] = NewRating
)

✅ Total Column

Total :=
CALCULATE (
DISTINCTCOUNT ( 'Fact'[Category1] ),
ALLEXCEPT (
'Fact',
'Fact'[MIS_date],
'Fact'[Rating Old]
)
)

✅ D / Total Column
D / Total :=
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( 'Fact'[Category1] ),
'Fact'[Rating New] = "D"
),
[Total]
)

✅ Stable Count
(Rating Old = Rating New)

Stable :=
CALCULATE (
DISTINCTCOUNT ( 'Fact'[Category1] ),
'Fact'[Rating Old] = 'Fact'[Rating New]
)

✅ Upgrade Count (Lower → Higher Weight)
Upgrade :=
VAR OldWeight =
LOOKUPVALUE (
'Rating Master'[Weight],
'Rating Master'[Rating], SELECTEDVALUE ( 'Fact'[Rating Old] )
)

RETURN
CALCULATE (
DISTINCTCOUNT ( 'Fact'[Category1] ),
FILTER (
'Fact',
LOOKUPVALUE (
'Rating Master'[Weight],
'Rating Master'[Rating], 'Fact'[Rating New]
) > OldWeight
)
)

✅ Downgrade Count (Higher → Lower Weight)
Downgrade :=
VAR OldWeight =
LOOKUPVALUE (
'Rating Master'[Weight],
'Rating Master'[Rating], SELECTEDVALUE ( 'Fact'[Rating Old] )
)

RETURN
CALCULATE (
DISTINCTCOUNT ( 'Fact'[Category1] ),
FILTER (
'Fact',
LOOKUPVALUE (
'Rating Master'[Weight],
'Rating Master'[Rating], 'Fact'[Rating New]
) < OldWeight
)
)

✅ Matrix Visual Configuration (Very Important)
Rows

MIS_date
Rating Old

Columns

Rating Master[Rating]
(this creates R1…R8…D columns automatically)

Values

Movement Count
Total
D / Total
Stable
Upgrade
Downgrade

✅ Set “Show items with no data = On” for Ratings.

✅ Performance Notes (This Matters at Scale)

Use integer weights, not text comparisons
Keep Rating Master small
Avoid calculated tables
All measures are fully slicer‑aware