Cumulative Grades Matrix

  • I thought I'd put our new section to the test! This is a genuine query, so any help will be gratefully received.


    I have a Power BI report based on data that is in this format:


    Excel 2016 (Windows) 32 bit
    [Table="width:, class:head"][tr=bgcolor:#888888][th][COLOR="#FFFFFF"] [/COLOR][/th][th]

    [COLOR="#FFFFFF"]A[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]B[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]C[/COLOR]

    [/th][/tr]
    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]1[/COLOR]

    [/td]

    [td]

    Student

    [/td][td]

    Subject

    [/td][td]

    Grade

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]2[/COLOR]

    [/td]

    [td]

    Student 1

    [/td][td]

    Art

    [/td][td]

    A*

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]3[/COLOR]

    [/td]

    [td]

    Student 2

    [/td][td]

    Art

    [/td][td]

    A*

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]4[/COLOR]

    [/td]

    [td]

    Student 3

    [/td][td]

    Art

    [/td][td]

    C

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]5[/COLOR]

    [/td]

    [td]

    Student 4

    [/td][td]

    Art

    [/td][td]

    E

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]6[/COLOR]

    [/td]

    [td]

    Student 5

    [/td][td]

    Art

    [/td][td]

    U

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]7[/COLOR]

    [/td]

    [td]

    Student 6

    [/td][td]

    Art

    [/td][td]

    C

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]8[/COLOR]

    [/td]

    [td]

    Student 7

    [/td][td]

    Art

    [/td][td]

    B

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]9[/COLOR]

    [/td]

    [td]

    Student 8

    [/td][td]

    Art

    [/td][td]

    B

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]10[/COLOR]

    [/td]

    [td]

    Student 9

    [/td][td]

    Art

    [/td][td]

    D

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]11[/COLOR]

    [/td]

    [td]

    Student 10

    [/td][td]

    Art

    [/td][td]

    A*

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]12[/COLOR]

    [/td]

    [td]

    Student 11

    [/td][td]

    Art

    [/td][td]

    A

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]13[/COLOR]

    [/td]

    [td]

    Student 12

    [/td][td]

    Art

    [/td][td]

    C

    [/td]

    [/tr]
    [/table]
    [Table="width:, class:grid"]

    [tr][td]

    Sheet: Sheet1

    [/td][/tr]

    [/table]


    I am trying to create a visual that summarises the data thus, with cumulative grade counts and percentages:


    Excel 2016 (Windows) 32 bit
    [Table="width:, class:head"][tr=bgcolor:#888888][th][COLOR="#FFFFFF"] [/COLOR][/th][th]

    [COLOR="#FFFFFF"]E[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]F[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]G[/COLOR]

    [/th][/tr]
    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]2[/COLOR]

    [/td]

    [td]

    A*-A

    [/td][td]

    A*-C

    [/td][td]

    A*-E

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]3[/COLOR]

    [/td]

    [td]

    4

    [/td][td]

    9

    [/td][td]

    11

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]4[/COLOR]

    [/td]

    [td]

    33.33

    [/td][td]

    75.00

    [/td][td]

    91.67

    [/td]

    [/tr]
    [/table]
    [Table="width:, class:grid"]

    [tr][td]

    Sheet: Sheet1

    [/td][/tr]

    [/table]


    I am able to use the matrix visual to create a table with each of the grades and their percentages individually, but not cumulatively.


    Anyone know how it can be done? I have thought about a grouping column in the underlying data, but this doesn't work because of the overlap (e.g. grade A being in all categories and not just one).

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • If you put the grades in the columns, you can group them in pairs but rename the groups to match your desired headings. Then add a quick measure to do a running total of the count. I haven't had a chance to look at the percentage but would think you could do something similar there.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Sorry, Rory - I'm still finding my way with Power BI. What do you mean by putting the grades in the columns? Which columns do you mean? If I have chance later, I'll mock up a .pbix file.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • The Matrix is a little like a pivot table and has row/column areas to drop fields into. (I am assuming Power BI Desktop?)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • if you have a pbix I will try and take a look too on the PBI desktop

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Here we go. If anyone fancies having a go, I'd be delighted to see how it's done!

    Files

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Thanks, chaps, for looking - please see the attached now. I really don't know what happened there!!!


    PS I don't like these comment things - you can't see that someone has posted from the new posts view. I can see notifications, but that's not my preferred MO - just another thing I dislike about this new version - ugh!!!


    PPS On second inspection, comments don't even trigger a notification, so there was no way of my knowing they were here without looking at the thread again. Sorry about that! :(

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Here's the running totals and groups. I need to have a think about how to present the percentages at the bottom. [ATTACH]n1200501[/ATTACH]

    Files

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Thanks again, Rory - this is a great start. I'll delve into the method you've used later. If you have any ideas about the percentage, let me know. If it has to be a separate table, that's fine, too.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • If a separate table is OK, you could just copy the one that's there and choose to show the values as a % of row total?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • FYI - this little project had had to go on hold for a while. As soon as i get back to it, I'll ckeckthis out. Thanks again for the help!

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules