Average by column category then sum those values. Please help!

[jordanwebb10]

I need some helpsolving an issue. I have been using Powerpivot to analyze and report on oursport science data. For simplicity sake I have my tables set up in thefollowing fashion. The tables are beingrelated by date table and name table.

Data Table
[TABLE="class: grid, width: 1"]
<tbody>[TR]
[TD] Name [/TD]
[TD] Date[/TD]
[TD] Training Load[/TD]
[TD] Session Name[/TD]
[TD] Session Type[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Date Table
[TABLE="class: grid, width: 1"]
<tbody>[TR]
[TD] Date[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Name Table
[TABLE="class: grid, width: 1"]
<tbody>[TR]
[TD] Name[/TD]
[TD] Info[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Everyday each athlete is assigned data in the [Training Load] Column for different workouts.These workouts are classified in the[session name] column as "Pre Practice Workouts", "PracticeWorkout" and "post practice workouts". The workouts are classified in the [Session type] column as "Full".

Within the same table I am also breaking up "practice workouts" into that practices'individual drills. These drills are being classified in the [Session Name] column by the various "drill names". These are further classified in the [session Type] column as "drill".

What I am reportingto the coach is the following.:

Team averages foreach "Prepractice", "Practice" and "PostPractice" individually. (this is easy just using the SUM function)

I then take those values and manually sumthose three averages to create the "Total Training Load" Metric.

Does anyone know how do it?

Thanks in advance.

 

[jordanwebb10]

https://skydrive.live.com/redir?resid=153A718734D89DD6!5128&authkey=!AMMUMOo5XNLYlhY

I uploaded my workbook to skydrive if that would be of any help solving this problem.

Thanks in advance!

 

[powerpivotpro]

You need to use AVERAGEX in concert with an IF(HASONEVALUE()) or an IF(ISFILTERED()) test. The post linked below takes an AVERAGE() base measure and then forces the totals to be a sum by using SUMX. You will do the reverse - a sum base measure that averages for totals - but the technique is the same:

Subtotals and Grand Totals That Add Up “Correctly” « PowerPivotPro

 

[jordanwebb10]

You need to use AVERAGEX in concert with an IF(HASONEVALUE()) or an IF(ISFILTERED()) test. The post linked below takes an AVERAGE() base measure and then forces the totals to be a sum by using SUMX. You will do the reverse - a sum base measure that averages for totals - but the technique is the same:

Subtotals and Grand Totals That Add Up “Correctly” « PowerPivotPro

I just preordered DAX FORMULAS for Power Pivot: The Excel Pro's Guide to Mastering DAX by Rob Collie

I am hoping this will help grow my knowledge of DAX and power pivot.

I've spent the last couple of days trying to understand the example you gave me to no avail. Although I am starting to get SUMX. Would you be able to give me an example using the tables I included? This just seems to be a little out of my understanding.


Thanks in advance for any help!

 

[powerpivotpro]

Define two measures.

[Load Measure - Base]=
SUM(ZephyrDT[Load])

[Load Measure - Final]=
IF(ISFILTERED(Roster[Name]), [Load Measure - Base], AVERAGEX(VALUES(Roster[Name]), [Load Measure - Base]))

And then you just use the second measure on the pivot. Make sense?

BTW - pre-ordered? The book is already out. Or did you mean the Kindle version? You can get that right now from MrExcel.com if you want (includes a PDF reference card).

 

[Laurent C]

AVERAGEX(VALUES(Roster[Name]), [Load Measure - Base])

would be enough, I guess.