The average of top 2 for grouped data

[nqahman]

Hi everyone,

I would like to calculate the average value of top two values for every section per year&month,

I have tried ranking them first, but I couldn't have a correct ranking.

Do you guys have an idea of how to do it?

[Zubair_Muhammad · Accepted solution]

Hi @nqahman

Try this MEASURE

Measure =
AVERAGEX (
    TOPN (
        2,
        FILTER (
            ALL ( TableName ),
            TableName[YEAR] = SELECTEDVALUE ( TableName[YEAR] )
                && TableName[MONTH] = SELECTEDVALUE ( TableName[MONTH] )
                && TableName[Section] = SELECTEDVALUE ( TableName[Section] )
        ),
        TableName[VALUE], DESC
    ),
    CALCULATE ( SUM ( TableName[VALUE] ) )
)

[Zubair_Muhammad · Accepted solution]

Hi @nqahman

Try this MEASURE

Measure =
AVERAGEX (
    TOPN (
        2,
        FILTER (
            ALL ( TableName ),
            TableName[YEAR] = SELECTEDVALUE ( TableName[YEAR] )
                && TableName[MONTH] = SELECTEDVALUE ( TableName[MONTH] )
                && TableName[Section] = SELECTEDVALUE ( TableName[Section] )
        ),
        TableName[VALUE], DESC
    ),
    CALCULATE ( SUM ( TableName[VALUE] ) )
)

[nqahman]

Thank you @Zubair_Muhammad for your response,

An error exists because of too many arguments in the FILTER function.

The maximum number is 2 arguments: Table name and one expression.

[Zubair_Muhammad]

@nqahman

I have only 2 arguments in above formula for FILTER

1)

ALL ( TableName )

2) 

 TableName[YEAR] = SELECTEDVALUE ( TableName[YEAR] )
                && TableName[MONTH] = SELECTEDVALUE ( TableName[MONTH] )
                && TableName[Section] = SELECTEDVALUE ( TableName[Section] )

[Zubair_Muhammad · Accepted solution]

@nqahman

Please see the attached file here

(With your sample data)

[nqahman]

Thank you so much @Zubair_Muhammad. Finally, it's working.
Appreciated.

[Zubair_Muhammad]

@nqahman