Create a Distribution chart by bucketizing a measure

[RustyNails]

Hello friends. Imagine my scenario as part of Supply Chain data where users are ordering items.

In this table, I created "Average (days)" as a measure that calculates the average days it takes for project from Task 1 to Task 2. The way the calculation works is that it is dependent on user Task selection. In this case, the user selected 5 tasks. The duration will calculate between the preceding task and the immediate task. For example, it takes pjid_1 about 15 days from Product Ordered to Product Approved. Then it takes about 8 days from Product APproved to SKU Received. 28 days from SKU Received to SKU Picking, and so on and so forth. Now imagine thousands of Products ID's with hundreds of Tasks.

My goal is to create a distribution chart using Bar or Line that tells me number of Products in a bin. For example, there are 20 products that fall in the bucket of 0 - 10 days from Product Ordered to Product Approved Average Days. There are about 35 products that fall within 21 - 20 days. 18 products that fall within 21 - 30 days, etc. I tried creating a custom column that did a simple if average > 0 && average <= 10 then '0 - 10' type conditions, but my PBI freezes when I attempt this. Is there a better way of binning these measures based on product count? Finally, the question of user selection of tasks. Ideally I would want PowerBI to show me multiple charts if user selects multiple task (For ex, Chart 1 is Task 1 to Task 2, Chart 2 is Task 2 to Task 3, etc). But I can work with superimposition or only restrict it two tasks for now.

[Daniel29195]

@RustyNails 

cant you create the average as calculated column in your table . 

then use the create group builtin in feature in power bi to create the bins base on the calculated column. 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[RustyNails]

Actually I cant. The logic to check user selection and get the average duration only works as a measure.

[gmsamborn · Accepted solution]

Hi @RustyNails 

I created a bucket table with 3 columns: Bucket (ie. 0-10), Min(0) and Max (10) for each bucket.

I wrote 1 measure.

Count = 
    COUNTROWS(
        FILTER(
            ALL( 'Table' ),
            'Table'[Average (days)] >= SELECTEDVALUE( 'Buckets'[Min] )
                && 'Table'[Average (days)] <= SELECTEDVALUE( 'Buckets'[Max] )
        )
    )

The ALL() might have to be changed to ALLSELECTED() depending on your needs.

Let me know if you have any questions.

Create a Distribution chart by bucketizing a measure.pbix

[RustyNails]

Thank you! that worked.

I'm trying to visualize this using bar charts. Would you be able to know how to create bins? Like for example, I want user to select bins of 10, 20, 30, 30 etc. Is there a way to show this? Also what if the duration between two tasks is like say 1000 days. 

thanks