Hi everyone!
I had brought this question to analytic Coffee and I'm still puzzling through and wanted to share here.
I'm trying to create a line chart that compares productions in different years by order weeks prior to performance – however, I want it to look at an running sum proportional to the total sales. This would be for looking at historical data research rather than active sales. Below is a screenshot of how the chart currently looks - because I'm looking at sales pre-pandemic and after reopening, the totals are vastly different, but I want to compare the two lines to where the relative pace to total sales was similar or different. I could hard-code the final totals, but I want to be able to change things out to compare different performances.
Here's a look at the current chart:
Here's what I want to chart to look like - this has the totals hard-coded into the formula, but I want to be able to change the production filters on the chart without having to change to formula every time.
Other potential use cases might be comparing the pace of subscriptions to single ticket sales leading up to a performance, or a fundraising campaign breaking out different donor groups to compare responses after a mailing is sent.
Thanks in advance for any help and ideas!
- Christine
Hi Christine,
Within each value, over the course of each Week prior to Performance, you're looking to access the total sales a divisor in a % Total Sold calculation, yeah?
Within each Week, you can override the Week grouping using the ALL() function. For example ( [Total Ticket Count] , ALL( [Order Weeks Prior to Performance] ) ). This will return, in every Week Prior to Performance, the same Total Ticket Count across all Order Weeks Prior to Performance.
Maybe this offers a simpler way to your desired results.
RSUM( [Total Ticket Count] ) / ( [Total Ticket Count] , ALL([Order Weeks Prior to Performance]) )
Thanks, Chris! That simpler formula does read a little better, and I was easily able to add my fiscal year filters to it to create the different comparison lines on my chart.
Christine Wingenfeld,
If you have a moment to share a .dash file would love to see what you have come up with.
--Tom
I played around with this formula a bit more and tried out some different use cases - here's a .dash file with those examples for others to play around with and reference:
RunningSumasPercenttoTotalExampleWidgets.dash
The original intent of this formula was for a larger dashboard project comparing the Fall seasons pre-pandemic and after reopening this past year. I'm also happy to share that .dash file if anyone might be interested, though it's still a bit of a work in progress.