We’re making some great progress! Now it’s time to focus on formula calculations. In this lesson, you’ll learn how to create your own custom calculations and apply them to your reports. Our BI expert Rastko will take you through it step by step.
You’ll learn:
Grab your materials and dive into a hands-on learning experience to get the most out of your lesson:
In today’s lesson, you will learn how to use Zebra BI Tables’ Formula editor and create custom formulas or custom rows within the visual. Additionally, you will learn how to format the formulas so you can make them stand out from the rest of the visual.
In this example, you will learn how to create Gross margin percentage of revenue, Operating income percentage of revenue and Net income percentage of revenue formula rows. We will also show how to format the formula rows to make them stand out visually. Consider relative variance specifics for percentages and how to control them. We will also mention some requirements and current limitations for creating formulas in multi-level tables.
Creating custom rows is easy. Simply right-click on the Gross margin account and select Add formula. Now you are in focus mode. And as you can see on the screen, the Formula editor is displayed at the top of the visual. Using it is really simple. First, define the name of your formula. In this case, it’s Gross margin percentage. Once you define the name, press Tab or click inside the formula box to define the formula. Start typing in the names of the accounts you need in the formula. The autocomplete feature will display the Gross margin account. Press Tab to include it in the formula. Take note that the square brackets around the category or account names are the syntax rule.
After you’ve defined the formula, you can apply custom formatting to it to make it stand out in the visual. First, you can format this formula as a percentage, which we will use in this case by clicking on the percentage symbol. Make it bold. And italic (to conform to IBCS for percent format). You can even change the color. Finally, you can define the number of decimals and units. You will learn about the formatting options in more detail in a minute. To include the formula row in the visual, press on the green Add button. Gross margin as a percentage of revenue in a few simple steps added in the Zebra BI Tables visual. Repeat this procedure for the Operating and Net income percentage calculations.
While creating a formula, it is worth noting that it will be displayed below the account you right-clicked on. Let me show you what I mean. Right-click on the operating income account. And select Add formula. The blue arrow in Focus mode is pointing at the place where the formula will be displayed. Just below the Operating income account which I used to add the formula.
If you are creating formulas in multi-level tables, as demonstrated here, make sure your formulas are at the same hierarchy level as the accounts you’re intending to use in the formulas. In this case, you can use only use the first-level accounts as the draft formula is below the first-level account Operating income. Additionally, the formulas will follow any sorting logic applied before or after their creation.
Next you will learn how to manage and format all formulas centrally by using the formula manager. Let me show you how it works. Right-click on any formula you’ve created. Net income percentage, for example, and click on Edit formula. Once we’re in focus mode, click on the blue Manage formulas button. Here you can see all the formulas individually. The formula name, the formula expression, units and decimal places.
Let’s move on to bulk formatting. Bulk formatting in the Formula manager allows you to format all formulas at once. Let me show you the trick.
Click on the top checkbox to select all formulas. Then you can apply the following formatting options. You can use different number formats, select the number of decimal places. For text, you can select bold, italic and color formatting. Using this opportunity, put columns or formulas in light blue. Click on the color formatting option and paste in the RGBA combination from the description. Click on Save, and the preview is visible in the Formula manager. To apply this, click on Save & close, and note that it’s applied to all the formulas in the visual. Finally, click on Save to display it in the visual outside the Formula manager.
Finally, let us briefly mention the relative and absolute variance columns’ display options. First, the value in the relative variance column is suppressed by default. To display it, click inside the visual, then move to Format your visual in the Visualizations pane. Under Data labels, turn off the Suppress relative variance percentage values setting. The value in the relative variance column is now displayed. Second, the value in the absolute variance column is displayed in percentage points by default.
Additionally, you can display the variance in basis points as needed. Turn on the Use basis points format setting. In this case, you’ll probably need to remove the decimal points, as shown on the screen.
Well done. You are truly making a difference. With formula calculations or custom formulas under your belt, you’re ready to conquer the world of actionable reporting. Keep up the great work.
