Hello,
I've been tackling this question with one of my colleagues.
We've been trying to answer the question, for a particular membership organisation. What are the average daily sales of this membership each month?We've been able to come up with this:
It’s filtered for years 2018-2024 and the member org in question
The average Sales value on the left is: count([Constituent ID])/6/30.436875
6 is used as it's the number of years the membership org has been on sale for.
30.43 is used as it's the average number of days in a month.
... but I don't like the fact I have to manually change the number of years the scheme has been in place and for 50% of the year isn't accurate, and I don't like using an average number of days a month.
I've had a go using a pivot table
Which does give me a number that I can use as the average sales per day in that month. The only slight caveat is when there are 0 sales in a day, the subtotal average is excluding these and not treating them as a 0, which skews the averages and makes the numbers innacurate.
I've tried wrapping the value for #of unique constituent IDs in a case statement, but it doesn't seem to fix it.
There must be a better way! Can anyone help?
Hi George,
I'm not sure we can get you quite as far as you're trying to go, but a couple of thoughts...
You may be able to replace your division by 6 with [# unique Calendar Year] in which there were membership sales, assuming you have sales in every year since the membership organization was on sale. This value would then adjust as you change membership organization.
For the number of days in a month we could get closer with something like
( [# unique Calendar Day of Month] , ALL( [Membership Organization] ) , ALL( [Calendar Year] ) )
This formula will produce a count of unique [Calendar Day of Month] for the given month, but will look at purchases across all membership organizations and calendar years. Only days of the month with a sale against them will be returned, so if we include not just days in this month and this year that have sales for this membership organization, but days in this month that have sales in any year or membership organization we're far more likely to get back all the distinct days of this month.
COUNT ( [Constituent ID] )/ ( COUNT ( [Calendar Year] ) * ( [# unique Calendar Day of Month] , ALL( [Membership Organization] ) , ALL( [Calendar Year] ) ) )
Hey Chris, that's a very neat way of solving this one, thank you very much!