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

1

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?

Screen Shot 2020-06-25 at 8.06.18 PM
Screen Shot 2020-06-25 at 8.06.18 PM1946Ɨ1442 154 KB

1 Like
2

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
3

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.

4

@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.
)
5

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

6

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
7

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
8

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.

9

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

10

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,

11

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.

12

Worked like a charm. Thanks so much!
Dominic

1 Like