Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Average of Top N Values based on Ranking Index

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:

IDRanked IndexDeparture 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.

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , 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's avatar
    v-luwang-msft
    5 years ago

    Hi Anonymous ,

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , 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's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

    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