Using Visual Totals

Posted by Mathias on Apr 26, 2010 in Bime Tips, Blog | 0 comments

You can obtain standard sub and/or grand totals on rows and/or on columns simply by selecting the check boxes in the “Totals” toolbox illustrated in Figure 1. This can be done in any visualization, but a data grid is easiest for our example.

Fig. 1 – The Totals toolbox.

Use visual totals for non-additive aggregators on calculated measures

Totals cannot be computed correctly when the aggregator on a calculated measure is non-additive, such as AVG (average), COUNT or D_COUNT (distinct count). In this case, an information message is displayed and these totals are left blank in the result, as illustrated in Figure 2.

Fig. 2 – Totals are left blank (blue shapes) when aggregator is non-additive

If you want values for these non-additive totals, you can toggle the visual totals check box, computed as the sum of the displayed values, illustrated in Figure 3. However, keep in mind that these visual totals do not match with the specified aggregator, e.g. are not averages in this case.

Fig. 3 – Visual totals (blue shapes) for a non-additive aggregator on a calculated measure.

Sometimes, the definition of a calculated measure (i.e. the associated calculated formula) can cause counterintuitive values for totals. For example, let West Shipping Cost or One be a calculated attribute defined as follows:

IF ([Region] = “West”) THEN SUM(Shipping Cost) ELSE 1 ENDIF

The semantic of this calculated measure is: if the value of the Region attribute is equal to “West” then return the SUM(Shipping Cost) measure’s value, else return 1. As illustrated in Figure 4, several computed values for totals are counterintuitive, such as the total of the line highlighted in blue. The expected value of this line would be 3 (i.e. the arithmetic sum of the line). However, since the value of the Region attribute is not equal to “West”, the return value of the calculated measure is 1 for the total of the line.

Fig. 4 – Calculated measure’s definition can cause counterintuitive values for totals.

So, if you want to obtain the intuitive values for totals, you can toggle visual-totals, as illustrated in Figure 5.

Fig. 5 – Visual totals for intuitive values on calculated measure.