Calculate columns of a certain value

[Anonymous]

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

[v-ljerr-msft · Accepted 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