Calculating Monthly/Yearly "Daily" Patient Census

[jdugas]

I have a need to calculate the monthly daily patient average for my company. I have a table that list, on a daily basis, the patients that are on service with us. I can display the daily census number without issue using a distinct count on a concatenated calculated column but I'm struggling with is showing the monthly daily average of the distinct count.

Example:

January 1st the patient count is 2546. On January 2nd the patient count is 2541 on January 3rd the patient count is 2530....and so on. I would like to sum the patient counts and divide by the number of days in the month to give me a daily monthly average. Also need to do this on a daily yearly average.

I'm brand new to this so any help or ideas is appreciated. What I've search online so far hasn't help so I'm asking the community.

Thanks Jeremy

[KGrice]

It sounds like you'll need an AVERAGEX function. The AggregateX functions (SUM, MAX, MIN, etc.) can be used to perform a calculation at one level and then bring that calculation up to another level. For example, we can calculate the count of patients at the daily level and aggregate that at the monthly level. Without knowing more about your data structure, here's an example of how this might look:

Data structure:

Date            Patients

8/1/2016     51

8/1/2016     20

8/2/2016     4

8/2/2016     31

...

Measure to get a sum of patients at any level:

Patient Count = SUM(TableName[Patients])

Measure to get an average of patients counted at the daily level, including by month:

AVERAGEX(VALUES(TableName[Date]), [Patient Count])

Here's an example of the output at a daily level for an entire month:

And here it is at a monthly level:

[jdugas · Accepted solution]

Thanks. I was able to solve this by using countrows divided by distinct count.