How to Create Custom Histogram / Bar Chart Based on Measures

[Anonymous]

Hi there!

Is there a way to create a histogram based on 2 measures? I have a [Total Orders] measure, which counts the number of distinct order numbers (text data type). I also have another measure for [Total Customers], which counts the number of distinct customer IDs (text data type).

I would like to create a histogram or bar chart which could show the count of customers that fall within a range of order counts. The "range of order counts" would be like bins, where you have e.g. 0-10, 11-50, ... , 1500-2000, etc. 

I'm thinking your "Values" pane in a clustered column visual would be the [Total Customers] measure, and your "Axis" pane would be the bins mentioned above. Not sure how to execute on this though!

[samdthompson]

Hello try this:

1. click on the three dots in the Fields pane for the field Orders (dont worry about the measure). 

2. selact new group and let PBI create the Bins for you.
3. Use that new grouping on the x axis and put the customer count measure in the values. 

[Anonymous]

My order ID field contains both text and a number e.g. R123456 and/or 1234-E, etc.

Is there a way in which I could pull out the numbers from that field in a new column or create a unique list in numeric format? Some order IDs repeat across multiple lines.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You need to create a table with the bins :0-10, 11-50, ... , 1500-2000 the create a measure by summarize to group your measure vlaues into these bins. you can refer to the similar post:  https://community.powerbi.com/t5/Desktop/How-to-create-bins-for-measure-urgent/m-p/371730

And there is a good blog for your reference: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi @samdthompson - I've tried that, but because my order ID field is a text data type the grouping option does not include bins. It only has the "lists" grouping option