Forum Discussion
Microsoft EmployeeBar Chart with Dynamic Floating Points Numbers on X-axis
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.
9 Replies
- lbendlin
Super User
what you have is a column chart, not a bar chart. Try a bar chart instead - there you can use your measure.
MomoJengThanks, 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.
