cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Daxwithme Frequent Visitor
Frequent Visitor

calculate average for Top 3,5,10

Hi,

 

I'm trying to calculate the average of Top 3,5,10 with the help of a table I created by "Enter Data".
That table I created, operate as a slicer in my report so when I choose 3 it will show me top 3 values.
unfortunately, when I'm trying to calculate the average for the top 3,5,10 its not working since the assistant table is not related to the main table.

 

I succeed to do it for count function, so when I choose 3 for example it will count the top 3 values according to their ranking position.

 

This is how I DAX the count top 3,5,10 with COUNT Function:

 

VAR selectedvalues = SELECTEDVALUE(TopNTable[Top Movies by IMDB])
VAR countrowsmovietitle = COUNT('Movies Details'[Movie Title])
VAR topntable = TOPN('TopN & Ranking Measures'[Ranking Top Movies],TopNTable)
VAR IFs = IF(
selectedvalues = 0
,countrowsmovietitle,
topntable)
return IFs

 

this how my report looks like:

 

forum help.PNG

hope someone can drop some light for me.

 

thanks in advance,

Chen

3 REPLIES 3
Super User III
Super User III

Re: calculate average for Top 3,5,10

Hi @Daxwithme 

 

could you try this piece of DAX:

Average rating =
VAR _topNselected =
    SELECTEDVALUE ( TopNValues[N]; 100 )
RETURN
    AVERAGEX (
        FILTER (
            ADDCOLUMNS (
                movieRatings;
                "rank"; RANKX ( ALL ( movieRatings ); CALCULATE ( SUM ( movieRatings[Rating] ) ) )
            );
            [rank] <= _topNselected
        );
        CALCULATE ( SUM ( movieRatings[Rating] ) )
    )

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Daxwithme Frequent Visitor
Frequent Visitor

Re: calculate average for Top 3,5,10

Dear Sturla,

 

Unfortunately, it didn't work, I'm going to try and play with your piece of DAX little bit more, If I'll get a good result, I will update it here.

 

Thanks you very much for your time trying to help me!!

 

With Best R,

Chen.

Super User III
Super User III

Re: calculate average for Top 3,5,10

I created a very simple test report to check that the measure works: .pbix 

 

Feel free to share your report and I'll take a look at it.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors