Calc average at higher level of granularity

[jafa1970]

Hi there,

I have a dataset with two levels of granularity in the one table and would like some guidance on how to approach doing analysis at the higher level.

I am working with student data and each student is enroled Units. At a higher level, the student is enrolled in a Course as well, and the Units make up the course. The data is in one table, so the Course info is repeated for each Unit enrolment. (I am working with a flat text file export - unable to get database access)

I want to work out average time to complete a Course, and have the Course Commence Year, and the Course Completion year (in a separate table). I have a calculated column that works out the years taken complete the course, but this value shows against every Unit for that Student-Course combination.

I am not sure how to work out the average at the Course level.

Any suggestions on the approach would be appreciated (am happy to try and work out the DAX, just need an idea on what to play with)

The table looks something like this (abbrev. to show relevant info):

Student ID|Course Code|Unit Code|Course Commencement Year|calc col - Years to Complete
123|ABC|U1|2010|3
123|ABC|U2|2010|3
987|ABC|U3|2014|0


Cheers
Phil

 

[jafa1970]

Thanks scottsen, had headed down that path. (Also found a powerpivotpro blog post on Measures v Calc Columns which was useful reading to get a better grasp on the use of each)

I have the formula you suggested, and the result I am getting is puzzling though. When I look at the Course-Student level the value is correct, however when I look at the Course level on its own the Average is 3.615, I was expecting 3.4 (17/5).

[TABLE="width: 226"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]COURSE_CODE[/TD]
[TD]STUDENT_ID[/TD]
[TD]Avg#2[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ABC Total[/TD]
[TD][/TD]
[TD="align: right"]3.615385[/TD]
[/TR]
</tbody>[/TABLE]



Wondering if I am looking at this the wrong way perhaps.

Cheers

[TABLE="width: 226"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

Can you share your workbook? I mean, if each of the A-E rows are made up of MANY rows, it could make sense, but I would need to see it.