How do I calculate Average Daily Census on this data?

[Anonymous]

Here is a subset of the data table.  I need to be able to calculate the Average Daily Census for each program, based on the rows in this table.  I am not a programmer at all so I have no idea how to write this.  The table name is Globa28DailyCensusMA.  For the CSU program, I need to break it up into Child =<17 and adult =>18.  Of course Power Bi divides the date up into three separate columns for the visit_start_date. Any help would be appreciated.  The formula for this is: 

Avg Daily Census = Calculate(CountRows(Global28DailyCensusMA),Filter(Global28DailyCensusMA,related(visit Type[Global28DailyCensusMA])="Daily Census"))*1.0/[Number of Days]

I tried doing this but it doesn't work.

visit_start_date	Client ID	Client Age	Program	Visit Type
7/1/2019	2122	55	CSU	Daily Census
7/1/2019	3262	60	CSU	Daily Census
7/1/2019	4894	22	CSU	Daily Census
7/1/2019	6331	25	CSU	Daily Census
7/1/2019	6639	32	Detox	Daily Census
7/1/2019	6945	29	CSU	Daily Census
7/1/2019	7404	36	Crossroads	Daily Census
7/1/2019	11203	19	CSU	Daily Census
7/1/2019	12670	40	Crossroads	Daily Census
7/1/2019	13184	64	Detox	Daily Census
7/1/2019	14028	33	Crossroads	Daily Census
7/1/2019	18671	29	Crossroads	Daily Census
7/1/2019	19035	24	Crossroads	Daily Census
7/1/2019	19094	40	Crossroads	Daily Census
7/1/2019	20773	26	CSU	Daily Census
7/1/2019	22357	49	CSU	Daily Census
7/1/2019	23133	19	CSU	Daily Census
7/1/2019	25436	26	Detox	Daily Census
7/1/2019	26405	35	Crossroads	Daily Census
7/1/2019	28881	31	CSU	Daily Census
7/1/2019	29432	36	Detox	Daily Census

[dax]

Hi rdavis61,

If possible, could you please explain this in more details("For the CSU program, I need to break it up into Child =<17 and adult =>18.  Of course Power Bi divides the date up into three separate columns for the visit_start_date. Any help would be appreciated.  ")?  In addition, you also could inform me your sample data and expecting output, then I will help  you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cmcmahan · Accepted solution]

Luckily, you don't need any prgramming knowledge to get this.  If you right click the table Globa28DailyCensusMA, and select the New Quick Measure option, you'll launch a helpful wizard.  You want an average by category, with that category being the date.  The base value would be a count of any column from the table.  

That will create the measure for you! Mine looked like this:

Average Daily Census = 
AVERAGEX(
	KEEPFILTERS(VALUES('Table'[visit_start_date])),
	CALCULATE(COUNTA('Table'[Visit Type]))
)

I put that in some table visuals, filtering them by the age of the person, and came up with this from your example data. Try it with your data, and see if you come up with what you're looking for!

[Anonymous]

This worked perfectly!  Thank you so much.