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

[jenmar2023]

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!

[barritown]

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

[jenmar2023]

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?

[barritown]

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

[jenmar2023]

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

[barritown]

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

Please double check with what you do.