Forum Discussion

harshadrokade's avatar
harshadrokade
Post Partisan
4 months ago
Solved

Help with Matrix table visual

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_dateDeptCategory1Rating OldRating New
6/30/2025D1123R1R5
6/30/2025D1123R1R5
6/30/2025D1124R1R1
6/30/2025D1125R4R3
6/30/2025D1126R1R6
6/30/2025D1123R2R6
6/30/2025D1124R1D
6/30/2025D3124R3R3
6/30/2025D4126R3R1
6/30/2025D5127R1R7
3/31/2025D1124R7R2
3/31/2025D2125R1R6
3/31/2025D3125R7R3
3/31/2025D4126R3R3



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_dateRating OldR1R2R3R4R5R6R7R8DTotalD/TotalStableUpgradeDowngrade
6/30/2025R11   11  140.251 3
6/30/2025R2     1   10  1
6/30/2025R3         00   
6/30/2025R4  1      10 1 
6/30/2025R5         00   
6/30/2025R6         00   
6/30/2025R7 1       10 1 
6/30/2025R8         00   


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_dateDeptCategory1Rating OldRating New
6/30/2025D1123R1R5
6/30/2025D1123R1R5
6/30/2025D1124R1R1
6/30/2025D1126R1R6
6/30/2025D1124R1D


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

RatingWeight
R1100
R299
R398
R497
R596
R695
R794
R893


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.

Need Help

6 Replies

  • collinq's avatar
    collinq
    Super User
    4 months ago

    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

     



    • harshadrokade's avatar
      harshadrokade
      Post Partisan
      4 months ago

      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.