Average of grouped max

[jpereztang]

Hello guys,

I have troubles trying to find way to solve this calculation. I need the average of max delivered days values grouped by num order

I try with this measure

delivered days grouped = SUMX(VALUES(Time[NumOrder])
                    ,MAX(Time[Delivereddays])
)

 and then 

avg days = DIVIDE([delivered days grouped],[Orders],0)

But what i obtain is 11 and i dont know why, please can you help me?

[Anonymous · Accepted solution]

Hi @jpereztang

You can try this:

=
VAR x =
    SUMMARIZE ( 'Time'; 'Time'[Num Order]; "dd"; MAX ( 'Time'[Delivered Days] ) )
RETURN
    AVERAGEX ( x; [dd] )

[AlB · Accepted solution]

Hi @jpereztang

If you just wrap a CALCULATE around the MAX() in your first measure it should work. The current problem is that with no calculate there is no context transition and MAX() always yields 11, the max for the full [Delivered days] column. 

[jpereztang]

@AlB this also works, i couldn't figure out why this measure doesn't work if in the past work for similar scenario and as you say is because i was omitted calculate.    Thank you! 

[Anonymous · Accepted solution]

Hi @jpereztang

You can try this:

=
VAR x =
    SUMMARIZE ( 'Time'; 'Time'[Num Order]; "dd"; MAX ( 'Time'[Delivered Days] ) )
RETURN
    AVERAGEX ( x; [dd] )

[jpereztang]

at first sight it fits perfectly. Thank you.