cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daxwithme
Regular 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.

Highlighted
Daxwithme
Regular 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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors