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

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.

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

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

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

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

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