How to display Exam Grades in Venn Diagram

[Fairos]

How can i plot Students Marks (A, B,B+,C,D and F) into Venn Diagram? 

[KarinSzilagyi · Accepted solution]

Hi @Fairos, I made a quick example-file for you since you'll also need to transform your data to make this work. Link to RadarChart_Test.pbix  

To get started with what you're trying to do you'll need to transform your data to get it into this format:

If you open the PowerQuery Editor (Home > Transform data) you can see the step by step guide on how I went from your current format to this.

Step 1: Select the columns "RecNo" and "Name" and apply Transform > Unpivot Columns > Unpivot other columns

Step 2: Add a custom column to extract the Subject. I used a Conditional Column here since it's easier to adjust if you aren't used to writing M-Code.

Step 3: I then added another conditional column so we can separate "Rate" and "Score" in one of the next steps:

Step 4: Remove the "Attribute" column (since we don't need it anymore)

Step 5: Select the "Type" column and apply Trasform > Pivot Column with "Values Column" set to the column "Value" and use Advanced Options > "Aggregate Value Function" = "Don't aggregate"

Step 6: Change the column types as needed (e.g. Score = whole number, Rate = text) 

Step 7: Close and apply

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVRNj9owEP0rVq5FasIGAscsiCJBWmmRilrEYWjM2trEpiFeBL++M04chyoHW6PE8zLvwzkcgigYBW/mASbPpWKvuH6DVBKfxjFuS1zTELdXXHMqUlzJlJ58CY6jQzAmAP2QBVB3LVmmBZQ8tzj4bkJnF1TM2yIJm+5RMJtZQIJ5weq7qbCrkB8C2MlivUH+KMHC0NEVrnHSFh1wg2dRaOQVyEpfWXrKTcEyUyPghVhMWhbTmfu8H6TFIgg6ttU3tuFavbM950MTY+G0GId2HmolkB2o/M62tnltcNtqjY+j0J3vZI3DlkgrKyEQt0w+oMbR07+oqWhFTSt4lOTKfN7idPRfJi1O3xUacyexcSNQDqOUvDp/fpRQ0enEcfKzWQQLHnZ60Pd2wgjInSk7KKHuK+pFccU06SyJCOqn/Ljx6ydUksHXC8pT/xHoq3XWi+tHGvI2oqimsuBcsTWaqnLHqLF6DaXMncCrbohnuMRLFFFyv8H5rCsixrJ7bgPrZOzR6Y0Y9gAos2lx4lXNFgIU26NdC6G1Gu71OrvKosQ2NfJsCjS+tFcQgTyV5RNIk15vu4WgciGwcSsvbG9M/652QfXqJrMeCWK75ZylODtcnPfLYTv6vlJJPmAYtHJONMkYgBi8axENkvHq846c6cZsbrT/4rdB0XuRa5WxIHPLgBqJx0YYGyufzvj/dD79vkI7wjsojuqVLOP4B7M58Ao6AP8LaYrj8R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RecNo = _t, Name = _t, BMScore = _t, BMRate = _t, EngScore = _t, EngRate = _t, MatsScore = _t, MatsRate = _t, HisScore = _t, HisRate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RecNo", Int64.Type}, {"Name", type text}, {"BMScore", Int64.Type}, {"BMRate", type text}, {"EngScore", Int64.Type}, {"EngRate", type text}, {"MatsScore", Int64.Type}, {"MatsRate", type text}, {"HisScore", Int64.Type}, {"HisRate", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RecNo", "Name"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Subject", each if Text.Contains([Attribute], "BM") then "BM" else if Text.Contains([Attribute], "His") then "His" else if Text.Contains([Attribute], "Eng") then "Eng" else if Text.Contains([Attribute], "Mats") then "Mats" else null, type text),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Type", each if Text.Contains([Attribute], "Score") then "Score" else "Rate"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Score", Int64.Type}, {"Rate", type text}})
in
    #"Changed Type1"

-------
I've also added examples of different Radar Charts you can install in the file to show you how you could use them and added the names both to the bookmarks + in the title of each visual

I hope this helps!

If this post helped resolve your issue, please mark it as the solution to make it easier for future readers to find.

[Fairos]

Hi Karin, 

100% you have understood my intentions and provided the solutions. I thank you and salute you for your expertise. 

Accept as Solution.

[Fairos]

There are about 137 students going to taking Major Exam with 12 subjects and i am helping the teachers to do the PowerBI Dashboard to display the exam results. 

1. Exam score and grades in Matrics Table - Done.
2. Comparings of how many students have :-  Stuck here.
Top score
12A's,

10A's + 2B's, and so on

Percentage of Passings and so on. 

[Khashayar]

Hi, and I hope my answer can help you.

In your dataset, the Venn diagram isn’t showing values because it’s not the right chart type for categorical data such as student grades (A, B+, C, D, F). A Venn diagram is meant to show overlaps between groups, not grade distributions.

Here are some better ways to visualize your data in Power BI:

Option 1: Stacked Column Chart (Recommended): Purpose: Compare how many students achieved each grade in every subject.

Option 2: Matrix Table: Purpose: Show a cross-tab view of grades per subject.

Option 3: Venn Diagram (Only for Overlaps): If you want to see which students got A in multiple subjects, then a Venn diagram is suitable.

If you found this post helpful, please consider accepting it as the solution so that other members can find it more easily.

Regards,

Khashayar Yazdani | Microsoft MCT

https://www.linkedin.com/in/khashayary/

[KarinSzilagyi]

Hi @Fairos, I know, this isn't what you asked, but if you want to see a distribution of how the students are doing in different areas I would recommend to use a different visual like a Radar Chart rather than a Venn diagramm.

(Quick example from google, as I'm on mobile right now). I would use the different grades as layers, that way you can show how the grades of students in different subjects are distributed - e.g. "X percent of students that got an A in Maths also got an A in English".

Example: https://youtu.be/c9qZJ04T-6k?si=qnodaHasTFbwrHC7 

[Fairos]

Tq Karin, 

I shall try Radar chart for now.

[Fairos]

is this doable for around 150 - 200 students?

[KarinSzilagyi]

Do you want to compare the grades of individual students (e.g. the grades of Megan vs Fairos) or an overview of the distribution of grades per subject accross all 150-200 students? Either way would be possible.

[Fairos]

There are about 137 students going to taking Major Exam with 12 subjects and i am helping the teachers to do the PowerBI Dashboard to display the exam results. 

1. Exam score and grades in Matrics Table - Done.
2. Comparings of how many students have :-  Stuck here.
Top score
12A's,

10A's + 2B's, and so on

Percentage of Passings and so on. 

[Ashish_Mathur]

Hi,

For your second question as well, a table/matrix would be the best visual.