Calculate deviation between two calculated row totals

I have a crosstab which shows actual and budgeted sales for top 10 customers. I have a calculated column ("deviation") which shows the difference between actual and budget figures in percent.

At the bottom of my crosstab i have a calculated row (highlighted in yellow) which sums actual and budgeted sales for all customers outside top 10 - the remaining customers are hidden using a visibility agent.

My problem is that the calculation from the deviation column is not passed correctly to the calculated row at the bottom of the crosstab. The correct figure in the attached example should be "-79 %", but "9" is shown instead. How can i fix this?



  • Hi Thomas

    If you add your deviation as a calculated column (not a new measure) - and then add your calculated row you should have a table looking a little like this:

    The red square is where the calculated column and the calculated row meet - a so called intersection.

    Now you can decide what should be done in the intersection - should the row continue (that would make total of the percentages) or should the column continue (that would calculate a percent as desired).

    In my case I also want the Deviation calculation to continue - so I open the Deviation calculation and click Calculate in intersection:


    And then I set a checkmark to decide that Deviation should be calculated in intersection where it meets the Total calculated row and click apply:


    Now my table looks like this:


  • Hi Niels

    This works perfectly :-) Thanks!



Please sign in to leave a comment.

Didn't find what you were looking for?

New post