Calculate Totals and Subtotals for visible data

If data in a crosstab is influenced by a visibility agent, you will see that the remaining Totals and Subtotals often are wrong. They do not take into account that some data has been hidden in the crosstab.

The workaround is to create calculations that can replace the Totals and the Subtotals.

Calculations for Total of visible data

In this crosstab, Salespersons with Revenue less than 500000 have been hidden:

'Total of visible' has been calculated like this:

sum(0, all(v), m1)

Furthermore, the original Total may hidden (by use of 'Visibility' in the Properties Smartpad) and 'Total of visible' may be pinned to the top.

Calculations for Subtotals (and Total) of visible data

When your crosstab contains a dimension hierarchy, you may also need to consider how to get the Subtotals correct.

In the second crosstab in this screenshot, Months with Revenue less than 4000000 have been hidden:

In this case, to get to the right Totals and Subtotals we will need as many calculated columns as we have levels in the hierarchy.

Eventually, from 'Visibility' in the Properties Smartpad, you may want to hide the original Revenue measure and the intermediate calculations (c1 and c2 in above screenshot).

The syntaxes for the calculations in this example:

Calc Column (c1):

if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m1) else sum(d1, all(v,c), m1)

Calc Column (c2):

if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m1) else sum(c1, all(v,c), m1)

Calc Column (c3):

if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m1) else sum(c2, all(v,c), m1)