I am struggling to create a frequency distribution.
I am using data where each row is a 'patient visit' under PAT_ENC_CSN_ID, the ID for each visit is unique Here's how the data looks:
What I need to create is a frequency distribution, that will display the number of times a a certain number of visits occured.
My X-axis should show 'bins' for number of daily visits, and my Y-axis should represent the count of times a certain number of daily visits occured.
So, for example, one bar on the bar-chart would show 50-100 on x-axis, and 146 on y-axis.
This would mean that for my entire data set, there were 146 days with 50-100 daily visits.
I am struggling to get the barchart to bin dynamically for me, and I can't create a meaningful 'new group' using 'PAT_CSN_ID', because they are ID numbers, not real values. Creating a dummy variable column where each ID=1 to count them also doesn't help, since the entire column is just 1's. I am trying to figure out and learn DAX and Calculated tables, which may be the path forward, but I am not sure. The Idea seems to be simlpe enough, but I can't the visuals to work.
Any help would be greatly appreciated!
P.s.: I also need to be able to slice/filter by Day of week (Monday, tuesday, wednesday), FAC_ID (facility) and ENC_TYPE_TITLE (Visit type), which would be the next step in building my dashboard. That is why I don't want to 'hard-code' my bin sizes, and do it dynamically. Because at different levels of data (facility level/doctor level), the bins would have to be different sizes.
I watched your tutorials, but I'm not sure How to apply that to my data. In each tutorial you use New Data to create a new table to store the measures you create. Do you have a suggestion as to what kind of measure I should create in my case? In your example, you hardcode the buckets to have specific limits, for example: 0-0.1, or 0.1-0.25. Is there a way I could do the binning dynamically? Or create a mesure that automatically chooses bin size based on the data?
So that for example, for 1 month my count range is 10-100, but on 1 year scale my count range would be 500-1000, so I can't hardcode the bins with exact numbers, ideally I would want Power BI to look at the range and create 10 bins automatically.