Average of Top N Values based on Ranking Index

[jbeckham812]

Hi All,

I need a measure that calculates the average of the top 5 values in the "Departure to Received" column based on rank. Table below:

ID	Ranked Index	Departure To Received
NH085	500	55
NH084	499	60
NH092	498	85
NH081	497	74
NH086	496	51
NH088	495	56
NH090	494	62

So in the table above, the average computed  would be 65 ((55+60+85+74+51)/5). Im a complete newbie to DAX, figured making the ranked table descending would help if TOPN was needed to be used in the measure, but could make the ranking ascending if that would make the solution easier.

[amitchandak · Accepted solution]

@jbeckham812 , if Ranked Index is column, Create a new measure like

measure =
var _max = maxx(allselected(Table), Table[Ranked Index])
return
calculate(sum(Table[Departure To Received]), filter(Table, Table[Index] >= _max -5 && Table[Index] <=_max))

[v-luwang-msft · Accepted solution]

Hi @jbeckham812 ,

Whether you are creating a column, or a measure, you can use the following dax:

test = 
VAR test1 =
    MAX ( 'Table'[Ranked Index] )
VAR test2 =
    CALCULATE (
        AVERAGE ( 'Table'[Departure To Received] ),
        FILTER (
            'Table',
            'Table'[Ranked Index] <= test1
                && 'Table'[Ranked Index] >test1 - 5
        )
    )
RETURN
    test2

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Best Regards

Lucien

[amitchandak · Accepted solution]

@jbeckham812 , if Ranked Index is column, Create a new measure like

measure =
var _max = maxx(allselected(Table), Table[Ranked Index])
return
calculate(sum(Table[Departure To Received]), filter(Table, Table[Index] >= _max -5 && Table[Index] <=_max))