Help with Matrix table visual

[harshadrokade]

Hi, First of all, Thanks for your time to read this long thread and a complex requirement.

I have data as below (Dummy data shared)-(Unfortunately i cant share the data file)

MIS_date	Dept	Category1	Rating Old	Rating New
6/30/2025	D1	123	R1	R5
6/30/2025	D1	123	R1	R5
6/30/2025	D1	124	R1	R1
6/30/2025	D1	125	R4	R3
6/30/2025	D1	126	R1	R6
6/30/2025	D1	123	R2	R6
6/30/2025	D1	124	R1	D
6/30/2025	D3	124	R3	R3
6/30/2025	D4	126	R3	R1
6/30/2025	D5	127	R1	R7
3/31/2025	D1	124	R7	R2
3/31/2025	D2	125	R1	R6
3/31/2025	D3	125	R7	R3
3/31/2025	D4	126	R3	R3

I have slicers created in power bi on MIS_date column and on Category2 column

I want to create a matrix visual on screen as below where all the possible Ratings will reflect in second column rows (Rating Old column) and all the same Ratings will be shown in a seperate column on right side (All columns after Rating Old column) as shown below. Basically I want to show the rating movements based on distinct count of Category1 values.

Illustrative example for D1 selected in Department slicer and Jun 25 Qtr end date selected on Slicer, 

MIS_date	Rating Old	R1	R2	R3	R4	R5	R6	R7	R8	D	Total	D/Total	Stable	Upgrade	Downgrade
6/30/2025	R1	1				1	1			1	4	0.25	1		3
6/30/2025	R2						1				1	0			1
6/30/2025	R3										0	0
6/30/2025	R4			1							1	0		1
6/30/2025	R5										0	0
6/30/2025	R6										0	0
6/30/2025	R7		1								1	0		1
6/30/2025	R8										0	0

Logics explanation-
For D1 selected in Department slicer and for Jun 25 Qtr end date selected on Slicer, 
We first filter on Rating Old column and select first rating i.e. R1 and then we check what is the new rating for all R1 in column Rating New. Refer below filtered table-

MIS_date	Dept	Category1	Rating Old	Rating New
6/30/2025	D1	123	R1	R5
6/30/2025	D1	123	R1	R5
6/30/2025	D1	124	R1	R1
6/30/2025	D1	126	R1	R6
6/30/2025	D1	124	R1	D

The distinct count of Category1 values is updated in the related Rating columns.

E.g. we see there are two entries where rating changed from R1 to R5 in new rating column but the distinct count of Category1 is 1 and so we update 1 in front of R1 row and in R5 column.
Then we see There is 1 entry where the R1 rating remained R1 in new rating column so we update 1 in front of R1 row and in R1 column.
Then we see There is 1 entry where the R1 rating remained R6 in new rating column so we update 1 in front of R1 row and in R6 column.
This is how we keep checking each rating (R2, R3, R4...) in Rating old column and the distinct count of Category1 column values is updated in respective Rating column of New Rating.

Total column is total of all Rating columns i.e. R1 to D
D/Total column is count in D column is divided by Total

I have another master table called Rating Master which decides which rating has columns as Rating and is weight

Rating	Weight
R1	100
R2	99
R3	98
R4	97
R5	96
R6	95
R7	94
R8	93

Stable column- Stable will show count of all those rating where rating remained same as in rating old and rating new column
Upgrade column- Upgrade will show count of all those ratings which moved from lower weight to higher weight rating
Downgrade column- Downgrade will show count of all those ratings which moved from Higher weight to Lower weight rating

I have already updated the table above as oer logics mentioned based on given data.

Request you to help me to achive this in power bi table/matrix visual.

[Gabry]

Hey buddy @harshadrokade ,

I prepared a pbix file attached. If I understood correctly I did everything you needed.

Please let me know if it's ok.

Cya

[harshadrokade]

Thanks a lot @Gabry . This was very very helpful.

JUst one problem. The table visual is not showing all the ratings in Rating Old column because we have used cross join of Old and New rating column of the Main table. I want to show all the Ratings in Rating Old column and every rating column should appear on right side (which is already there). The Rating column will be blank if there is no value foe any row of Rating Old column but it will show all raitngs.

As of now, the table doesnt show few ratings if those are not part of the table. Rating master has list of all ratings

Can u pls help. 

[Gabry · Accepted solution]

@harshadrokade  Ok no problem. Here you are: 

You can find the updated pbix attached.
Don't forget to accept the solution if this solve your issue 😉

[harshadrokade]

Thanks a lot @Gabry . You are awesome. 

SOmehow my Downgrade and Upgrade total is not coming right. I will check again and if i cant fix it, I will seek for your help again. Pls help if you have time..

[Gabry]

Hey @harshadrokade . I don't understand. To me it looks good and works correctly

if it's not what you need please provide better example / explanation

[collinq]

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