Dax for calculating Average from Max values

[pmaurizi]

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!

[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. 

[OwenAuger · Accepted solution]

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? 

[pmaurizi]

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!