Whenever you want to add a new element to your table and it’s not in your data set, you can now simply calculate it with a custom formula within a Zebra BI Tables add-in. Zebra BI Formula Editor provides powerful capabilities not available in native Excel and helps you write formulas and easily calculate new data elements within your tables.
Example: Adding Operating profit %
The example below explains how the Formula Editor works. You can use the same principles for any data set, no matter what industry you’re working in.
1. Right-click on Operating profit and select “Add Formula”.
2. The new formula editor will appear at the top of your table.
3. To add a new row, simply type the name of your new value column (Operating profit %) and type your custom formula:
Operating profit % = Operating profit / Sales Revenue
The auto-complete feature will search for all elements that you already have in your data set.
4. To format your new value column, simply click on the % symbol, use bold or italic formatting, apply your custom color, and confirm by clicking the “Add” button.
5. The new row will appear below the Operating profit and will calculate the percentage in all table columns. That means it will work for all your value columns such as PY, and AC, as well as for custom-calculated variances.
That’s how you can add completely new elements to your tables, without doing any work on your data set. If you need to change anything (formula, color, settings, etc.), simply right-click on the formula and select Edit formula. This will open the formula editor again. After you’re done, don’t forget to click on Save.
To manage multiple formulas in your report, use Formula manager.
This feature enables you to see a list of all the formulas used in your report. The main benefits are:
- formatting the formulas one by one or all at once
- deleting the formulas that you might not need anymore.
If you changed or deleted something by mistake, there is an Undo button, which will erase any changes you did accidentally.
To access the formula manager follow the steps below:
Step 1: Right-click on any of the rows.
Step 2: Select Add Formula.
Step 3: In the Formula editor click on the new button Manage formulas.
Step 4: Edit the formulas you wish or delete the ones you no longer need.
Step 5: Click on Save & close when you’re done.