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

 

[scottsen]

Any chance this easy dude works for you?

=SUM(Table1[YearToComplete]) / COUNTROWS(Table1)

 

[jafa1970]

Hi, thanks for the suggestion.

Tried that formula. When I bring it into the pivot, if I use a field setting of average, I get the same value for every course.

What I am trying to achieve is the Average Years to Complete for each Course (ie multiple student completing a course, what is the average of that), however the row level of my data is more granular than Course, so there will be multiple rows for each Student for all the units they did.

Happy to provide more info, will try to mock up some data to give a clearer indication of the requirement.

Cheers.

 

[XLBob]

Try this AVERAGEX(VALUES(Table1[Student ID]),SUM(Table1[calc col - Years to Complete])/COUNTA(Table1[Unit Code]))

 

[scottsen]

When I bring it into the pivot, if I use a field setting of average, I get the same value for every course.

Something weird going on here. The formula I gave you should have been used in a calculated FIELD (a measure, not a calculated column). So, it should be brought into the pivot table... without any weird auto-sum/count/avg weirdness.

 

[scottsen]

Try this AVERAGEX(VALUES(Table1[Student ID]),SUM(Table1[calc col - Years to Complete])/COUNTA(Table1[Unit Code]))

I like the idea... in theory. But an average of an average scares me.

 

[jafa1970]

Something weird going on here. The formula I gave you should have been used in a calculated FIELD (a measure, not a calculated column). So, it should be brought into the pivot table... without any weird auto-sum/count/avg weirdness.

Hi, I put the formula in a column in the PowerPivot window, so I guess that is he calculated column you mentioned.

I was under the impression that a calculated field (ie done in the Pivot in Excel itself) is not possible with an OLAP data source eg PowerPivot.

I might be confusing terminology here.

Cheers

 

[jafa1970]

Now I have sorted out where I need to put this formula (apologies for not understanding that), I am seeing the two formulas look like they produce the same result, so progress there.

I suspect I need to filter out the results where the course is not yet complete (my averages are much lower than I was expecting). That would mean filtering out where DWH_Data[YRS_TO_COMPLETE] = 0.

Here is the actual formula used (with table / column info)

=sum(DWH_Data[YRS_TO_COMPLETE])/COUNTROWS(DWH_Data)
=averagex(values(DWH_Data[STUDENT_ID]),sum(DWH_Data[YRS_TO_COMPLETE])/counta(DWH_Data[UNIT_CODE]))

I will have a look at how to do this, but happy if someone has a suggestion on the approach.

Cheers

 

[scottsen]

Calculated Fields (called Measures in 2010) are where the true awesomeness of Power Pivot comes alive!

Outside the power pivot window, on the main excel window... but on the Power Pivot tab, you will find a Calculated Fields drop-button, with "Create New" on there. Put my formula in there, give it a good name (Avg Years to Complete) and OK that dialog. Now that can be dropped in the values portion of a pivot table for dynamic calculation.

 

[scottsen]

Awesome, glad you made progress.

You will need to call the CALCULATE function to do some filtering.

So instead of:
=sum(DWH_Data[YRS_TO_COMPLETE])/COUNTROWS(DWH_Data)
it will be something like:
=CALCULATE( sum(DWH_Data[YRS_TO_COMPLETE])/COUNTROWS(DWH_Data) , DWH_Data[YRS_TO_COMPLETE] > 0)