Sort on Summarized field in a report

Hello, I have a spreadsheet with a bunch of columns. I created a report that groups the data by "Client Servicer" where "Submitted" equals N and "Expiration Date" is in the past. The report also Summarizes the data by summing up the "Annual Premium" amount for each Client Servicer.

There are over 100 different Client Servicers who meet the above criteria in the report. I was hoping to be able to sort on the summed up Annual Premium amount (is this possible?) and then pull onto a dashboard, the top 20 Client Servicers with the largest summed up Annual Premium amounts. Is this possible? In the report, I sorted by Annual Premium but it sorts the individual rows for each individual Client Servicer and doesn't actually sort the report by the summed up Annual Premium amount for the Client Servicers.

Thanks in advance for your help!!

Answers

  • Hi @K Miller

    There currently isn't a way to Sort Reports by the values created in the Summary rows. The Sort will apply to the source data, but Grouping will automatically sort values alphabetically in their groups. Please provide your feedback to the Product team through this form, here!

    What I would do in this instance is create a metric sheet and use Formulas to output your calculations.

    In order to build the formula you'd need to know all the different Client Servicer names so we can search for them in your source sheet. A SUMIFS Function can Sum together values based on criteria in other columns.

    Then you can have a helper column in this metric sheet that Ranks the values you've brought in, and a Report can filter on if that Rank column is up to 20 (to be the source for your Chart). Does that make sense?

    Let me know if you need help with the formulas! If so, it would be useful to see a screen capture of your source sheet but please block out sensitive data.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now