cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leahy268 Visitor
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 NameQ1Q2Q3Q4
HarryADAC
JohnBDAB
BenBAAA
TomCBBC
Correct AnswerBDAC
DisciplineTrigonometryPerimetersAlgebraMultiplication
Percent of AU that got it correct0.50.70.90.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
v-ljerr-msft Super Contributor
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.

 

r1.PNG

 

Here is the test pbix file for your reference. Smiley Happy

 

Regards

1 REPLY 1
v-ljerr-msft Super Contributor
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.

 

r1.PNG

 

Here is the test pbix file for your reference. Smiley Happy

 

Regards