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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lvanam
Frequent Visitor

Histogram - User defined bin size

Is there any way to define the user defined bin sizes in Histogram?

For example I have a field called Product size that has data varying from 0 to 5000.I want to be able to divide the bins as:

<100, 100-300, 300-500 and 500+ for the histogram. If I define the custom bin size as 4, it automatically divide it into bin sizes with equal number of intervals. I want to be able to define the sizes as above. How do I do that?

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @lvanam,

 

Based on my research, we can only specify Bins with specific positive integrate values to divide with equal range. It’s not supported to custom different ranges for each column in Histogram visual currently. I would suggest you send feedback to Power BI Custom Visuals Support alias: pbicvsupport@microsoft.com.

 

Meanwhile, you can work around the issue use “Stacked column chart”,  below is the sample:

 

Data table: Name, Amount(0~5000)
 Capture.PNG

 

Add a measure to calculate the range of amount.

Range = var temp= SUM(Sheet1[Amount]) return 
if(temp<=100,"less than 100",if(AND(temp>100,temp<=300),"more than 100 and less than 300",if(AND(temp>300,temp<=500),"more than 300 and less than 500","more than 500")))

 

add a calculate column to store the “range” and create a “Stacked column chart” visual to show the result.

Capture2.PNG
 

Regards,
Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

15 REPLIES 15
asitm
Helper III
Helper III

You can use a custom visual to dynamically determine bin sizes. Either specify a number of bins or the size of each bin.

https://appsource.microsoft.com/en-us/product/power-bi-visuals/yavdaanalyticspvtltd1628223732998.his... 

 

asitm_0-1695887634559.png

 

jajohnson
Frequent Visitor

Nvm I got through. I used a column instead of a measure and removed the sum aggregation.

v-shex-msft
Community Support
Community Support

Hi @lvanam,

 

Based on my research, we can only specify Bins with specific positive integrate values to divide with equal range. It’s not supported to custom different ranges for each column in Histogram visual currently. I would suggest you send feedback to Power BI Custom Visuals Support alias: pbicvsupport@microsoft.com.

 

Meanwhile, you can work around the issue use “Stacked column chart”,  below is the sample:

 

Data table: Name, Amount(0~5000)
 Capture.PNG

 

Add a measure to calculate the range of amount.

Range = var temp= SUM(Sheet1[Amount]) return 
if(temp<=100,"less than 100",if(AND(temp>100,temp<=300),"more than 100 and less than 300",if(AND(temp>300,temp<=500),"more than 300 and less than 500","more than 500")))

 

add a calculate column to store the “range” and create a “Stacked column chart” visual to show the result.

Capture2.PNG
 

Regards,
Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

I am trying to implement this solution but the Range is not adding to visuals except in the tool tip field. Power BI is not allowing me to add it to the Axis field of the visual.

 

Anonymous
Not applicable

Hi Xiaoxin,

 

I've just implemented your solution and it worked great, thank you! I have a follow-up question: one of my bins isn't displaying on my graph's axis, because it has no data that falls into that bin. Is there a way to modify the DAX formula to have that bin appear? (I'm a relative newbie to DAX formulas, though have had some experience iwth Excel).

 

Many thanks in advance,

 

Scotty.

I did a similar thing today for different bins for 1-5,6-10,11-20, 21-30. I got the order as 21-30,1-5,11-20,6-10 on the x-axis. I need the above order. How do I get it?

 

@v-shex-msft @BhaveshPatel @Seth_C_Bauer @ankitpatira @v-haibl-msft @Baskar

 

Thank you in advance.

Baskar
Resident Rockstar
Resident Rockstar

Hi Ivan ,

I Think u got the answer , great 🙂

Try this too it will help u to understand the flow without complex If.

 

Measure or column = Order 

Example :

  Bins = Switch ( True(),

                           And( Order >1,order < 5) , "1-5",

                           And( Order >=6,order < 10) , "6-10",

                           And( Order >= 10 1,order < 20 ) , "11-20",

                           And( Order >=21,order < 30) , "21-3"

                         )

Cheers dude !!!

 

RY33
Frequent Visitor

@Baskar Your suggestion worked for me but getting same issue as other users regarding the bins order. Not clear how I can change order to go from small to large ( I have sorted the bins columns ASC in the table but it didn't reflect on the chart). Can anyone help?

Thanks

RY33
Frequent Visitor

lvanam
Frequent Visitor

Now, should I order the Range column by order column?
Baskar
Resident Rockstar
Resident Rockstar

I can't get u clearly :-(,

 

If my assumption , after the result u want to show the range in proper order like Asc or Desc ? if yes 

 

i have to create one more column with order number based on Range Output

lvanam
Frequent Visitor

Hi @Baskar

I created another column similar to Range (measure) and I gave the values such as 1,2,3,4. The idea is to sort the prior range column with Range1 column. RangeSort = var temp= SUM(Query2[Age]) return Switch(True(),AND(temp>=6,temp<6),"1",AND(temp>=6,temp<11),"2",AND(temp>=16,temp<31),"4",AND(temp>=31,temp<61),"5",AND(temp>=61,temp<121),"6",AND(temp>=121,temp<10000),"7")

 

I now get an error message "Circular dependency was detected between RangeSort1, Range1, Rangesort1 😞

 

 

Anonymous
Not applicable

Did any one got any solution for sorting the range as required? I am also facing the same problem as @lvanam mentioned. My range is coming >14, 0-1, 2-7, 8-14 whereas I need it like >14, 8-14, 2-7, 0-1.

This is a great solution and a quick way to get to have a brush function in a histogram connect to a scatterplot.

 

So the process looks like:

1. Create the Range variable based on the Range = var temp = ...  from above with appropriate bin values

2. Create a NewRange = Calculate (TableX[Range])

3. Create the Stacked bar with a Count of Name {or another field in your data table--I could use Count (Distinct)} by NewRange 

 

This looks like it will only work for one variable in your table or you end up with a circular reference, no?

 

lvanam
Frequent Visitor

Is there any way to define the user defined bin sizes in Histogram?

For example I have a field called Product size that has data varying from 0 to 5000.I want to be able to divide the bins as:

<100, 100-300, 300-500 and 500+ for the histogram. If I define the custom bin size as 4, it automatically divide it into bin sizes with equal number of intervals. I want to be able to define the sizes as above. How do I do that?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.