cancel
Showing results for
Search instead for
Did you mean:
Leahy268 Visitor

## Calculate columns of a certain value

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-ljerr-msft Super Contributor

## Re: Calculate columns of a certain value

Hi @Leahy268,

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

1 REPLY 1
Highlighted
v-ljerr-msft Super Contributor

## Re: Calculate columns of a certain value

Hi @Leahy268,

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