Forum Discussion
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.
I attach a picture with the original table, I also put together an auxiliary calculated table to illustrate how the calculation should be done (the resulting values, and the formulas), and the final result I am looking for.
The report should only show a card with the final Average, it is not necessary to show the table with the calculations.
In the report there are time slicers.
Thank you very much!
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?
3 Replies
- OwenAuger
Super User
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?
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!
- Kedar_Pande
Here’s a DAX measure that should work for your scenario:
Average_Max_Time_in_GL =
VAR MaxGradeLevels =
VALUES('Source Data'[Grade Level])
VAR MaxTimes =
ADDCOLUMNS(
MaxGradeLevels,
"Max Time", CALCULATE(MAX('Source Data'[Time in GL]), 'Source Data'[Grade Level] = EARLIER('Source Data'[Grade Level]))
)
RETURN
AVERAGEX(MaxTimes, [Max Time])Once you create this measure, you can add it to a card visualization in your report to display the average value. The slicers in your report will dynamically filter the results based on user selection.
