When it comes to reporting, each company has its specific needs. That’s exactly where custom-calculated columns come in, providing just the flexibility you need to apply it to your own data. See how you can create your own hands-on.
You’ll learn:
Grab your materials and dive into a hands-on learning experience to get the most out of your lesson:
Custom calculated columns give users flexibility in visualizing the data beyond the pre-set reporting scenarios. You will learn how to visualize two or more measures in a single Zebra BI Tables visual.
In the first example, we’ll take a look at a single custom column representing a portion of actual sales in percent. In the second, we’ll take a look at multiple custom columns representing gross profit measures, together with sales data in a single Zebra BI Tables visual. On top of that, you will learn how to display different chart types and custom calculated columns, define different column measures and IBCS scenario patterns. Finally, we will say a few words about chart scaling and formatting columns.
We have actuals and previous year’s sales data for each group. Additionally, we have prepared a portfolio percentage of actuals measure (AC), and we would like to visualize it using a custom calculated column. To do that, simply drag and drop the measure into the Values placeholder next to the AC measure which is already in the Values placeholder, and it’s shown up in the Zebra BI Tables visual. Notice that by default, custom calculated columns don’t have any formatting applied or charts displayed in the table. This is where you, as a report creator, step in.
Everything you need to do with the column happens in the settings in the column’s dropdown menu. From here, you can choose the chart type and format the column (this is also available in other columns). Additionally, you can define the measure type and the IBCS scenario pattern. More on that later. For now, we’ll only display a pin chart and make sure that the IBCS scenario pattern is appropriate; like the color for the actuals data. Scaling-wise, you would like to keep this measure in the second scale group. Then turn on the Grand total row. As a final touch, let us add a background fill to the custom calculated column. Move to the settings in the column’s dropdown menu, and under Format column, select Background Fill and paste in the RGBA combination from the description.
Click on Save and notice that the background is now light gray. Light gray or light blue are great options for making the custom calculated columns stand out visually. Generally, you would like to avoid red or green colors, as they are reserved for negative and positive variances. In the following example, you will learn how to visualize two measures in a single Zebra BI Tables visual.
Next, let me show you how to visualize two measures in a single visual. We have sales data in the Scenario placeholders, actuals, and previous year for each business unit. Additionally, we have prepared gross profit measures and would like to include them as custom calculated columns in the table.
First, let’s bring all gross profit measures to the Values placeholder, as we did in the previous example. We have the gross profit measure for the actual data, bring it to the Values placeholder. Then we have the gross profit measure for the previous year data. Bring it to the Values placeholder as well. And finally, we have the gross profit actual versus previous year absolute variance measure which we will bring to the Values placeholder as well.
Now we need to define the following for each custom calculated column: calculation type, chart type, IBCS scenario pattern for the value measure type, and last but not least, scaling and formatting. Let me show you how to do it in a few easy steps. Starting with the Gross Profit custom calculated column, click on the settings dropdown menu next to the column. Moving on to the measure type, keep the measure type as value since this measure summarizes actual gross profit data. Moving on to the chart type, display a bar chart.
Make sure that the scenario pattern is actual. Going back to the settings, under scenario patterns, select the Actual IBCS scenario pattern colored in black. Let us repeat this procedure for the Gross Profit Previous Year custom calculated column. Under settings, keep the measure type as Value, as we did with the Gross Profit custom calculated column. Also, display a bar chart. An additional step is required in this case. Defining the proper scenario pattern for Previous Year measures. Go back to the settings in the column header, under Scenario patterns, select Previous Year and notice that the bar chart is now colored in gray which is compliant with the IBCS scenario pattern standards.
Last but not least, let us take care of the absolute variance custom calculated column. Move to the settings. First, change the measure type. In the dropdown menu, select Absolute variance. Go back to the settings and change the chart type to plusminus. Notice that the variance column colors are now aligned with the other absolute variance columns. To align the column order, rearrange the Gross Profit and Gross Profit Previous Year columns. As explained in the previous example, the good practice is to format the custom calculated columns and make them stand out a bit within the visual. All the formatting options we have in the other columns are available in the custom calculated columns as well.
Finally, we can say a few words about chart scaling and column formatting. It is worth mentioning that with custom calculated columns, a scale grouping option is available. Under settings, you can select Scale Group number one. With Scale Group number one, you can align the scaling across the visual. Choose from scale groups two to six to scale the gross profit measures separately. In this case, make sure that each column is assigned to the same scale group.
Perfect. Now you’re really getting a deep understanding of the capability and flexibility of Zebra BI visuals. I strongly encourage you to continue with the Zebra BI Academy series. We are here to support you. Good luck.
