topic Top N with DAX in DAX Commands and Tips

Top N with DAX

LuisNC — Mon, 24 Jul 2023 16:52:00 GMT

Hello Dear Community,

I've got a dataset with only one table:

Here's a few row's example:

Every song has a genre and a popularity. I just want to create a visual showing the top 5 genres based on their average popularity.

It shouldn't be too hard to do, but I can't make it 😞

I can make it adding filters in a bar chart, but I'd like to know how to do it with DAX:

Could you please give me a hand?

Thank you!

Re: Top N with DAX

OwenAuger — Tue, 25 Jul 2023 05:19:20 GMT

Hi LuisNC

You can use a pattern like this:

1. First create an Average Popularity measure:

Average Popularity = AVERAGE ( spotify_tracks[popularity] )

2. Next create the measure Average Popularity for Top 5 Genres:

Average Popularity for Top 5 Genres = VAR N = 5 VAR TopGenres = CALCULATETABLE ( TOPN ( N, VALUES ( spotify_tracks[genre] ), [Average Popularity] ), ALLSELECTED () ) RETURN CALCULATE ( [Average Popularity], KEEPFILTERS ( TopGenres ) )

3. Place the measure Average Popularity for Top 5 Genres in the visual with genre on the axis.

Does this work for you?

Regards

Re: Top N with DAX

LuisNC — Thu, 27 Jul 2023 10:48:49 GMT

Thank you OwenAuger , It's correct !!