Is anybody able to help me out with the correct formula to find the average number of Order Weeks Prior to Performance? By outputting a pivot of Order IDs, Performances and Orders Weeks Prior to Performance into Excel I can see that the average is one number, but when I use the formula "avg([Order Weeks Prior To Performance])" I'm getting an entirely different, clearly wrong number.
Thanks as ever!
Hi Lawrence,
The AVG() function will not serve us well in this case. The grain of the data in the Seats and Tickets cube includes a row for every price layer on every seat and/or ticket. The AVG() function only supports taking the average across all the rows of data in the result set and so if you've got some tickets with 1 layer that are 2 weeks prior and others with 3 layers that are 1 week prior, the ones with 3 layers will have more weight in the average and skew the results. As well, the unsold seats have a placeholder value as well of I think 0 weeks prior, and they too will skew the results if not filtered out of the equation.
One approach may be to filter on just the Ticket Price layer category, and [Seat Sold Flag] field set to Y. This could limit your set to just one row per ticket, but this assumes you want an average based on a count of tickets and not a count of orders. Meaning that order with 10 tickets to 1 performance is given greater weight in the average than an order with 1 ticket to that same performance.
For weighting the average at the level of an Order instead of a Ticket, I recommend we derive a formula that will count the Weeks Prior only once per order. This is similar to counting the Performance Budget Amount only once per Performance:
SUM ( [Order ID] , [Max Order Weeks Prior to Performance] )
Then divide that by the count of orders:
SUM ( [Order ID] , [Max Order Weeks Prior to Performance] ) / [# unique Order ID]
This still assumes we are still grouped by maybe production season, and that multiple performances on different dates within a production season are not in the same order.
If you're in need of this average "across performances and productions," where we need to respect different Order Weeks Prior to Performance within the same order for different performances... then we need to group that Order Weeks Prior to Performance by both Order ID and Performance ID and sum those results:
SUM ( [Order ID] , SUM( [Performance ID] , [Max Order Weeks Prior to Performance] ) )
And then divide not by a simple unique count of orders, because in this higher level aggregate, a unique count will underrepresent an order with multiple performances in it. We need a unique count of Order ID + Performance ID. For that we could take a unique count of orders within each performance, and then sum those results.
SUM ( [Order ID] , SUM( [Performance ID] , [Max Order Weeks Prior to Performance] ) ) / SUM( [Performance ID] , [# of unique Order ID] )
To be safe, we might still want a filter on the [Seat Sold Flag] field set to Y, just to be sure there aren't any unsold seats slipping in.
Best,Chris