Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
Can anyone tell me why TotalMode is unavailable for Pivot Table.
Thanks
you will have subtotals in pivot table
Hi Nikhil,
In Pivot Table the total Mode will be diasbled in Expressions tab, rather you can make use of the Subtotals in Presentation tab
by nature pivot tables are a little different than straight tables, and as such, the option is disabled. The corresponding option is to use the 'Show Partial Sums' on the Presentation tab
The pivot table and the straight table have different calculation algorithms internally (to accommodate for the grouping and the sorting features), and the "Total mode" feature is very hard to implement in the pivot table algorithm.
However, you can achieve the same thing by using an Aggr() function. The "Sum of Rows" calculation can be defined by
Sum(Aggr( <OriginalPivotExpression>, <PivotDimension> ))
HIC
Hey Thanks but can you also tell me difference between total expression and sum of rows with any example?
Thanks
Expression total is the "correct" one - it evaluates the expression, but for the entire possible data sample. The Sum of Rows is sometimes (but not always) different.
In the following example you have sales per product. And one product can belong to two product groups. Note the difference in the totals.
In this example, the Sum of Rows is obviously wrong, but there are other examples where it is correct to sum over the individual rows.
HIC
Hey,
Thanks HIC.But i just did a small implementation that you did above and get different result in Expression Total. Can you plz explain why is it so.
Of course you get a different result. You have all the numbers in one single source table - and in this table the three rows sum up to 500. The aggregation always sums in the relevant source table.
HIC