Creating a Custom Formula or Calculated Member in Sage Data & Analytics

In Sage Data & Analytics, custom formulas can be created for use in a single report or resource. Custom formulas can only be used within the resource in which they are created. Calculated Members, on the other hand, are like custom formulas but can be reused across multiple resources.

In this blog post, we will review how to create both custom formulas and calculated members.

What is a Custom Formula?

A custom formula is a calculation of returning a single input value based on specific criteria.

Case Study:

The Controller has a report that shows you the sales amount by sales rep/employee. This set of data can be sliced down to Year, Quarter, Month, Day. You have been tasked to add column that will calculate the commission payout based on sales rep commission % set in the system with you an extra 2.8% bonus for all sales rep.

Steps to create the custom formula:

1. Go into Edit Mode -> Query

2. Right click in the Columns section -> Custom Formula

3. In the Dimension Tree, drag the Invoice line amount to the Formula section

4. Type in * and hit the enter key

5. In the Dimension Tree, drag the Commission % to the Formula section and enter in a 2.8% in the formula. You can type in the parenthesis for proper order of operation and drag it to the appropriate spot. Reminder to hit enter when you type an operator or value in the formula before dragging.

6. Apply

7. You will see a ‘Customer Formula’ in the Columns section. You can validate that the Custom Formula is doing exactly what you expect.
8. Rename the Custom Formula
a. Right click the ‘Custom Formula’ -> Rename it to ‘Commission Payout’

What is a calculated member?

A calculated member is like a custom formula, but it is reusable in other reports. You will see it available in your dimension tree. There are a few ways to create a calculated member, in our example, we are going to leverage an existing custom formula to modify.

1. Add the ‘Commission %’ to the columns -> Open Commission %

2. Double click on the ‘Commission %’ -> Unwarp primary field: Formula

3. Add in the .028 to the calculation with the correct operators -> Apply

Before:

After:

4. Save As -> Rename to ‘Commission % with Bonus’ -> Ok

5. This calculation can now be used in other reports as it is now in the dimension tree.

6. Go back to the Sales by Sales Rep report -> Refresh and you should see the ‘Commission% with Bonus’ -> Drag the ‘Commission% with Bonus’ to the columns.

7. Now you should see the ‘Commission % with Bonus’ -> Save

For more information on creating custom formulas or calculated members in Sage Data & Analytics, please contact us.