Announcement

Collapse
No announcement yet.

Cumulative Grades Matrix

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    1
    Student Subject Grade
    2
    Student 1 Art A*
    3
    Student 2 Art A*
    4
    Student 3 Art C
    5
    Student 4 Art E
    6
    Student 5 Art U
    7
    Student 6 Art C
    8
    Student 7 Art B
    9
    Student 8 Art B
    10
    Student 9 Art D
    11
    Student 10 Art A*
    12
    Student 11 Art A
    13
    Student 12 Art C
    Sheet: Sheet1
    I am trying to create a visual that summarises the data thus, with cumulative grade counts and percentages:

    Excel 2016 (Windows) 32 bit
    2
    A*-A
    A*-C
    A*-E
    3
    4
    9
    11
    4
    33.33
    75.00
    91.67
    Sheet: Sheet1
    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.

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment


        • #5
          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.

          Comment


          • #6
            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!
            _______________________________________________

            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

            _______________________________________________

            Comment


            • #7
              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.

              Comment


              • #8
                Here we go. If anyone fancies having a go, I'd be delighted to see how it's done!
                Attached Files
                Last edited by AliGW; March 3rd, 2018, 22:54.
                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.

                Comment


                • rory
                  rory commented
                  Editing a comment
                  Might just be me, but I can't access either of those files.

                • KjBox
                  KjBox commented
                  Editing a comment
                  Nor can I, both files are showing a File size of 0!

              • #9
                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.

                Comment


                • #10
                  Here's the running totals and groups. I need to have a think about how to present the percentages at the bottom. Test Data for Mocks.pbix
                  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

                  Comment


                  • #11
                    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.

                    Comment


                    • #12
                      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.

                      Comment


                      • #13
                        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

                        Comment


                        • #14
                          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.

                          Comment


                          • #15


                            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.

                            Comment

                            Working...
                            X