Top # on a new Table based on Count

[Anonymous]

I searched but I couldnt find an answer, new to Power BI of course. Any help is highly appreciated.

I have a table with calls based on languages. I am creating a new table with total count like this:

Lan Count = ADDCOLUMNS(
SUMMARIZE(Calls, Calls[Language]),
"Count", CALCULATE(COUNTA(Calls[Language]), Calls[Checked]=TRUE))

I trying to get the top 5 based on Count, it can be on this table or a new one. If two languages have the same count, they cant have the same rank. I currently get all languages with all counts, I tried doing a new table with TOPN but it returned all languages with a number, not just 5. I have seens some answers using RANKX, but for some reason I cant get RANKX to work, it asks for more parameters than the examples I've seen. The result I'm looking for is only top 5 on a table (has to be a table, not a visualization) like this:

Language	Count
English	200
Spanish	100
Cantonese	50
Mandarin	50
Japanese	40

If your answer/solution contains more columns that is also fine, as long as it only returns top N with the count and not more.

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

Lan Count = 
TOPN (
    10,
    ADDCOLUMNS (
        SUMMARIZE ( Calls, Calls[Language] ),
        "Count", CALCULATE ( COUNTA ( Calls[Language] ) + 0, Calls[Checked] = "True" )
    ),
    CALCULATE ( COUNTA ( Calls[Language] ) + 0, Calls[Checked] = "True" )
        + RANKX ( Calls, Calls[Language],, DESC, DENSE ) / 10000
)

For TOPN, we should pay attention to this:

If there is a tie, in order_by values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.

Best Regards,

Icey

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

[Anonymous]

I'm trying to understand how the answer works. The + 0 should have no usage, the count is already an integer/long, adding a + 0 should not make any difference. Adding the count with the rank, should also not make a difference, the count of two duplicates would have the same Rank, therefore adding the rank and dividing by 1000 should net the same result. Would love an explanation if you have the time. Thank You.

[Icey]

Hi @Anonymous ,

1. Yes, +0 can be deleted in your scenario.

2. The expression below is used to rank [Language] column from "Z" to "A". Then divide 10000. This is work when the count results are duplicate.

RANKX ( Calls, Calls[Language],, DESC, DENSE )

Best Regards,

Icey

[Icey]

Hi @Anonymous ,

I create an example:

And get the result below using your DAX expression:

Then create another table using the DAX expression below and get the result you want.

Lan Count = 
TOPN (
    5,
    ADDCOLUMNS (
        SUMMARIZE ( Calls, Calls[Language] ),
        "Count", CALCULATE ( COUNTA ( Calls[Language] ), Calls[Checked] = "True" )
    ),
    CALCULATE ( COUNTA ( Calls[Language] ), Calls[Checked] = "True" )
)

Can this meet your requirements?

Best Regards,

Icey

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

[Anonymous]

It works if I do Top 5, even if the count repeats, I only get 5, if I try to do a Top 10, it returns more than 10, I have no idea why.

Edit: If I do a top 15 it works, I am more confused now, only if I try to do anything between Top 7 and Top 12 it returns more, but for top 5 and top 15 it only returns 5 or 15, even if there are duplicate Counts.