Forum Discussion
Histogram - User defined bin size
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: [email protected].
Meanwhile, you can work around the issue use “Stacked column chart”, below is the sample:
Data table: Name, Amount(0~5000)
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.
Regards,
Xiaoxin Sheng
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: [email protected].
Meanwhile, you can work around the issue use “Stacked column chart”, below is the sample:
Data table: Name, Amount(0~5000)
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.
Regards,
Xiaoxin Sheng
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?
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?
Anonymous BhaveshPatel Anonymous ankitpatira v-haibl-msft Baskar
Thank you in advance.
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 !!!- Now, should I order the Range column by order column?
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 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.
