Forum Discussion
Calculating Test Scores by Question and by User
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.
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!
3 Replies
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!