Hello, analytical friends!
I've been struggling with something for a while and so I come to you. I have a pivot widget in my daily report that is a re-creation of an excel chart. Everything works except for the Grand Total of the Budget Amounts. I've attached pictures of what my widget is showing along with what is showing in Season Overview, the formula I'm using in Analytics and the sql query I did to see if something was incorrect in the wrong data. If anyone can assist me in what I need to adjust, I will be SO grateful. Thanks in advance!
I forgot to add that the budget amounts are correct for each production season, it's only the grand total that is wrong.
Hi Katie!
One thing to check is what the "subtotal by" is set to on your values. Sometimes the "Auto" option can do funny things so you need to manually change it to "Sum."
- Christine
P.S. (Sending you greetings from Pittsburgh!)
One thing that's helped me in the past is to change the 'sub-total' to 'sum', rather than 'auto'.
When using bucketed formulas, this can sometimes be the issue.
Thank you, thank you, thank you!!!! I love having access to super smart people! It's fixed!
P.S. hugs & kisses, Christine! I miss you! All my love to the 'burgh!
Hi Katie,
That subtotal setting is the easiest and quickest fix. The deeper fix is to know that the Budget Amount doesn't live in the SEAT & TICKET VALUES dimension, and is therefore subject to inflation when aggregated. In this case the budget is being inflated by the count of seats, tickets, and price layers. To counteract the inflation, Multi-Pass Aggregation is your best friend. This formula...
SUM ([Performance ID], MAX([Budget Amount]))
...sums only one instance of the budget amount per performance. This allows for "Auto" subtotals, and will not inflate even when other filters, like Price Layer Category are in play.
Best,Chris