Hi All,
I'm hoping to set up an Analytics line chart with the running sum of ticket revenue (by order days prior to performance) that has a second line that shows the budget amount. In theory, that second line would be a straight horizontal line across the chart, as we see the ticket revenue approach the goal.
However, when I set up this chart I'm getting a jagged line for the budget (see the black line in the screen shot) I think is a result of the budget filter also respecting the dates (not sure if the budget was edited occasionally throughout the season) but what I want is a straight line that only shows the total/max budget.
To make matters more complicated, I cannot hard code the number into the chart because I'd like the widget to respect the dashboard filters (whether the user selects one event, multiple events, or a whole season).
Any thoughts?
Hi Anna,
This is a sticky one because in order to avoid inflation of the Performance Budget Amount field, we typically use multi-pass aggregation, and group the Budget by Performance ID prior to taking a SUM()...
( SUM ( [Performance ID] , [Max Budget Amount] )
The other technique we have in our toolbox is the ALL() function, to break out of however we're grouping the data in a widget and get access to more holistic numbers, for example if instead of Budget Amount, you were after the % of the overall paid amount at the end of the curve...
RSUM ( [Total Ticket Paid Amount] ) / ( [Total Ticket Paid Amount] , ALL ([Order Weeks Prior to Performance ]) )
Where we hit on a challenge with Budget Amount is we can't easily break out of our Order Weeks for that Performance ID we're grouping by. We can only apply the ALL() function to an aggregation, and not to that "group by" field. I have 2 ideas for you to try.
If there exists in the data, reliably, at least one week in which all events have sales, then we could look for the week that has the MAX Budget Amount and render that on all weeks.
MAX ( [Order Weeks Prior to Performance] , SUM ( [Performance ID] , [Max Budget Amount] ) )
But if there is no week in which all events are represented, this value will under-report. We could instead embrace the row-inflation and try something like this:
( [# of unique Performance ID] , All([Order Weeks Prior to Performance]) ) *( [Total Budget Amount] , All([Order Weeks Prior to Performance]) ) / ( DUPCOUNT([Ticket Count]) , All([Order Weeks Prior to Performance]) )
The number of events, times the inflated budget amount over all weeks, divided by a row count. A quick test seemed to return correct results even with a mix of distinct budget amounts.
I'm attaching my work to simplify the formula construction for you, that way you can copy / paste the formula after importing the dashboard and testing it against your data.
RSUMAgainstGoal.dash
Hi Chris,
This is very helpful - thank you so much for taking the time to share this dash! I've tried all of the formulas you provided and am finding that the "embrace the inflation" formula works best for different combinations of performances/production seasons but if I choose the entire season it will embrace far too much inflation! I wonder if it's possible to combine it with a case statement that just sees total budget if full season is selected in the filters? Not sure that's possible?
I expanded my filters to an entire season and see what you mean. Thanks for working on this with me. I've actually been trying to solve particular use case for a while, and might have it. Would you try this? I'm reattaching the dashboard sample.
There's a new column in the bottom widget called Wk=0. It's the same formula as [Only Max from Weeks], however, I've tried setting a filter on the [Order Weeks Prior to Performance] field to only return the value from week = 0. That should work even if week 0 is filtered out of the dashboard or widget. It may not work as is if Unsold seats are filtered out.
The idea being that we need to find and report on a week in which all events are represented. For unsold seats, we stamp 0 into that [Order Weeks Prior to Performance] field. So assuming that all events have at least 1 unsold seat (or that if they're sold out, they have sales in week 0), then week 0 should have all events.
That seems to have done it, thank you! I did need to change my chart to order WEEKS prior to performance instead of order DAYS prior -- I originally tried to use your same equation by just changing that one criteria but it really doesn't want to work with days. This will work for our purposes, though, and I really appreciate the help!
I'm sad to hear that DAYS didn't work. May I have an export of your dashboard?
Just popping in the middle of this to say YESSSSSSSS! I changed it over to Order Weeks Prior to Opening and this is super helpful to be able to show on a graph instead of just an indicator widget! Thanks, Chris, and thanks, Anna, for bringing the topic up.