Hi folks,
I've made a dashboard for tracking ticket sales to target
Budget Amount is ok - I'm using the multi-pass aggregation and Grand Totals are great.
The trick is the ticket target. So over here the finance team want to also track number of tickets sold to a target number. That number is generated from Budget Amount (Gross Box Office) / an estimated ATP.
For Ticket Target and related fields I've used the formula
CASE WHEN [Max Production Season ID] = 939 THEN 4529WHEN [Max Production Season ID] = 953 THEN 3187WHEN [Max Production Season ID] = 962 THEN 3150ELSE 0 END
Obviously it doesn't lend itself to aggregation so my totals are out. Just wondering if anyone had thoughts on handling that.
I guess I'm looking for a place in Performances or Production Season to sneak in a number so I can divide by it. I'm not seeing anything in the Seats and Tickets Cube
Hi Heath,
On the Value for your Target, go to the options menu and select the Subtotal By option. It should be set to Auto, which reuses the formula of the value in the context of the subtotal. However, you can switch it to Sum which will just sum the results of the Value’s formula instead. Would you see if that gets you the total you’re after?
Best,Chris
That is golden.