The answer to your specific question is that the average of 1 is 1.
Remember that the "Number of Records" is essentially a column in your data sources that is "1".
.. the key is .. but using "daily" census .. that is a totally different measure/column in your data source.
Sorry, I'm still confused. If I have 132 records in a month and I want to know what's my average per day I used to be able to select AVG versus SUM. This no longer works.
I see another calculation called "Avg number of records" ... what is that? Maybe someone has already created the calculation you need.
To get the average number of records PER DAY, you need to count the number of records in a month and then divide it by the number of days in that month. You can't expect Tableau to know when a user wants a per DAY average versus a per MONTH average versus a per RECORD average.
Something like this (i'm doing this from memory with no Tableau Desktop to test on):
// total number of records each month
SUM([Number of Records])
// divide by the number of days in the month
// get the days in the month by counting the difference in days between two dates
// The second date is the original date with one month added to it.
SUM(DATEDIFF('day', DATETRUNC('day',[Date]), DATETRUNC('day',DATEADD('month',[Date],1)))
Christine, is your data one row per patient or multiple rows per patient? If its multiple rows per patient, taking the average of the sum of records wouldn't get you the desired result. When you use Tableau default number of records, its just counting the number of rows in your data set. It doesn't explain why the calculation worked before and not in the new version.