Hello humans,
I'm trying to answer the question "What is the average ticket count of all performances with sales so far". Essentially, I'm trying to see what the average sales per day are for one of our Production Seasons, to date, only looking at current sales.
This is what I came up with, but I'm wondering if there's an easier way:
[Total Ticket Count] /
( SUM ( [Performance ID] , IF ( [Total Ticket Count] > 0 , 1 , NULL )))
I have to give most of the credit to Chris Wallingford, and is SUM/IF work in previous threads. But basically, I totaled up the amount of performances where more than 0 tickets have been sold,
then divided the total ticket count for the Prod Season by that amount. At least, that's what seems to be happening!
I checked this by pulling a CSV file, then using excel to total the numbers and average that way, and the numbers line up. I just wanted to see if there was a cleaner way to do this. Thoughts?
Hi Nathanael,
That seems fine / accurate. I'd offer an alternative that's maybe a little more performant and easier to read:
( COUNT( [Performance ID]) , [Ticket Count > 0] )
Gets the distinct count of events having rows with non-zero ticket counts. When adding Ticket Count, be sure to add it as a Filter and not as an Aggregation, and then use the Value type setting to select > 0.
You're also limiting by Production Season, so I assume this is your daily admission event. If you find yourself in a situation with more than one Performance ID on a given date, you could Count( [Days in Performance Date] ) instead of Performance ID.
Best,Chris