Forum Discussion

pmaurizi's avatar
pmaurizi
Frequent Visitor
1 year ago
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. ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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? 

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

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.