Re: I need to calculate the percent of a group over the total
Hi guys!!
I need to summarize the percent of a group over the total as shown in red in the example.
Any suggestion? Thanks
There isn’t a way for the built-in summary bars to display those percentages, but it can be done using a linked table and a series of rollups.
First make a [Summary] table containing one record for each group in your main table, plus one record labeled “Total”. I don’t know how you’re grouping your data, so I went with generic names for this example:
Add a link field in the main table that allows you to link to these new records. Link the first record in each group to its appropriate record from the [Summary] table, as well as the “Total” record, then drag-fill from that record across the rest in each group to copy the links.
In the [Summary] table, add a rollup field named {Record Sum} to tally the total of all incoming linked records. This will give you one summary for each group, plus one that’s the total of everything.
Back in your main table, add a {Total} rollup field to bring in the value only from the “Total” record. You’ll use the rollup field conditional settings for this.
Back in the [Summary] table, add one more rollup field named {Percentage}. The aggregation formula will do two things. First it will use some nested functions to bring that total value back across, then divide the individual record summary by that amount to get that group’s percentage of the total (you’ll need to change the field formatting to “Percentage” and adjust the precision to your liking).
It’s not as convenient as having the percentages directly in the main table, but it’s the next best option.
If new records get added to the main table, you’ll need to make sure they have the proper links to the summary table. If an existing record changes groups, you’ll need to manually change the links accordingly. All of this link tweaking could be done through an automation if you’re up for it, but it would require scripting to determine which group record each main record should link to.
