cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonysommer
Helper II
Helper II

Pie chart to show top 10 users who submitted the most tickets

I have a pie chart that is using a collection that has a column with names as text (collection is called TextNames with the field of CreatedByInText as the text field with names of users) instead of trying to build the formula using the original people picker field. The formula is:

AddColumns(
    GroupBy(
        Filter(
            TextNames,
            CreatedByInText <> "name i do not want to include in chart" && CreatedByInText <> "another name i do not want to include in chart"
        ),
        "CreatedByInText",
        "Count"
    ),
    "NumberOfTickets",
    CountRows(Count)
)

This displays every user that ever submitted a ticket. Can anybody help with the formula so that it only shows the top 10 users with the most amount of tickets submitted? I am not sure how to implement that code.

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @tonysommer ,

Do you want to show top 10 users with the most amount of tickets submitted in your Pie Chart?

 

Based on the needs that you mentioned, I think the SortByColumns function and FirstN function could achieve your needs. Please modify your formula as below (set the Items property of the Pie Chart control) :

FirstN(
  SortByColumns(
    AddColumns(
      GroupBy(
        Filter(
            TextNames,
            CreatedByInText <> "name i do not want to include in chart" && CreatedByInText <> "another name i do not want to include in chart"
        ),
        "CreatedByInText",
        "Count"
      ),
      "NumberOfTickets",
       CountRows(Count)
   ),
  "NumberOfTickets",
   SortOrder.Descending
  ),
  10
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
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

3 REPLIES 3
eka24
Super User
Super User

Try

Filter(AddColumns( GroupBy( TextNames, "CreatedByInText", "Count" ), "NumberOfTickets", CountRows(Count) ),
CreatedByInText <> "name i do not want to include in chart" && CreatedByInText <> "another name i do not want to include in chart"&&NumberOfTickets>10)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

v-xida-msft
Community Support
Community Support

Hi @tonysommer ,

Do you want to show top 10 users with the most amount of tickets submitted in your Pie Chart?

 

Based on the needs that you mentioned, I think the SortByColumns function and FirstN function could achieve your needs. Please modify your formula as below (set the Items property of the Pie Chart control) :

FirstN(
  SortByColumns(
    AddColumns(
      GroupBy(
        Filter(
            TextNames,
            CreatedByInText <> "name i do not want to include in chart" && CreatedByInText <> "another name i do not want to include in chart"
        ),
        "CreatedByInText",
        "Count"
      ),
      "NumberOfTickets",
       CountRows(Count)
   ),
  "NumberOfTickets",
   SortOrder.Descending
  ),
  10
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
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

Perfect, thank you everybody!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,049)