Rank count of Appt Types w/ rank 11 and above listed as Other

[willz06jw]

Hi and thanks for reading,

I am trying to figure out how to:

1.  Rank a count of Appointment Types

2.  Create a bar graph that lists the top 10, then count up all of the rest and list as OTHER.

I am stuck at part 1.  I am trying to use RANKX and calculate COUNTX, but I am having a difficult time with the code (since I am a bit of a DAX novice).

Here is what I am using -- it does not work

Rank = RANKX(appointments,calculate(COUNTx(Appointments,Appointments[Appt Type]),filter(Appointments,Appointments[Appt Type]=EARLIER(Appointments[Appt Type]))),,DESC,Dense)

Thank you for your help,

Will

[v-yulgu-msft · Accepted solution]

Hi @willz06jw,

Not very sure how you want to show "count up all of the rest and list as OTHER". Based on my assumption, I considered below two scenarios. In my test, the visual shows Top 3 records.

1. Create Rank via measure. And show "OTHER" in a card visual.

Rank =
RANKX (
    ALL ( Appointments[Appt Type] ),
    CALCULATE (
        COUNTROWS ( Appointments ),
        ALLEXCEPT ( Appointments, Appointments[Appt Type] )
    ),
    ,
    DESC,
    DENSE
)


Other = CALCULATE(COUNTROWS(Appointments),FILTER(Appointments,[Rank]>3))

2. Show "OTHER" in bar chart as well as the Top10 list.

If that is the case, you need to create some extra calculated tables.

New first calculated table and add a calculated column.

Appointments2 =
ADDCOLUMNS (
    SUMMARIZE (
        Appointments,
        Appointments[Appt Type],
        "count of appointment", COUNT ( Appointments[Appt Type] )
    ),
    "Rank", RANKX (
        Appointments,
        CALCULATE (
            COUNT ( Appointments[Appt Type] ),
            ALLEXCEPT ( Appointments, Appointments[Appt Type] )
        ),
        ,
        DESC,
        DENSE
    )
)

Sum =
IF (
    Appointments2[Rank] > 3,
    CALCULATE (
        SUM ( Appointments2[count of appointment] ),
        FILTER ( Appointments2, Appointments2[Rank] > 3 )
    ),
    CALCULATE (
        SUM ( Appointments2[count of appointment] ),
        ALLEXCEPT ( Appointments2, Appointments2[Appt Type] )
    )
)

New a second calculated table. Drag fields from this table to bar chart.

Appointments3 =
UNION (
    SELECTCOLUMNS (
        FILTER ( Appointments2, Appointments2[Rank] <= 3 ),
        "Appt Type", Appointments2[Appt Type],
        "Count appt", Appointments2[Sum]
    ),
    SELECTCOLUMNS (
        FILTER ( Appointments2, Appointments2[Rank] = MAX ( Appointments2[Rank] ) ),
        "Appt Type", "Other",
        "Count appt", Appointments2[Sum]
    )
)

Best regards,

Yuliana Gu