Formula Column for Summary Calculations

Platform discussions
,
1

My team and I are currently utilizing a board to monitor the performance KPIs of one of our sales channels, and we’ve encountered a specific need related to data analysis and presentation.

In our setup, we have a Formula column designed to calculate the average order value by dividing the total value of orders (Column Value) by the number of orders (Column Orders). This setup works well for individual row calculations; however, when it comes to the column summary, we’ve noticed that it averages the results of the formula for each row, rather than recalculating the average based on the summaries of the other columns involved.

Our ideal scenario would be for the column summary to dynamically calculate the overall average order value by using the sum of the “Column Value” and the total count from the “Column Orders”, thereby providing a more accurate reflection of the average order value across all entries.

Is there a way to configure the Formula column to perform such a calculation for the column summary? If not directly feasible through the current settings, could you advise on any workarounds or if there are plans to incorporate such functionality in the future?

2

Hi Karim,

I hear where you’re coming from here.

A potential workaround you could set up, is a connect board column and connect the board to itself. From there you can connect the rest of the items in the one cell and mirror the numbers column - you can them integrate that mirrored numbers column in the formula column:

Screen Shot 2024-04-04 at 1.23.56 pm
Screen Shot 2024-04-04 at 1.23.56 pm2364×700 108 KB

An another alternative workaround (whilst I understand this would change the format of your workflow) is to set up those numbers in the subitems, and mirror the subitems number column into the parent item (as a column summary), to then include in the formula - this way that column summary is included in the formula column.

I hope this helps in some way and do let me know if I’ve misunderstood your request

3

Hi Bianca,

Thanks for the feedback and for the workaround. Unfortunately, it won’t really work in this specific board workflow.

Here is a test board that better demonstrates the issue:

image
image712×427 24.6 KB

As shown above, we have 3 columns:

  • Orders (daily orders) / NUMBERS
  • Gross Value (total value of orders) / NUMBERS
  • Average Order Value / FORMULA = DIVIDE({Gross Value},{Orders})

So it obviously works for each Formula cell on the individual rows.
But not on the summary.

Would it be possible to add this as a feature request? i.e. having an additional button next to “Note/Sum/Average/…” that would allow a user to select the formula to be applied on the summary.

2 Likes