Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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!
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.
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!
This worked perfectly! Thank you so much.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
68 | |
68 | |
43 |
User | Count |
---|---|
146 | |
106 | |
105 | |
90 | |
65 |