To help you answer questions about mix, contribution, and total ratios, we have included the keywords “of” and “percentage of.” These keywords allow you to explore insights like the share of a specific store's sales compared to an entire region. Previously, these types of searches required complex group aggregate formulas, but these keywords make the process much more accessible.
To get the most out of these keywords, it is helpful to understand the results they provide. For example, searching gifts per payment method returns the revenue for that specific payment method. If you search Amount percentage of payment method , the system returns the amount as a percentage of each payment method.
You can also adapt these keywords for various business needs by using the following options:
Specify aggregation type, for example,
average amount of regionSpecify multiple columns, for example,
average amount of region and stateSpecify the total ratio, for example,
amount percentage of all
“Of” keyword example
You might need to know the amount for each giver's state. To find this, you would search for amount by giver state, which shows total gifts at the state level. To see the revenue of each specific state, you can add the filter amount of giver state to your search.
The “of” keyword automatically generates a group aggregate formula to answer this. In this example, the formula used is group_aggregate (sum (gifts), (giver state), query_filters()). Your results will display the sum of revenue at the customer level and will include any other filters you have active in the Chart Builder.
“Percentage of” keyword example
If you want to understand each country's percentage contribution to the revenue of a customer region, you can enter the following filters into your search: Revenue by givier Region and amount percentage of giver Region. Using the percentage keyword creates this formula: sum (amount)/ group_aggregate (sum (amount), (giver Region), query_filters()).
To see your results formatted as a percentage, please follow these steps:
Click the more options menu icon (Three dots button) on the right side of the column header for “Total amount % of Region” and select Number format.
In the menu that appears on the right side of the table, select Number under Category and click Percentage. The results appear as a percentage of the total amount at the region level.
If you search for amount percentage of all, your search will show the percentage contribution of each region compared to the total amount. This creates the following formula: sum (amount) / group_aggregate (sum (amount), {}, query_filters()).
“Average __ of” example
Suppose you want to find the average revenue for each givers nation and region. You would search for average amount , Nation, and average amount of Region. This query uses the following formula: group_aggregate (average (amount), (Region), query_filters()).
Multiple columns example
Just as you can define multiple grouping columns with a group function, you can use the and keyword to group multiple columns in the Chart Builder. If you want to see amount at the city level alongside the amount for both the region and nation, you can search for amount, City, and amount of Region and Nation.
This search returns a table showing total revenue at both the region and nation levels. The query generates this formula: group_aggregate (sum (amount), (Region, Nation), query_filters()).