Calculating Test Scores by Question and by User

[Anonymous]

Hello,

I am having some difficulties trying to work out something in DAX/Power BI that I know how to do in Excel. Am taking my first stepts in Power BI and DAX and could really use some help.

Say that one of the Data Sources are a table with test results from a list of test takers as below.

	Question 1 Score	Question 2 Score	Question 3 Score		User Score
User1	3	1	1		0.555555556
User2	3	1	3		0.777777778
User3	2	1	3		0.666666667
Question Scores	0.888888889	0.333333333	0.777777778

I like to have both User and question scores so that I can track success by user and by question.

I am using the current formulas in Excel.

	Question 1 Score	Question 2 Score	Question 3 Score	User Score
User1	3	1	1	=SUM(B3:D3)/(COUNTA(B3:D3)*3)
User2	3	1	3	=SUM(B4:D4)/(COUNTA(B4:D4)*3)
User3	2	1	3	=SUM(B5:D5)/(COUNTA(B5:D5)*3)
	=(SUM(B3:B5))/((COUNTA(B3:B5))*3)	=(SUM(C3:C5))/((COUNTA(C3:C5))*3)	=(SUM(D3:D5))/((COUNTA(D3:D5))*3)

I want to replicate the same calculations in Power BI but cannot find a way to lock the SUM and COUNTA ranges to a limited array of cells. 

Or maybe this is the wrong way of doing it....

Thank you for your help.

[waltheed · Accepted solution]

Hi jagostinhoCT,

Yes you can do it, in two ways...

If you keep the structure of your table the way it is now, it's more manual work and not so flexible..

Create a calculated column, called avgscore:

= ([q1] + [q2] + [q3] ) /3

Add measures for each question (column):

avgscore q1:=AVERAGEX('ScoreTable';[q1])

avgscore q2:=AVERAGEX('ScoreTable';[q2])

avgscore q3:=AVERAGEX('ScoreTable';[q3])

(You divide everything by 3 again, but I skipped that :-))

You could use those like in the pivot table shown below.

It would be better to change the table structure, like the one in the screenshot above, with columns: User, Question, and Score. So just one column for the questions!

Then you would only need ONE measure:

avg score:=AVERAGEX('MyTable'; [score])

Then drag the avg score measure,the row headers, and column headers in and everything just works!

[waltheed · Accepted solution]

Hi jagostinhoCT,

Yes you can do it, in two ways...

If you keep the structure of your table the way it is now, it's more manual work and not so flexible..

Create a calculated column, called avgscore:

= ([q1] + [q2] + [q3] ) /3

Add measures for each question (column):

avgscore q1:=AVERAGEX('ScoreTable';[q1])

avgscore q2:=AVERAGEX('ScoreTable';[q2])

avgscore q3:=AVERAGEX('ScoreTable';[q3])

(You divide everything by 3 again, but I skipped that :-))

You could use those like in the pivot table shown below.

It would be better to change the table structure, like the one in the screenshot above, with columns: User, Question, and Score. So just one column for the questions!

Then you would only need ONE measure:

avg score:=AVERAGEX('MyTable'; [score])

Then drag the avg score measure,the row headers, and column headers in and everything just works!

[Anonymous]

@waltheed

Many thanks.