Forum Discussion

glynwilks's avatar
glynwilks
Helper I
3 years ago
Solved

Count Top N

Hello, I  am using dyamic text to display the following.   The below also shows that <Top Agent> has received the most positive responses from end users with a total of <Total Positive Responses> p...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi glynwilks 
    To achieve a goal I created 3 measures:

    1. Counter of positive responses :

    PositiveCount =
    CALCULATE(
        COUNTROWS('table'),
        'Table'[Rating Name] = "Positive"
    )
    2. Top 1 for aggent :
    Top agent =
    TOPN(1,
        ALL('table'[Agent]),
        [PositiveCount],
        DESC)
    3. "Filtered" counter that you can put on your text:
    Top1PositiveCount =
    CALCULATE(
        [PositiveCount],  -- Your previously created PositiveCount measure
        TOPN(1, ALL('table'[Agent]), [PositiveCount], DESC)
    )
    + Text measure :
    Top agent text = "Top agent is " & [Top agent] & "He has " & [Top1PositiveCount] & " positive responses"
    Result :
glynwilks's avatar
glynwilks
Helper I
3 years ago

Thanks and sorry about that.

 

So basically this is my first calculation to calculcate which one of my technicians has the most reviews from clients.

Top Rated Agent = TOPN(1,ALLNOBLANKROW(Smileback[ticket.agents]),[Total SmileBack Reviews])
 
I then want to create a measure that will count the number of reviews and filter by the top rated agent. So, when I use the dynamic text, I can say the top rated agent is <Agent Name> and they had <Number> of reviews.
  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    As I mentioned please share some data tables to work with.

    • glynwilks's avatar
      glynwilks
      Helper I
      3 years ago

      Sorry, I must need a coffee.

       

      Thanks

       

       

      idRating NameAgentRated On
      26332648PositiveAgent One2023-06-30T01:05:15.2682580+00:00
      25456296PositiveAgent One2023-06-23T05:59:16.9924060+00:00
      26148912PositiveAgent One2023-06-20T08:20:03.4610000+00:00
      26149716PositiveAgent One2023-06-20T07:12:43.4991100+00:00
      26149502PositiveAgent One2023-06-20T06:43:15.5154700+00:00
      26148655PositiveAgent One2023-06-20T05:11:27.2444930+00:00
      26148858PositiveAgent One2023-06-20T04:51:48.5108660+00:00
      26146811PositiveAgent One2023-06-20T03:57:17.1584720+00:00
      26147099PositiveAgent One2023-06-20T01:47:16.8998920+00:00
      26146763PositiveAgent One2023-06-20T01:39:34.0617300+00:00
      25997867PositiveAgent One2023-06-12T01:24:25.7117440+00:00
      25681610PositiveAgent One2023-05-30T10:11:15.3619750+00:00
      24740357PositiveAgent One2023-03-29T09:48:55.7645930+00:00
      24428143PositiveAgent One2023-03-10T10:00:35.1060530+00:00
      22130090NegativeAgent One2022-10-25T09:33:11.2421290+00:00
      26957369PositiveAgent Two2023-08-07T22:59:23.3517300+00:00
      26629000PositiveAgent Two2023-07-19T11:32:12.9528910+00:00
      26530890PositiveAgent Two2023-07-13T02:46:36.9067660+00:00
      26507514PositiveAgent Two2023-07-12T01:53:26.8083000+00:00
      26461370PositiveAgent Two2023-07-10T07:33:32.1791590+00:00
      26412472PositiveAgent Two2023-07-06T06:27:06.7066960+00:00
      26410265PositiveAgent Two2023-07-06T04:20:43.3175250+00:00
      26267062PositiveAgent Two2023-06-27T07:10:36.7741500+00:00
      25974848PositiveAgent Three2023-06-09T07:36:18.9444670+00:00
      25928738PositiveAgent Three2023-06-07T07:53:37.4747550+00:00
      25903948PositiveAgent Three2023-06-06T03:37:10.6948620+00:00
      25853408PositiveAgent Three2023-06-02T02:27:40.7135590+00:00
      25832974PositiveAgent Three2023-06-01T08:12:49.1531310+00:00
      25832609PositiveAgent Three2023-06-01T06:04:39.2809710+00:00
      25774665PositiveAgent Three2023-05-29T01:18:47.5430780+00:00
      25706376PositiveAgent Three2023-05-24T07:11:03.4745760+00:00
      25704685PositiveAgent Three2023-05-24T03:40:04.8397400+00:00
      25555807PositiveAgent Three2023-05-16T02:18:08.7313890+00:00
      25403507PositiveAgent Three2023-05-08T08:05:36.9605270+00:00
      25374502PositiveAgent Three2023-05-05T02:41:41.8482250+00:00
      25374527PositiveAgent Three2023-05-05T02:39:32.1282340+00:00
      25324723PositiveAgent Four2023-05-03T00:13:52.6724020+00:00
      25163034PositiveAgent Four2023-04-24T08:47:21.1982940+00:00
      25163638PositiveAgent Four2023-04-24T07:56:07.9047550+00:00
      25114723PositiveAgent Four2023-04-20T08:01:21.9467010+00:00
      25113844PositiveAgent Four2023-04-20T03:31:06.1003440+00:00
      25042541PositiveAgent Four2023-04-17T03:28:27.4758550+00:00
      • Ritaf1983's avatar
        Ritaf1983
        Super User
        3 years ago

        Hi glynwilks 
        To achieve a goal I created 3 measures:

        1. Counter of positive responses :

        PositiveCount =
        CALCULATE(
            COUNTROWS('table'),
            'Table'[Rating Name] = "Positive"
        )
        2. Top 1 for aggent :
        Top agent =
        TOPN(1,
            ALL('table'[Agent]),
            [PositiveCount],
            DESC)
        3. "Filtered" counter that you can put on your text:
        Top1PositiveCount =
        CALCULATE(
            [PositiveCount],  -- Your previously created PositiveCount measure
            TOPN(1, ALL('table'[Agent]), [PositiveCount], DESC)
        )
        + Text measure :
        Top agent text = "Top agent is " & [Top agent] & "He has " & [Top1PositiveCount] & " positive responses"
        Result :