cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdugas
Helper I
Helper I

Calculating Monthly/Yearly "Daily" Patient Census

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

 

1 ACCEPTED SOLUTION

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

View solution in original post

2 REPLIES 2
KGrice
Solution Sage
Solution Sage

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:

 

AverageX.PNG

 

And here it is at a monthly level:

AverageXMonthly.PNG

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

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.