Trying to create a dax query to compare one persons test scores to the average of the others

[Popeye55]

I would like to be able to use a slicer to select someone by their ID number and then display a line chart where one line is the selected individual's scores over time and the other is the average of everone esle. Is this possible using DAX queries?

Here is an example of the data:

ID number	Q1 score	Q2 score	Q3 score	Q4 score
1	75	54	67	86
2	80	36	54	75
3	90	76	90	84

For example if i selected ID# 1 the first line in the chart would show his scores and the second line would average the other two people's scores.

[tamerj1]

Hi @Popeye55 
Here is a sample file https://we.tl/t-dxOdnQphje

You need first to unpivot your data perhaps comfortably using power query

[BITomS · Accepted solution]

Hi @Popeye55 ,

I think the first thing you need to do is unpivot your data table as I don't see a date field to create your 'view over time'. Details on how to do this are here: https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

So your table would effectively look like this:

ID	Score	Date
1	75	01/01/2022 (or whatever Q1 start date is)
1	54	01/04/2022 (or whatever Q2 start date is)
1	67	01/07/2022 (or whatever Q3 start date is)
1	86	01/10/2022 (or whatever Q4 start date is)
2	80	01/01/2022 (or whatever Q1 start date is)
2	36	01/04/2022 (or whatever Q2 start date is)
2	54	01/07/2022 (or whatever Q3 start date is)
2	75	01/10/2022 (or whatever Q4 start date is)
etc.	etc.	etc.

Then you could have one measure just as = AVERAGE('TABLE'[Score]) . This will update to show a single ID in your line chart when the ID has been selected from a slicer.

Your second measure for the overall average would be = CALCULATE(AVERAGE('TABLE'[Score]),ALL('TABLE'[ID])) . This will ignore any slicer selection made by a user.

If you want to exclude the user selected ID, the second measure above can be enhanced by using = CALCULATE(AVERAGE('TABLE'[Score]),ALL('TABLE'[ID]),'TABLE'[ID])<>SELECTEDVALUE('TABLE'[ID])))