How can I transpose column averages into rows of another table?

I have a table with columns A, B , C and the data in these columns is averaged at the bottom of each column.

In other table, I make rows and enter A, B, C as the first entries on the rows.

My question is how can I get the average from the bottom of each column in table 1 to display in each row of table 2?

If I do the following, I get the same value in each row: [Monthly Sales Units].[Item A].Average()
What I need is for [ItemA] in the above formula to be a variable based on the current row.

Is this possible?

I don’t think this is really possible because ‘schema’. My understanding, and I’m happy to be proven wrong, is that generally when using a database like Coda, you want to keep all of the ‘type data’ on the row, not the column. So you’d be better served by Monthly Sales Units, instead of [Item A] [Item B] [Item C]`:

[Item], A, 13
[Item], A, 17
[Item], A 11
[Item], B, 110
[Item], B, 80
[Item], B, 33
[Item], C, etc..

This way you can do a lot more with the data, like your averages. Its also how the formulas and buttons ‘think’ - if you want to AddRows() // add more data // its going to come in the form of a new row.

While it seems like it may be ‘more concise’ to do it the way you have, I’ve found as I’ve migrated from spreadsheets where I would use your current syntax frequently, setting it up as a table with more entries provides way more power.

Another benefit, if you want to add another datum to to Item A = 13, how do you do it? You can’t right now. But if you had the table, you could add another column Price or Weight and then give everything another attribute.

Best of luck!

1 Like

Thanks for your response. It’s too bad if it’s not possible.

I understand the idea of working vertically in columns when you are gathering a lot of data. I can structure my first table as you suggest. However, the reason I want to try to bring an average into a row in another table is so that I can use that average in some calculations within the same row.

In my particular case, I can sync in my inventory level in separate rows of a table. If I can then reference the average daily sales and place them next to the inventory levels, then I can calculate how many days of inventory I have left in that row. That data can then be displayed in a beautiful gantt chart where you can visually see which products are running low.

Problem is, if I can’t get the averages or totals or anything from my the vertical columns in table 1 into a horizontal row of table 2, then the whole concept won’t work.

@Dominic_Symons

You can iterate over the columns using SwitchIf().

SwitchIf(
  [Column 1] = "Item A", [Monthly Sales Units].[Item A].Average(),
  [Column 1] = "Item B", [Monthly Sales Units].[Item B].Average(),
  etc.
)

Hi @Dominic_Symons,

I have the feeling there might be some data design that could actually help on this.

Rule of thumb: “any time you have a value in a dimension, there is some smell”. i.e. if there are some data as columns we should be alerted.

For this reason, I believe that Item A, B and C should not be columns.
You might see them for convenience as headers, but I guess that they are references to other data; also because this way your data model won’t change if/when your Items change

I provided an example that simulates your use-case.

As you can see, not only you can visualise your data according to your needs, but this way you can easily implement any statistics (also: automatically)

I hope this helps and i’m curious to know your opinion.
Cheers

It’s interesting how frequently this comes up.

In my own usage I have found that it generally falls to a question of scalability. If the data set needs to grow over time, I build out the proper schema to facilitate that. If it’s a more static solution, I’ll just use SwitchIf() or something similar to get the job done and move on with other work.

That’s one of the nice things about Coda. When proper schema is required, it can be built with minimal overhead, but the user isn’t forced to build it (and manage it) for every single situation.

Still though, programmatic access to columns would be :heart_eyes:.

1 Like

Wow, that’s pretty cool. First time I see a table grouped on the top and bottom like that to organize data. I feel like I still have so much to learn but I believe this can work for me.

Thanks a lot!

Dominic

1 Like

OK I might have assumed it was intuitive, but with my layout you make another table with columns

Item Average
a =Monthly Sales Units.filter(item=thisrow.item).average())
b etc

Then you can also pull other metrics, like max(), min() and other more complex analysis.

Yes, I had figured that out just after replying to you earlier! I think I just hit send before it really sank in.

Federico,

Your solution has really worked well for me. I got stumped on one seemingly simple issue though. I made an interactive filter on the monthly sales unit table so I can sort by last three months, last six months but the transposed averages don’t change with the filter. Is there an easy way to make the average reflect the interactive filter value of the table above?
Thanks,

Hi @Dominic_Symons,

You are right.
A good solution would be to create a View of the Monthly Sales Unit Table - like in the modified example) and refer to that view in the transposed table.

The table is always considering all the rows, while the view is an actual subset of the original table.

Let me know if it helps.

Worked like a charm. Thanks so much!
Dominic

1 Like