Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nqahman
Regular Visitor

The average of top 2 for grouped data

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?

 

Capture.PNG

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

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] ) )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

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] ) )
)

Regards
Zubair

Please try my custom visuals

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.

@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] )

 


Regards
Zubair

Please try my custom visuals

@nqahman

 

Please see the attached file here

 

(With your sample data)


Regards
Zubair

Please try my custom visuals

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

@nqahman

 

1049.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.