Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
how do I get the sum of the values of the rows in a PivotTable?
Thank's.
Try writing a different expression for the sum of the rows using dimensionality() condition.
The native Totals of the PivotTable will only return the total of the aggregation (unlike the sum, avg, sum of rows which are allowed options in a straight table)
There is a 'sum of rows in pivot table' topic in the product HELP under the aggr() function. Also attached about 1/2 way down.
The aggr() does what is called 'Advanced Aggregation'
It involves doing multiple aggregations... so if i wanted to sum up the max sales values for every region, year combination i would write something like this
Max(Sales) -> evaluates max(Sales) at dimensional context of chart. The grand total would be the biggest max value of the dimension values / intersections.
aggr( Max(Sales) , Region,Year) -> same as above if the chart has region and year as the sole dimensions. Again the grand total would be the biggest max.
sum( aggr( Max(Sales) , Region,Year) -> sums up the maxes , so that outside of the dimensional context of Region and Year , you are adding up the maxes for a sum of maxes (sum of rows)