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

    Yes, that's how I have done it. I think I need a sample .pbix! Will do that later.

    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 have a pbix I will try and take a look too on the PBI desktop

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


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

    _______________________________________________

    Thank you - I will post one tomorrow morning. :)

    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 we go. If anyone fancies having a go, I'd be delighted to see how it's done!

    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]

    Thanks, Rory - I shall take a look presently. :)

    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 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

    I haven’t had time to have a proper look at this yet, but I am sure you are right - thanks!

    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

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login