Count Top 3 and Rest as Other

[Anonymous]

I have the below table, where I am trying to create visualization where I can get Top 3 Meeting Medium and Rest of the Meeting Medium's as "Other".
So, the top 3 as per the below data should be In Person, Virtual and On Phone while On Site and Null should reflect as Others.

Date	Meeting Medium
9/8/2021	In Person
9/9/2021	Virtual
9/10/2021	Virtual
9/11/2021	On Site
9/12/2021	Virtual
9/13/2021	In Person
9/14/2021	In Person
9/15/2021	On Phone
9/16/2021	On Phone
9/17/2021	Virtual
9/18/2021	On Phone
9/19/2021	On Phone
9/20/2021	Virtual
9/21/2021	Virtual
9/22/2021	Null
9/23/2021	Null

For example: I need something like below:

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I can clearly understand your requirement, I think you can achieve your requirement by creating one single calculated column:

Category =

var _count=SUMMARIZE('Table','Table'[Meeting Medium],"Count",COUNT('Table'[Date]))

var _ranktable=ADDCOLUMNS(_count,"Rank",RANKX(_count,[Count],,DESC,Skip))

var _rank=MAXX(filter(_ranktable,[Meeting Medium]=EARLIER('Table'[Meeting Medium])),[Rank])

return

IF(_rank<=3,[Meeting Medium],"Others")

And you can get what you want, like this:

Then you can create a stacked column chart to place it like this to get the result you wanted:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

@Anonymous 

In addition to the below suggestion, you can add a new column to your table with this code:

New Group = 
VAR _A =
    RANKX (
        'Table',
        CALCULATE (
            COUNT ( 'Table'[Date] ),
            ALLEXCEPT ( 'Table', 'Table'[Meeting Medium] )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( _A < 4, [Meeting Medium], "Other" )

then use the new column in the visual.

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[VahidDM]

Hi @Anonymous 

Try this code to add a new table:

Table 2 = 
VAR _A =
    SUMMARIZE ( 'Table', 'Table'[Meeting Medium], "Count", COUNT ( 'Table'[Date] ) )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS ( _A, "Rank", RANKX ( _A, [Count],, DESC, DENSE ) ),
        "Group", IF ( [Rank] < 4, [Meeting Medium], "Other" )
    )

Output:

Use Group column in the visual.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!