I'm having a problem with summing a column in a pivot table. I use the following formula for the Actual Revenue column which works correctly for each line item:
When you look at the subtotals or totals, only the status IDs <> 'Firm' are summed up. Sample data is listed below. Can anyone please tell me how I would go about getting the whole 'Actual Revenue' column to sum up correctly?
Pivot tables always evaluate the expression for the total instead of summing the rows. On the total line, the StatusID has TWO values, not one, so evaluates to null in your comparison, giving you the else value. So your total line is probably sum(PostAmt * RevFlag)?
To do a sum of rows in a pivot table, you use aggr(). The basic form is this:
sum(aggr(your expression, your dimensions))
So in your case, something like this if I'm understanding your pivot table:
Pivot tables always evaluate the expression for the total instead of summing the rows. On the total line, the StatusID has TWO values, not one, so evaluates to null in your comparison, giving you the else value. So your total line is probably sum(PostAmt * RevFlag)?
To do a sum of rows in a pivot table, you use aggr(). The basic form is this:
sum(aggr(your expression, your dimensions))
So in your case, something like this if I'm understanding your pivot table:
