Calc average at higher level of granularity

jafa1970

New Member
Joined
Feb 17, 2011
Messages
38
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
Any chance this easy dude works for you?

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

jafa1970

New Member
Joined
Feb 17, 2011
Messages
38
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

Board Regular
Joined
Aug 17, 2012
Messages
65
Try this AVERAGEX(VALUES(Table1[Student ID]),SUM(Table1[calc col - Years to Complete])/COUNTA(Table1[Unit Code]))
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263

ADVERTISEMENT

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

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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

New Member
Joined
Feb 17, 2011
Messages
38

ADVERTISEMENT

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

New Member
Joined
Feb 17, 2011
Messages
38
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)

Code:
=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

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,187
Members
409,749
Latest member
BorisYeltsin

This Week's Hot Topics

Top