Glad I found this group from the conference. I am trying to answer what I think should be a simple question in Analytics: How are my current sales for a performance and how does that compare to the performance budget that we entered in the performance setup screen in Tessitura?
There is an option in Analytics to pull "Budget Amount", but the usage only appears to show the correct amount when used in a pivot table and only when placed in a row. If I put it in the value section, the amount is not correct. Does anyone know why [Budget Amount] when placed in the rows section shows the correct amount, but behaves differently and displays incorrect amounts when its placed in the values section in the pivot table or in anything else?
Hey all. I worked with Chris Wallingford on this at conference. Here is the skinny. The join to budget is a 1 to many join in 15.1 with performance being the many part somehow (I don't have direct access to my data, so I'm not positive on that join). So, the formula you need is:
sum([Performance ID],MAX([Budget Amount]))
Note: the same thing happens in Campaigns, but you can see the correct formula in the Tessitura supplied dashboards.
The explanation of the formula is as follows: Sum up the max of the budget amount for performances grouped by performance ID. The "group by" is done first in the Sysense formulas, unlike SQL which is at the end of the select statement. So, group by performance ID, get the max of the Budget amount from that group, and add it all up.
Worked for me. Hopefully, this will work for you guys too.
David - thank you for the formula, I thought that was exactly what I was looking for. But unfortunately it is still not working for me. The budget is correct in my pivot unless I add in Performance Date as a row. When I turn off the performance date row my pivot is grouped by production season and the budget is correct. When I add the performance date row the pivot then expands to show each individual performance date and then the budget balloons into over 55 million for each performance. I'm going to send in a support ticket on this.
Can't replicate this as we don't repeat performances, but it is a little weird that Performance ID isn't a unique enough grouping. Could you try grouping by performance date instead, still grabbing the Max budget amount for the group?