Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

TOPN Function

Hi, 

 

Can you please help me with something?

 

I have a table that includes the following columns:

1) Tickets ID 

2) Created Time 

3) Account

 

I need to calculate the top 10 customers who raise tickets. 

 

Since they are all in one table - I just tried to use the Top10 feature from the visuals pane filters and it did not work - its showing more than 15 accounts. I tried using the TOPN function - it says "Multiple columns cannot be converted to a scalar value."

 

(i.e) TOP10Accounts = TOPN(10, 'All Tickets', 'All Tickets'[Account],ASC)

 

Can you please help?

Need Help
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Create two measures like and check

    CntAccounts = count('All Tickets'[Account])

     

    Top 10 City Rank = CALCULATE([CntAccounts],TOPN(10,all('All Tickets'[Account]),[CntAccounts],DESC),VALUES('All Tickets'[Account]))

     

    refer to my video : https://www.youtube.com/watch?v=QIVEFp-QiOk

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Create two measures like and check

    CntAccounts = count('All Tickets'[Account])

     

    Top 10 City Rank = CALCULATE([CntAccounts],TOPN(10,all('All Tickets'[Account]),[CntAccounts],DESC),VALUES('All Tickets'[Account]))

     

    refer to my video : https://www.youtube.com/watch?v=QIVEFp-QiOk

    • CupidC's avatar
      CupidC
      Helper II
      4 years ago

      Hi Amitchandak

      Thank you for the video, any chance that you can explain what "VALUES('All Tickets'[Account])" does at the end. I saw your reply in youtube comments, but still don't get it. Any chance you could elebrate more?

      Thanks

      Charles

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak  Thank you so much. This worked like a charm. I will try it on my other visuals too to see if it works on all scenarios. The video is very good too!