Hello to the Brain Trust,
I am trying to create a dashboard with budget amount for a 3-show run. When using a pivot table, if I add it to "Rows" I get what I need, but I really need it to live in the "Values" field so that I can run a formula like [Total Ticket Paid Amount]-[Total Budget Amount].
The problem is it does not return the right amounts. When I looked at documentation there is a caution that " This field should not be used as a Value field without applying multi-pass aggregation", my problem, I have NO IDEA what that means or even how to do it.
Has someone done this successfully and can I steal your formula?
Thanks in advance for your help :-)
Kimberly
I still don't fully understand this but this link was given to me and I find it helpful:
https://www.tessituranetwork.com/Help_System/Tessitura.htm#Tessitura%20Analytics/Designing%20Dashboards/Multi-Pass-Aggregation.htm?Highlight=multi%20pass%20aggregation
So for your formula you would want something like SUM([Performance ID], [Total Ticket Paid Amount]) - SUM([Performance ID],[Max Budget Amount]) which should be the total number of paid tickets (for the performances that fit your filters) minus their combined goals without duplication.
I use a similar formula for a percentage-to-goal widget.
Hi Kimberly,
You need to watch this - https://www.tessitura.com/Items/Videos/Training/Analytics/Webinar-Collection
Specific to your issue id the Goal Amounts that starts in Part 3 @ 2m50s
I believe that the following is what you are after if you filter your widget/dashboard to the correct Prod Season
SUM ( [Performance ID] , MAX ( [Budget Amount] ) )