Bar Chart with Dynamic Floating Points Numbers on X-axis

[MomoJeng]

I would like to make a dynamic histogram, where user can filter some floating point data and specify the number of bins in the histogram.

To be specific, I have a table of 10^7 rows in which one column has floating point numbers. I want the user to be able to filter the table based on the other columns, and choose the number of bins, 1<=n<=500. Based on the value of n, and the minimum and maximum values of the filtered data, the bin ranges and counts are calculated. I've already done these steps dynamically. A sample output:

          

My problem comes when graphing the results. I want binCenter and binCount as the x and y axes of a bar plot. But since binCenter is calculated dynamically, my understanding is that it need to be a measure, which means the bar plot won't accept it as an x-axis. I can make a scatter plot with the desired data:

          

Or I can make a bar plot where the x axis is the binIndex, rather than the binCenter, and then below tell the user the range of binCenter values (which is linearly related to binIndex), and also let the user hover if they want the specific value of binCenter:

          

But neither of these solutions is visually ideal. Is there some way to get the binCenter measure as the x axis of the bar plot? It's a little weird to have calculated all the numbers I need, but only to be able to show it as a scatter plot, and not a bar plot.

To anticipate a proposed solution, I should say that I've searched around and seen some solutions for dynamic x-axes on bar plots, but all the solutions I've found involve making a column with all possible values on the x-axis in advance. I can't do this for binCenter. Since it depends on the maximum and minimum values of the selected data and n, before the user selects the filters and value of n there are (10^7 )^2 * 250^2 = 6 * 10^18 possible values of binCenter. So I can only calculate the ones I need dynamically.

[MomoJeng]

Thanks, Ibendlin. I used the terminology wrong, I actually want a column chart. i.e. the bin index on the x-axis, and the counts as vertical heights.

However, I did try using a bar chart, and that visualization also wouldn't let me drag my bin center measure to the x-axis.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[MomoJeng]

Thanks for looking at this, Idendlin! Some sample data:

Seg1	Seg2	Val
A	X	5
B	X	3050
B	X	3080
C	Y	4
C	Y	4.2
C	Y	4.2
C	Y	4.6
C	Y	7.7
C	Y	9.8
C	Y	10

I want to be able to select filters, and a number of bins (from 1 to 50), and then dynamically calculate bins and their count, based on those parameters.

For example, suppose the user selects Seg1=C, Seg2=Y, and numBins=4. The values now range from 4 to 10, so we divide the range [4,10] into four equal-sized bins, and calculate the center and then count how many values of Val lie within each.

bin_lower	bin_upper	binCenter	binCount
4	5.5	4.75	4
5.5	7	6.25	0
7	8.5	7.75	1
8.5	10	9.25	2

If we had instead selected numBins=3, we would divide [4,10] into three equal-sized bins, as follows:

bin_lower	bin_upper	binCenter	binCount
4	6	5	4
6	8	7	1
8	10	9	2

And if we had selected Seg1=B, Seg2=unfilitered, numBins=3, we would divide [3050,3080] into 3 equal-sized bins:

bin_lower	bin_upper	binCenter	binCount
3050	3060	3055	1
3060	3070	3065	0
3070	3080	3075	1

I've already written the measured to dynamically calculate binCenter and binCount, and I can show them in a table or scatter plot. Just not in a column chart.

I have 10^7 different values of Val, and so after the user selects the filters and the number of bins, there are over 10^18 possible values of binCenter, so I can't pre-compute all the possibilities, I have to calculate them dynamically.

[MomoJeng]

Idendlin, just wanted to check in to see if you (or anyone else, of course) had any ideas on this.

[lbendlin]

Not exactly sure if this will help but please check this article Dynamic Banding or Grouping in Power BI - Using DAX Measures - Choose the Count of Bins - RADACAD

[MomoJeng]

Thanks, Ibendlin! I really appreciate your taking a look and recommending that article.

That article has a fixed list of possible x-values, that they can put in a static table. It's similar to the second "solution" I gave in my original question, where I have a static list of binIndex values (similar to Age in your article). My problem is that I want to show a measure (linearly related to binIndex) on the x-axis. That is, I want the value on the x-axis to be dynamically calculated. At this point I'm guessing that's not possible, but thank you very much for your help!

[lbendlin]

At the minimum you need a prepared list of bin ids. These cannot be measures, they have to come from a column.  But it will be possible to filter/restrict how many bins to show.  Realistically anything beyond 10-20 bins won't look good anyway.

The problem is that the bin ids cannot be replaced by a different string for display.  So you are stuck with bin 1 to bin 20  on the x axis (for example).

It should be possible to create a custom visual for this scenario.

[MomoJeng]

Yep, if I show the bin idex on the x-axis, then it's definitely possible: that's the custom column chart I created and put a screenshot of in my original question. 🙂

It's good to know that I'm stuck with the bin indices on the x-axis, so I will stop looking for a workaround. Thanks!

[lbendlin]

what you have is a column chart, not a bar chart.  Try a bar chart instead - there you can use your measure.