Calculated Average

[Anonymous]

Hello - 

I'm trying to calcuate a measure where I can see the average number of hours by fee level based on the 'individual'. As an example based on the data below, the average for Person A would be 4 within the 20K fee level. 

		Fee Category
Individual	Name of Project	20000	25000	35000	40000	45000
Person A	Project 1	3
Person A	Project 2					3
Person A	Project 3	5
Person A	Project 4			3
Person A	Project 5			8
Person A	Project 6		3
Person B	Project 1			6
Person B	Project 2			16
Person B	Project 3			7
Person B	Project 4			8
Person C	Project 5		5
Person C	Project 6			6
Person C	Project 7			12
Person C	Project 8				1
Person C	Project 9					13
Person C	Project 10			11
Person C	Project 11			7

[v-shex-msft]

HI @Anonymous,

Please try to use following measure formula if it suitable for your requirement:

AVG Measure =
CALCULATE (
    AVERAGE ( Table[Amount] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Individual] ),
    VALUES ( Table[Project Name] ),
    VALUES ( Table[Category] )
)

Calculate current row content average and grouped by individual, project name, category.

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you, Xiaoxin. This is very close. 

The only issue I'm running into is that the table I shared as an example is a summarized view of time data. Instead, I'm trying to take each of the rows that makeup the time for fee level 20K (3 hours for project A), which is 5 different row items.

Here's an example: 

I have 5 rows of data with each row equalling 3 hours (total of 15 hours). What the formula is doing is taking the average of the total time of those 5 rows (3 hours in this case). Instead, I'm trying to take the 15 hours and divide that by the count of 20K projects (say there were 10 projects...which would be on average 1.5 hours spent per project. 

I assume I need to involved a =COUNTA() function as well into my calculation...but i can't seem to figure the function out. 

Thanks,

M

[v-shex-msft]

Hi @Anonymous ,

Did you means calculate average on total level? If this is a case, I'd like to suggest you add conditions to check current level and write specific formula for total level calculation.

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng