cancel
Showing results for
Did you mean:
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:

hope someone can drop some light for me.

Chen

3 REPLIES 3
Super User III

## Re: calculate average for Top 3,5,10

could you try this piece of DAX:

Average rating =
VAR _topNselected =
SELECTEDVALUE ( TopNValues[N]; 100 )
RETURN
AVERAGEX (
FILTER (
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
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

## 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.

Announcements

#### 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!

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

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
Top Kudoed Authors