Hello everyone.
I have an existing board containing sales opportunities. The board has two status columns, one for Opportunity Type and one for Sales Stage. I’ve been asked to create a table for a management report containing the value of opportunities broken down by Type and Stage, and displaying summed figures for each combination.
So I’ve created a new Report board with rows and columns as shown by sample data below:
Stage 1 Stage 2 Stage 3 Stage 4 Stage Total
Type A £100 £50 £25 £10 £185
Type B £50 £30 £10 £5 £95
Type C £200 £250 £350 £200 £1000
Type D £50 £70 £0 £10 £130
Type Total £400 £400 £385 £225 £1410
The Type Total I get for free at the bottom of the table and the Stage Total is a simple formula column. Now I want to connect my new Report board to the Opportunities board and pull through the sum of the filtered items for each cell in my table. Eg populate the cell for Type A:Stage 1 with the SUM of the value for all opportunities WHERE Type = A AND Stage = 1.
In my new Report board I’ve added a Connect Board column, connected to the Opportunities board, and then a Formula column. However in the Formula column I can only see Names and Count available from the Opportunities board, and have found no way to access other columns.
How might I get this working? Do I need to do the filtering and sum in the Opportunities board in an extra column rather than trying to do it in the new Report board? And then how do I link to that value?
Or is this scenario beyond the standard Monday.com functionality? I’m a software engineer and this would be straight forward to do with code. However I don’t know anything about the Monday.com API and custom apps, but could explore that path if required.
Hi Stephen,
I’ve sampled your data structure and shown how you can use a Pivot Board to create what you’re looking for. Unfortunately this is an Enterprise feature.
For a non-enterprise solution, you could do something like:
- Create formula columns on your Opportunites Board for each Stage and populate them with formulas for that stage. i.e.
IF(
{Stage}="Stage 1",
{Amount},
0
)
- Create 2 two-way connection columns between your Opportunities Board and your Report Board name them “Type Connection” and “Total Connection”.
- On the Reports Board, create items for each Type as you have done above, as well as a total.
- Set up automations (there is a connect board automation that is relatively intuitive—let me know if you need help with it) or manually connect items as follows: All Opportunity items connected to their corresponding type Report item via the Type Connection column; All Opportunity items connected to the total Report item via the Total Connection column.
- Create Mirror columns on your Reports board for each Stage. Connect the mirrors to the formula columns you created earlier and show summary as “SUM”.
Alternative to the above would be to avoid the “Total” row and just use the group summary. Having a “Total” row is very excel and not very monday.com—monday.com boards are databases rather than spreadsheets. In that case, you’d only need one connection column.
An alternative non-enterprise solution could be to show the data as a graph. Create a dashboard and use bar charts for each type for instance.
Hope that helps!
Aha - pivot tables! Pivot tables I understand. Thanks Francis, I didn’t know Monday.com had those and I’ve got access to them with my subscription. I’ll have a play with adding a pivot table to my opportunity data.
Unfortunately Pivot Boards in Monday seem to be a bit undercooked. I first set up a Pivot Board as a new view in my main Opportunities board using the Type and Stage columns. Then I selected Sum and the Total Net Rev column to populate the cells. But all values are returned as 0.00 despite the Total Net Rev column having non zero numbers in it.
Cells where it’s blank rather than 0.00 are correct, eg there currently are no Type 1/Stage 1 opportunities. If instead of Sum I set the cells to be Count then I do get the correct count values in the relevant cells. My suspicion is that Sum isn’t working because the Total Net Rev column is actually a Mirror column that is set to sum values from the child subitems.
So then I tried instead adding a Pivot Board as a widget on a dashboard. Interestingly the behaviour of the Pivot Board dashboard widget is also broken but different to the behaviour of the Pivot Board view.
Now the Total Net Rev values are non zero numbers which look about right (I haven’t done a thorough check), so the dashboard pivot board can in fact pull values from a mirror column. However the Pivot Board has placed all values in extra null columns for both axis. I checked and confirmed that there are no null values in the Stage and Type columns so no idea why those bonus columns are getting added. And further the bottom right Total/Total cell is double counting and twice the value it should be.
So, next step is probably to contact Monday support to see what they suggest.
Hi Stephen,
Yes, there are two different Pivot Boards. I believe monday.com released one as an app, which they still support, before eventually releasing one as a widget.
In my experience, the widget is better.
If you’re having trouble with mirrored values, here are some suggestions:
- Try reversing and basing a pivot board off the subitems rather than the items if possible.
- Try using Formula columns. Even if it’s just to grab the data from the mirrored column and put it in a Formula column, the Pivot board may cooperate better with this data.
