Simple DAX Assistance - Unpivoting done, averages not coming out correctly

[afaherty]

Hello,

Here is some dummy data to represent my problem. Each student can be in more than 1 College Major, so the data is unpivoted so that each student has data for each of their majors.

I need to get the average of the tests but also the subtests. I have drawn a silly little depiction of how I am setting up the dashboard, including slicers. I was getting the correct averages UNTIL I unpivoted the data and now it's duplicating the averages.

Student ID	Test	Subtest	Score	College Major
1	Math	Decimals	3	Middle School Math
1	Math	Fractions	5	Middle School Math
1	Math	Equations	3	Middle School Math
1	Math	Decimals	3	Middle School English
1	Math	Fractions	5	Middle School English
1	Math	Equations	3	Middle School English
1	English	Contractions	4	Middle School Math
1	English	Prepositions	2	Middle School Math
1	English	Diagramming	3	Middle School Math
1	English	Contractions	4	Middle School English
1	English	Prepositions	2	Middle School English
1	English	Diagramming	3	Middle School English
2	Math	Decimals	5	Middle School Math
2	Math	Fractions	5	Middle School Math
2	Math	Equations	3	Middle School Math
2	Math	Decimals	5	Middle School English
2	Math	Fractions	5	Middle School English
2	Math	Equations	3	Middle School English
2	English	Contractions	3	Middle School Math
2	English	Prepositions	4	Middle School Math
2	English	Diagramming	3	Middle School Math
2	English	Contractions	3	Middle School English
2	English	Prepositions	4	Middle School English
2	English	Diagramming	3	Middle School English

Thank you in advance to anyone who can assist!

[Anonymous]

Hi @afaherty,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Irwan]

hello @afaherty 

has the sample data above been unpivoted?

also how to get Average for Math = 12? is this 12 come from the duplicate as you mentioned?

apart from those, please check if this accomodate your need.

not sure why but looks like your expected outcome is not matched with the sample data (perhaps because of the duplication).

nonetheless, since you want to show specific value of subtest and student id, you need to make different measure for each subtest and student id.

Hope this will help.

Thank you.

[afaherty]

@Irwan Thanks! So I got 12 because student 1's score for Math is 11 (3+5+3) and student 2's score for Math is 13 (5+5+3). That makes 24, divided by 2 students = 12.

[Irwan · Accepted solution]

hello @afaherty 

this DAX for the average

Test Average Score =
DIVIDE(
    [Overall Score Student 1]+[Overall Score Student 2],
    DISTINCTCOUNT('Table'[Student ID])
)

Hope this will help.

Thank you.

[afaherty]

Thanks so much!