Pivot table - Grand total and Averages in total



1 comment

  • Avatar
    Ido Darnell

    Hi Jeff,

    If I understand correctly, you're looking to show to values, i.e. assuming your pivot holds SUM, f the pivot table holds the sum of X for dim A, you'd like to show both the SUM of SUM(X) of A and the AVG of SUM(X) of A?

    It won't be possible to achieve 2 lines, one for each, on top of each other, without developing a plugin, but there are 2 things that come to mind which can provide these values in a different layout:

    1) Using a multipass aggregation:

    - Either hold the Grand Total as SUM or add it as a numeric indicator, for a grand total, simply add the 'Grand Totals' and make sure that at the value level, the subtotlling is being done by SUM (other aggregations, including auto, are available on the value's 'subtotal by' menu.

    - Then add another indicator which will hold the value which will represent the AVG of SUM(X) of A. For this you will need to utilize the multi-pass aggregation formula, see link below, the syntax should look like this:


    Multi-Pass Aggregations:


    2) Another, less elegant solution would be to simply add this column twice in the pivot, then sub-total it once by SUM and another by AVG. Less elegant and less recommended, as duplicate data may confuse end users.

    LMK if this helps,



    Comment actions Permalink

Please sign in to leave a comment.