Sum of Rows in Pivot Table

[#1]

DISCLAIMER: I have read a lot of the other solutions out there on getting the sum of rows in a pivot table. After a few hours of trying various solutions, I need to submit here to see if there is something completely unique about my requirement! I can't figure this one out!

I am attempting to get an annualized commission figure based upon how much commission my company has received and how many months have been billed.

My expression for the Annualized Agcy Comm figure calculates properly BY PRODUCT, but I can't get it to total those rows. Here is my expression:

=(Sum({$<IsWon={'true'}>}ProductionCompany_AccrualBalanceCalc))

/

(Count(Distinct{$<IsWon={'true'}>}AccountingMonth))

* 12

Here is a screenshot of my dilemma:

Thank you for any help.

[whiteline · Accepted solution]

Hi.

It's because your expression is calculated for totals regardless of dimensions, but the months count is different for each row.

Use aggr function to calculate your formula over the chart dimensions explicitly and the Sum() function to get the result.

=Sum(aggr([your expression], [chart dimension1], ...))

[#3]

Ahhh, I had the Aggr part right, but I wasn't wrapping the whole expression in a Sum(). Problem solved!