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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.