cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Top # on a new Table based on Count

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:

 

LanguageCount
English200
Spanish100
Cantonese50
Mandarin50
Japanese40

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Top # on a new Table based on Count

Hi @Shinigami ,

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
)

topn10.PNG

 

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.

View solution in original post

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Top # on a new Table based on Count

Hi @Shinigami ,

I create an example:

topn.PNG

And get the result below using your DAX expression:

topn2.PNG

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" )
)

topn1.PNG

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.

Highlighted
New Member

Re: Top # on a new Table based on Count

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.

 

 

Highlighted
Community Support
Community Support

Re: Top # on a new Table based on Count

Hi @Shinigami ,

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
)

topn10.PNG

 

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.

View solution in original post

Highlighted
New Member

Re: Top # on a new Table based on Count

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.

Highlighted
Community Support
Community Support

Re: Top # on a new Table based on Count

Hi @Shinigami ,

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors