Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
leobelyaev
Frequent Visitor

Frequency Distribution of Daily counts

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:screenshot.png

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.

3 REPLIES 3
leobelyaev
Frequent Visitor

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? 

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
Super User
Super User

@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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.