5 Replies Latest reply on Jun 20, 2017 11:56 AM by Katie Poznanski-Ring

# Calculating dimension average using FIXED when data are duplicated

Hello,

I am stumped trying to figure out how to compute an unduplicated average when my data are duplicated, and I’m hoping the community can help.

I am working with data about student evaluations of courses taken. Rows in my data reflect counts of responses for a course-instructor-item combination (i.e. 20 Strongly Agrees for MATH 101, taught by person A, questionnaire item 1). I need to calculate averages across courses for dimensions in the data, like an academic department. Some items on the questionnaire are about the course, and some are about the instructor. For the department average, the calculation for instructor items should include all instructors, but the calculation for course items should only include each course once when there are multiple instructors. I also need to be able to display the department average on an individual report about a course-instructor pair.

Using the data shown below, I would like the English department average for CRS1 to reflect (2.80+2.47+2.60)/3 = 2.62, rather than (2.80+2.47+2.47+2.60)/4 = 2.58

I appreciate any advice you can offer!

• ###### 1. Re: Calculating dimension average using FIXED when data are duplicated

Can you please post 10.0 version? thank you

Sherzod

• ###### 2. Re: Calculating dimension average using FIXED when data are duplicated

Here's a 10.0 version.

1 of 1 people found this helpful
• ###### 3. Re: Calculating dimension average using FIXED when data are duplicated

Edited and bumped

• ###### 4. Re: Calculating dimension average using FIXED when data are duplicated

I dug further into existing posts in the forum and found one that solved my problem.

How to count a value only one time per ID

This involved a couple of steps:

1. Create a field (First Record) that identifies the first instructor record among course records:

FIXED [Course] : MIN([Join Id])}

2. Create a field (Undup Crs Mean) that populates the desired value only for the first record:

IF [Join Id] = [First Record] THEN [Crs Mean] END

3. Create a field (Dept Mean) that calculates the department average using Undup Crs Mean for course items and Crs Mean for instructor items:

IF [Item Type]='Course'

THEN { FIXED [Department],[Item Id] : AVG([Undup Crs Mean]) }

ELSEIF [Item Type]='Instructor'

THEN { FIXED [Department],[Item Id] : AVG([Crs Mean]) }

END

• ###### 5. Re: Calculating dimension average using FIXED when data are duplicated

Hi Michael,

Congrats on finding your solution! I'm wrestling with something similar myself, but the reports I'm trying to compose are intended to be instructor-specific rather than Department aggregation. So, as the picture indicates, each instructor would receive a summary report displaying results for each of their courses as well as the department's aggregate and the college aggregate. I can't wrap my head around how to structure the data to accomplish the display. Do you have any ideas?

Thanks!

Katie