Frequency Distribution of Daily counts

[leobelyaev]

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.

[leobelyaev]

Hi Amit, thank you for helping me again!

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? 

[leobelyaev]

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.

[amitchandak]

@leobelyaev , You need to add Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q