Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
So early days in Power BI so far. However I have been asked to use it to collect data on what our students don't know.
So I have the following table:
Student Name | Q1 | Q2 | Q3 | Q4 |
Harry | A | D | A | C |
John | B | D | A | B |
Ben | B | A | A | A |
Tom | C | B | B | C |
Correct Answer | B | D | A | C |
Discipline | Trigonometry | Perimeters | Algebra | Multiplication |
Percent of AU that got it correct | 0.5 | 0.7 | 0.9 | 0.95 |
That would in the original continue to about 20 questions and the Disciplines would be the same for multiple Questions. However for the life of me I can't seem to work out what it I need to use in PowerBI to be able to calculate how many of our students got the correct answer. This would need to be a dynamic thing so that we can graph which areas the school as a whole needs to concentrate on and then drill to each class. I can easily calculate a row in Excel but can't work this one out in PowerBI at all.
Any help would be most appreciated.
Thanks
Warwick
Solved! Go to Solution.
Hi @Anonymous,
If I understand you correctly, you should be able to follow steps below to calculate how many of students who got the correct answer for each Question or Discipline.
1. I assume your data table is called "Table1".
2. Use the M queries below to create 3 tables in Query Editor.
Student And Answer
let Source = Table1, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Student Name] <> "Correct Answer" and [Student Name] <> "Discipline" and [Student Name] <> "Percent of AU that got it correct"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Student Name"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Question"}, {"Value", "Answer"}}) in #"Renamed Columns"
Correct Answer For Questions
let Source = Table1, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Student Name] = "Correct Answer"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Student Name"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Question"}, {"Value", "Correct Answer"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Student Name"}) in #"Removed Columns"
Questions
let Source = Table1, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Student Name] = "Discipline"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Student Name"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Question"}, {"Value", "Discipline"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Student Name"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Question"},#"Correct Answer For Questions",{"Question"},"Correct Answer For Questions",JoinKind.LeftOuter), #"Expanded Correct Answer For Questions" = Table.ExpandTableColumn(#"Merged Queries", "Correct Answer For Questions", {"Correct Answer"}, {"Correct Answer For Questions.Correct Answer"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded Correct Answer For Questions",{{"Correct Answer For Questions.Correct Answer", "Correct Answer"}}) in #"Renamed Columns1"
3. Only load "Student And Answer" and "Questions" to your model.
4. Create a relationship between these two table with the Question column.
5. Use the DAX below to create a new measure to calculate how many of students who got the correct answer.
Measure = CALCULATE ( COUNTA ( 'Student And Answer'[Student Name] ), FILTER ( 'Student And Answer', 'Student And Answer'[Answer] = MAX ( Questions[Correct Answer] ) ) )
6. Show the measure with Questions[Question] or Questions[Discipline] column on Table/Matrix visual.
Here is the test pbix file for your reference.
Regards
Hi @Anonymous,
If I understand you correctly, you should be able to follow steps below to calculate how many of students who got the correct answer for each Question or Discipline.
1. I assume your data table is called "Table1".
2. Use the M queries below to create 3 tables in Query Editor.
Student And Answer
let Source = Table1, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Student Name] <> "Correct Answer" and [Student Name] <> "Discipline" and [Student Name] <> "Percent of AU that got it correct"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Student Name"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Question"}, {"Value", "Answer"}}) in #"Renamed Columns"
Correct Answer For Questions
let Source = Table1, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Student Name] = "Correct Answer"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Student Name"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Question"}, {"Value", "Correct Answer"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Student Name"}) in #"Removed Columns"
Questions
let Source = Table1, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Student Name] = "Discipline"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Student Name"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Question"}, {"Value", "Discipline"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Student Name"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Question"},#"Correct Answer For Questions",{"Question"},"Correct Answer For Questions",JoinKind.LeftOuter), #"Expanded Correct Answer For Questions" = Table.ExpandTableColumn(#"Merged Queries", "Correct Answer For Questions", {"Correct Answer"}, {"Correct Answer For Questions.Correct Answer"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded Correct Answer For Questions",{{"Correct Answer For Questions.Correct Answer", "Correct Answer"}}) in #"Renamed Columns1"
3. Only load "Student And Answer" and "Questions" to your model.
4. Create a relationship between these two table with the Question column.
5. Use the DAX below to create a new measure to calculate how many of students who got the correct answer.
Measure = CALCULATE ( COUNTA ( 'Student And Answer'[Student Name] ), FILTER ( 'Student And Answer', 'Student And Answer'[Answer] = MAX ( Questions[Correct Answer] ) ) )
6. Show the measure with Questions[Question] or Questions[Discipline] column on Table/Matrix visual.
Here is the test pbix file for your reference.
Regards
User | Count |
---|---|
73 | |
69 | |
36 | |
25 | |
22 |
User | Count |
---|---|
96 | |
94 | |
53 | |
45 | |
39 |