topic Re: How to display Top 10 by month in a table / matrix in DAX Commands and Tips

How to display Top 10 by month in a table / matrix

jenmar2023 — Wed, 23 Aug 2023 17:16:23 GMT

I have a table called Forms. Within that table, I have a field called forms, Total and YearMonth.

Forms	Total	YearMonth
A	10	202305
B	20	202305
C	25	202305
B	20	202304
A	45	202304
C	5	202304

I would like my end result to be a table or some visualization where the user can see The top x forms by month.

For the month of April 202304 the top 3 forms were

A, B, then C

For the month of May 202305 the top 3 forms were

C, B, then A

How can I go about creating doing this in PowerBI? Thank you!

Re: How to display Top 10 by month in a table / matrix

barritown — Thu, 24 Aug 2023 13:28:24 GMT

Hi jenmar2023,

You don't need any DAX here.

All you need is:

1) Add a slicer.

2) Add a table visual.

3) Open the Filters pane and use the Top filter there for the [Total] column.

4) Enable sorting by [Total].

Hope this helps.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: How to display Top 10 by month in a table / matrix

jenmar2023 — Thu, 24 Aug 2023 13:36:28 GMT

The user only sees it one month at a time though? They have to select it each month. Is there a way to show it in a table view all at once?

Re: How to display Top 10 by month in a table / matrix

barritown — Thu, 24 Aug 2023 14:48:53 GMT

Okay, in this scenario you'll need some DAX.

You could create a calculated column like the one below.

In plain text:

Top3 Flag = VAR CurrentMonth = [YearMonth] VAR Top3 = CONCATENATEX ( TOPN ( 3, FILTER ( Data, [YearMonth] = CurrentMonth ), [Total] ), [Forms], "," ) RETURN CONTAINSSTRING ( Top3, [Forms] )

Once you have it, you should set it to True in the filter pane and sort your table by 2 columns holding the Shift key. Like on the screenshot below:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: How to display Top 10 by month in a table / matrix

jenmar2023 — Thu, 24 Aug 2023 16:19:49 GMT

Didn't work. Can't display the visual when I add Top3 flag

Re: How to display Top 10 by month in a table / matrix

barritown — Thu, 24 Aug 2023 19:20:35 GMT

Here's the file with the mock data where this solution works - https://www.dropbox.com/scl/fi/bemnu8v7a4ahcbpczew60/community-multiple-topx.pbix?rlkey=5ty9flldoh8tlda8zrr3bs550&dl=0

Please double check with what you do.