Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 t...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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