In Explore, you can find the total of your overall results or subtotals for individual categories in a report. Each total can be calculated with a unique aggregator (see Changing metric aggregators).
You can configure totals in two different ways in Explore:
- Simple totals let you add a row of grand totals and a row of subtotals, using any of Explore's aggregators. Grand totals will calculate the overall total of your results, using your chosen aggregator. Subtotals can be used if you have more than one attribute added to your report.
- Advanced totals, usable only with the Table visualization option, let you to add multiple rows of totals, each with a different aggregator. Grand totals will be applied only to the first attribute on the selected location. Subtotals are available only for the first attribute on rows. If you have attributes on columns, subtotals will not be an available action.
Note: Applying a metric filter to remove a value from being displayed in
the report doesn't exclude those values from the calculated total.
To add simple totals to your report
- In the report builder, click the result manipulation icon (
). - Select the Totals option.
- Underneath Grand totals on, check the location of the attributes for which you want to apply grand totals. You can select Columns, Rows, or both.
- Select an aggregator.
- Underneath Subtotals on, check the location of the attributes where you would like to apply subtotals. You can select Columns, Rows, or both.
- Select an aggregator.
- When you are finished, click Apply.
To add advanced totals to your report
- In the report builder, click the result manipulation icon ().
- Select the Totals option.
- Click the Advanced tab (this is only visible if your visualization type is Table).
- Click + Add a new total.
- Underneath Attribute select the location of the attribute you want to apply your
total to. You can calculate grand totals on either Columns or Rows, and
subtotals on an individual attribute. Note: You can only calculate subtotals on the first attribute listed in Rows. If you have an attribute in Columns instead, subtotals will not be an available option.
- Select an aggregator underneath Aggregators.
- Click + Add a new total to add any additional totals. If you want to delete a
total, you can click the trash can icon next to the total.
- Click Apply.
Below is an example of a report calculating the total average number of tickets and total summation number of tickets by month
14 Comments
We have added Sum Totals on rows and columns and then sorted the rows to be in ascending order which changes where the sum total is located. Is there a way to exclude the total from being sorted? Or a way to pin it?
Hi Sydney,
I created a sample query in my test account and I did see that the Sum Totals stay in the same position. I have created a ticket on your behalf so we can look into your query further.
Hey @... -
Sorry for the delay here. You can use the advanced sort feature under results manipulation to keep that sum in place!
Not sure how possible this is, but I'm trying to show the average of two totals. Is there a way to have this calculated and show using the KPI visualization?
Hi @...,
There's a couple ways you can do this, but the easiest might be custom metrics... something along the lines of:
MyCustomMetric:
IF ([CUSTOM_FIELD_NAME] = "CES") THEN
[Ticket ID]
ENDIF
MyCalculation
SUM(MyCustomMetric)/DCOUNT_VALUES([Update ID])
You could also do a Result Path Calculation and then hide part of your result to get it back into KPI format.
Hope this points you in the right direction!
Brandon
Hey @... how would I do the following (relatively simple I guess, but am struggling!)
I have a custom metric that calculates handling time - what I want is to show that figure as a percentage of total possible hrs worked (let's say 354). How do I do this?
Cheers for any pointers!
Matt
Hey @... -
Nice to hear from you! In this case, you'd use a second custom metric that is just
[Update Handing Time (Hrs)] / 354
Then, use the paintbrush to change your display format to a %.
Hope this helps!
Brandon
@... appreciate the help here!
I had problems at first, but then changed the formatting slightly:
SUM(Update handling time (hrs)) / 354
Cheers sir
Hi Zendesk - Having an issue where "simple" sum totals aren't showing for all rows. In the screen below, it appears only "Tickets reopened" is showing under Ticket totals for each row(s). Any suggestions? Thanks!
I'm not entirely sure how the "Ticket totals" row is being calculated in your screenshot - I presume this is a result path calculation, which might be why it's not working correctly as a total/sum aggregator for the metrics in your query.
At any rate, you can only calculate subtotals using the native "simply" sum total feature if you have multiple attributes in use in your query. I have attached a screenshot demonstrating how this could work with a similar example to the one you're working on.
I hope this helps clarify!
Thanks,
Dylan
Thank you Dylan Tragjasi for the additional information. Below is the query I based the previous one off of, only using "Tickets created" rather than "Tickets updated w/ comment". In this case, the rows are totaling correctly. Is there something I need to do in the result path calculation you mentioned to make "Tickets updated w/ comment" behave in the same manner?
Thanks,
Dylan
Hi all,
I'm adding grand totals on rows but the aggregator should use AVG instead of SUM. When I do this, there's no way to keep the aggregated numbers pinned to the top or bottom of the table. In other words, with SUM, the value of the grand total row will always be highest so it's easy to sort high to low, but an average would fall somewhere in the middle obviously.
So, is there any way to keep an AVG row at top or bottom? It seems a bit unlikely that a user would want it anywhere other than top or bottom, frankly.
If you're manually sorting the values by clicking on the metric column header, then the rows containing the totals (SUM/AVG) will also be included. You might want to sort via Result manipulation → Sorts if you need want the keep the totals in their original position/row. Here's an article about Sorting results.
Please sign in to leave a comment.