Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
thanks in advance,
Chen
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |