How do I bundle tow categories into one?

JLADSK2992

I am trying to track revenue that is open and revenue that has been won together through an intake form. It is currently showing all of it together but I want to be able to track open revenue through open cases and how much has been one. I need an equation for this. I also need to bundle other categories like this : AOV deal type - new, renew, both, AOV - dollar amount text input, opportunity dollar amount - text input - status drop down in progress resolved, high priority.

Brian_Richardson

In the answer below I'm assuming the following…replace these column names with your own as needed:

• A column called "Status" with options for "Open" or "Won"
• A column called "Revenue" with the expected revenue number, formatted for currency

If you want to know the amount per case, create a column called "Open Revenue" and enter the formula =IF (Status@row="Open", [Revenue]@row) in one of the column cells. Then right click that formula and choose Convert to Column Formula. This will apply the formula to every case. This formula checks to see if the case is in "Open" status, and if so it gets the Revenue number, if not then it stays blank.

Repeat to create a column called "Won Revenue", changing the formula slightly to =IF (Status@row="Won",[Revenue]@row)

You can then create a report and place the two revenue columns in the report, and use the Summarize function to Sum them both. That will give you a total Open Revenue and a total Won Revenue in your report at the top. If you group your report by something, like Location or whatever, then you'll have the revenue figures for each group as well as the total.

If you need the totals for Won and Open Revenue to do some further math, you can leverage the Summary fields found on the right side of the sheet. Click the Summary button, then add a Total Open Revenue field. In that field type the formula =SUM([Open Revenue]:[Open Revenue]). Repeat for Won Revenue.

Repeat this setup for your other various groupings to get the combinations of revenue states that you're looking for. If you need multiple conditions, you can change =IF(Status@row="Open",[Revenue]@row) to something like =IF( AND( Status@row="Open", [AOV Deal Type]@row="New"), Revenue@row)