Forum Discussion
Solved
Dax for calculating Average from Max values
Hello everyone, I need a DAX for calculating the Average of certain values of a column, but considering maximum values of another column, and these, in turn, calculated only for certain records. ...
Hi pmaurizi
I would suggest this:
Average of Max Time in GL over Max Grade Level per Employee = VAR EmployeeGradeLevel = INDEX ( 1, SUMMARIZE ( Data, Data[Employee ID], Data[Grade Level] ), ORDERBY ( Data[Grade Level], DESC ), DEFAULT, PARTITIONBY ( Data[Employee ID] ) ) VAR Result = AVERAGEX ( EmployeeGradeLevel, CALCULATE ( MAX ( Data[Time in GL] ) ) ) RETURN Result1. Use INDEX to compute a table containing the combinations of max Grade Level per Employee ID.
2. Then compute average of the max Time in GL for each of combination with AVERAGEX.
PBIX attached.
Does this work for you?
Hi pmaurizi
I would suggest this:
Average of Max Time in GL over Max Grade Level per Employee =
VAR EmployeeGradeLevel =
INDEX (
1,
SUMMARIZE ( Data, Data[Employee ID], Data[Grade Level] ),
ORDERBY ( Data[Grade Level], DESC ),
DEFAULT,
PARTITIONBY ( Data[Employee ID] )
)
VAR Result =
AVERAGEX ( EmployeeGradeLevel, CALCULATE ( MAX ( Data[Time in GL] ) ) )
RETURN
Result
1. Use INDEX to compute a table containing the combinations of max Grade Level per Employee ID.
2. Then compute average of the max Time in GL for each of combination with AVERAGEX.
PBIX attached.
Does this work for you?
You really are a genius!
It worked perfect, it is exactly what I need.
Seeing these solutions makes me realize how much I have yet to learn!!!
Thank you so much for your help!
