Calculations in crosstabs

If we create at a crosstab based on Profit per Customer Country (attribute) and by Time Year (from the Sales cube), we should end up with a crosstab with 14 rows and 4 columns (not counting the Total row/column) like this:


This crosstab, with the reference synatx in mind, can be broken down to:

  • Columns: Years, d1 to d4 – or d-1 to d-4 (if counted from right to left).
  • Rows: Countries, d1 to d14 – or d-1 to d-14 (if counted from bottom to top).
  • Measures: Profit, m1. This crosstab contains only one measure.

To calculate the difference between e.g. the last two years, we can do this with a Custom calculation:


We could also add an extra calculated column, Growth last two years, like this:


With Growth number formatted as Percent we should end up with a result like this:


Furthermore, we could add an average calculation for each year like this:


With the average calculation included, we should get a result like this:


Notice the two blank cells in the intersection between the calculated columns and the calculated row. They are blank simply because TARGIT does not know if you prefer to calculate an average for the diff and growth valuesor if you prefer to calculate a diff and a growth for the average values.

Supposing we decide to calculate the diff and the growth for the average values, we should re-open the the Diff and the Growth calculation to make sure that the Average is included in the Calculate in intersection setting:


In this way, we will eventually get to a result like this:


Was this article helpful?
0 out of 0 found this helpful



Please sign in to leave a comment.