I’m seeking assistance with creating columns that dynamically update the following information:
- Lifetime Deal Values: Sum of values from won deals associated with an account.
- Annual Recurring Revenue (ARR): Sum of values from won subscription deals that generate yearly revenue.
- Projected Additional ARR: Sum of values from active deals that, if won, would increase ARR.
Current Status:
- I’ve managed to create similar data using Chart views with filters for won deals or specific deal types. Both on an Item View Chart Widget and from the Board View Chart Widget.
- However, I can’t replicate this in table columns correctly. The number column and formula columns fail to aggregate values from the Deal’s board to the Account’s board.
From what you can see, for anything red outline with “0” is failing to add when it should be added. If its “0.00” that is because the deals are not perpetual or closed and makes sense that it should be $0.00. But I don’t understand how some are coming out with just 0. And in other cases the blue outlined values are active deals involved yet somehow the ARR an Projected ARR are the same when that shouldn’t be the case. I’m not sure if I have the formula incorrect or there is a mirroring issue.
Here are the formulas
Absolute - Deal Value won, regardless of type
IF(
{Close Probability 
} = “100%”,
TEXT({Total (Actual)}, “0,0.00”),
“0.00”
)
Absolute (ARR Filter) - Delas won that are Yearly/Monthly Subscription
IF({Close Probability } = “100%”, IF(CONCATENATE({Billing Cycle [TEST] 
}) = “Yearly”, TEXT({Total (Actual)} * 1, “0,0.00”), IF(CONCATENATE({Billing Cycle [TEST] }) = “Monthly”, TEXT({Total (Actual)} * 12, “0,0.00”), “0.00”)), “0.00”)
ARR (Additional Forecast) - Additional ARR that could be added that is from an active deal using forecasted value
IF(AND(OR(CONCATENATE({Billing Cycle [TEST] }) = “Yearly”, CONCATENATE({Billing Cycle [TEST] }) = “Monthly”), OR({Close Probability } = “0%”, {Close Probability } = “100%”)), “0.00”, IF(OR(CONCATENATE({Billing Cycle [TEST] }) = “Yearly”, CONCATENATE({Billing Cycle [TEST] }) = “Monthly”), TEXT({Total (Actual)} * ({Close Probability } / 100), “0,0.00”), “0.00”))
ARR (Projected Total) - Additional ARR that could be added that is from an active deal using Total actual
IF(OR(CONCATENATE({Billing Cycle [TEST] }) = “Yearly”, CONCATENATE({Billing Cycle [TEST] }) = “Monthly”), TEXT({Total (Actual)} * ({Close Probability } / 100), “0,0.00”), “0.00”)
For example, this account says it has 0 for the column “ARR [TEST] ” that is mirroring from the deal board column “Absolute (ARR Filter) [TEST] ”. This should be mirroring won deals that are subscription and adding them up… However, it is not. But if we go to that deal, we can see the won deal in the deal board you can see it has value in the “Absolute (ARR Filter) [TEST] ” column from the subscription deal won. Yet it’s not translating over.
Objective:
- I want to pull this data into table columns to filter and view top lifetime value customers or top ARR customers in the table view.
Questions:
- Is it possible to achieve this in table columns?
- Are there existing apps or special workflows to accomplish this?
- Is there a bug causing this issue or I have an incorrect formula?
- Ideally, it would be nice to instead have a “calculator” type column to filter and aggregate data from connected columns from other boards for reporting at the deal, account, and reseller levels.
Any guidance or suggestions would be greatly appreciated!