Count Top N

[glynwilks]

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> positive responses.

I can calculate top 1 agent with most positive feedback as 

Top Rated Agent = TOPN(1,ALLNOBLANKROW(Smileback[ticket.agents]),[Total SmileBack Reviews]) and this works. But for the second part, I need to calculate the total number of positive responses for the returned agent. How can I calculate the number of Top N results as per above?

[Ritaf1983]

Hi @glynwilks 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[glynwilks]

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]

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

[glynwilks]

Sorry, I must need a coffee.

Thanks

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

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[Ritaf1983 · Accepted solution]

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 :

Link to a sample file 

[glynwilks]

Amazing, thank you very much for this

[Ritaf1983]

Glad to help 🙂
Please consider Accepting it as the solution to help the other members find it more quickly