Percentage of Total - Grouped Table View

[E_Browning]

I am having trouble figuring out how to show a percentage of total in a grouped table as seen in the screenshot below. I can see the correct percentage when looking at an individual row, but as soon as it summarizes, it is not calculating as I would expect. For example, on Monday, I should see 17.2% (149/(719+149))

This is my current formula that is not working: thisRow.[# of Guests]/sum(thisRow.[Total Members],thisRow.[# of Guests])

Still having trouble wrapping my head around how coda works, so any guidance on how to accomplish this would be great.

[Christiaan_Huizer]

hi @E_Browning

thanks for posting the question, what is indeed very simple in any spreadsheet maybe a bit harder to see at first in Coda, but once you see it, it cannot be unseen anymore and it is much more powerful and robust set up.

It all starts with a proper data architecture. the image below shows that what is perfect for any spreadsheet does not work so well in Coda

once you have your table set-up, we apply the function you described

and the name you get via a Format()

Hope it helps, cheers, Christiaan

[Dale_Cowling]

Hi @E_Browning,

I don’t think what you are asking for is possible without Coda stepping in with development work. In my own testing I couldn’t do it.

I would love to have a Formula option that can reference the summary of other columns so it live update as you filter/group.

I would recommend submitting it as a feature request.

Have you considered having a summary table? Essentially a replica of the groupings you have now.

Obviously not ideal since you can’t expand the data but it will at least get the figures you are after.

A demo if this is in the embed below.

Another idea is to add a column to the table that runs this calculation per row and then use Min or Mode as summarize but the grand total will be wrong.

If anyone else wants to try here is a quick replica of the data as a playable doc.

Use this link to open the embed as a new tab with play mode enabled: https://coda.io/embed/RusilV7tWm/_suEtV?viewMode=embedplay

All the best

Dale

[E_Browning]

Thank you @Christiaan_Huizer, the source data is actually a google sheet and I cannot change the structure of that document due to other processes related to it. Thank you though

[Christiaan_Huizer]

thanks for the feedback @E_Browning ,
you I guess you need to transpose your data to make it easier, but I guess that setting up a transpose logic is not what you are looking for. May I be mistaken, this might give you some inspiration:

Coda Packs and Using Column Names

[E_Browning]

Thank you @Dale_Cowling, I have not tried a summary table and this will work for my needs!

Thank you

[E_Browning]

Thank you Christian for providing this post. I will review this in more detail. Right now, i have a workaround, but it does have limitations. Perhaps this blog post will help me remove those limits.

I appreciate your help!