Creating Calculated Measures and Attributes [BIME TIP]

The Bime Formula Engine, has been designed in order to allow you to deepen and enrich your analyzes through defining calculated members. Calculated members allow you to combine several attributes and measures of a datasource and thus extract more information from your data. For instance, you can create a calculated measure, 'Margin', by dividing the Revenue measure by the Cost measure. The Bime Formula Engine is an additional tool available if the original attributes and measures of the datasource are not sufficiently detailed directly to express the information you want to show. Don't worry; it's not as scary as it sounds!

For this, consider a very simple financial datasource, with two measures of this being Revenues and Cost. These two measures are interesting, but they do not directly express the realized profits. Therefore, we will create a calculated measure named Profit, which will be defined as the simple numeric difference between the two previous measures Revenues and Cost.

First, start by clicking on the descending arrow on the right of the Measures field. Then click on "Create a calculated Measure", as illustrated here:

Create a calculated Measure

Click on Create a Calculated Measure in order to start its creation.

Now, define your calculated Measure by setting the several fields of the Calculated Measure Definition's dialog box.

Calculated Measure Definition

The Calculated Measure Definition's dialog box.

We have first to name the calculated measure we want to create. In our example, we put Profit in the Name field. Note that the new name must be different from the existing measures and the attributes in the connection.

We have now to express the syntax of the calculated measure, i.e. how the calculated measure has to be computed. In our trial example, we set SUM(revenues) - SUM(cost) which expresses the numeric difference between Revenues and Cost.

Note that the definition's dialog box shows the many attributes, measures and the functions you can use in order to express more complex calculated measures.

Once set, we check the syntax of the chosen formula by clicking on "Check Formula" to ensure that it will compute.

As illustrated below, we can now click on "save" and get back to the Pivot table.

click save

Click on save in order to create the defined calculated measure.

Once created, the calculated measure Profit appears in the measures area. On the one hand, and as expected, our calculated measure matches exactly with the numeric difference between the Revenues measure and the Cost measure, as illustrated previously. On the other hand, note that a calculated measure can itself be dragged-and-dropped in Bime, like the other measures, but independently of the original measure(s), as illustrated below in the second picture.

drag and drop

Our calculated measure Profit computes as expected the numeric difference between Revenues and Cost.

calculated measure

Once created, a calculated measure can be dragged-and-dropped exactly like other measures.

Creating a calculated attribute

As with calculated measures, a calculated attribute allows you to combine several attributes and measures from your connection in order to extract more information. For this, let us consider a connection that lists the top 500 fortunes in the world. To create a calculated attribute, click first on the descending arrow of the Axis of Analysis field and then click on "Create a Calculated Attribute", as illustrated below.

Create a Calculated Attribute

Click on Create a Calculated Attribute in order to start its creation.

Let us imagine that you want to determine the most profitable countries. For this, you have to define your calculated Measure by setting the several fields of the Calculated Measure Definition's dialog box.

As illustrated below, you first have to name the calculated attribute you want to create. In our trial example, we set "Profitable Country" in the Name field. Note that the new name must be different from the existing measures and attributes in the connection.

The second field of the definition's dialog box allows you to define if the calculated attribute will have to be systematically computed (i.e. depending of the values of attributes for each query), or if it has to be computed from one of the attributes of the connection (in this case, your calculated attribute will compute the first time it will appear in a query, and will be kept in memory as long as its definition remains the same). In our example, we want to be able to distinguish countries. Consequently, we select the Country attribute for the Computed from field.

We have now to express the syntax of our calculated attribute. In our example, let us imagine that we want distinguish two categories of countries, depending of the value of the generated profits: When a country has a profit greater than $50 000, we consider it to be a "Profitable Country", otherwise it is a "Non Profitable Country". The corresponding calculated formula is consequently:

IF (SUM(profits) > 50000) THEN "Profitable Country" ELSE "Non Profitable Country" ENDIF

as you can see below. Like for calculated measures, please note that the definition's dialog box shows the many attributes, measures and the functions you can use in order to express more complex calculated attributes.

Once set, you click on "Check Formula" in order to validate the syntax of the formula.

You can now click on "save" and get back to the Pivot table.

Calculated Attribute Definition

The Calculated Attribute Definition's dialog box.

Once created, the calculated attribute Profitable Country appears in the Calculated Attributes area. As you can see from the image below, you can now drag-and-drop it on the surface, with, or independently of other attributes. In addition, note that you can reference each defined calculated attribute in other calculated measures and calculated attributes.

drag and drop

Once created, a calculated attribute can be dragged-and-dropped exactly like other attributes.

So there you have it! A step-by-step guide to calculated measures and attributes in Bime. Not to be feared, these calculated members allow you to extract more information from your data than ever before. Play around with them - they open up your ability to ask any question you want of your data.

Test out the Premium version of Bime for free – Free trial