Different calculations on subtotal level

Sometimes you may need to calculate numbers in a crosstab differently if it is on the lowest level (the leaf level) or if it is on one of the subtotal levels.

Example

If you have a crosstab with two measures, and the initial calculation should be one measure as a percentage of the other measure, you could do it something like this as a calculated column (c1):

sum(d-1, 0, m2) / sum(d-1, 0, m1)

When the above is formatted with 'Percentage' number formatting option, the result may look like this:

Notice that, on subtotal levels, the calculation is also just measure 2 divided by measure 1.

A use case might be that the calculated values on the subtotal levels should instead be the average of all children for each subtotal.

You can achieve this by adding a second calculated column:

if allcount(d-1, all(c), m1) = 0 then sum(c1, 0, 0) else avg(c1, all(c), 0)

Eventually, use 'Visibility' settings to hide the intermediate c1 calculation.