Forum Discussion

lvanam's avatar
lvanam
Frequent Visitor
9 years ago
Solved

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: <10...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

     

lvanam's avatar
lvanam
Frequent Visitor
9 years ago

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.

Baskar's avatar
Baskar
Resident Rockstar
9 years ago

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 !!!

 

  • lvanam's avatar
    lvanam
    Frequent Visitor
    9 years ago
    Now, should I order the Range column by order column?
    • Baskar's avatar
      Baskar
      Resident Rockstar
      9 years ago

      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's avatar
        lvanam
        Frequent Visitor
        9 years ago

        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 :(

         

         

  • RY33's avatar
    RY33
    Frequent Visitor
    7 years ago

    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