Forum Discussion
Bar Chart with Dynamic Floating Points Numbers on X-axis
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.
Idendlin, just wanted to check in to see if you (or anyone else, of course) had any ideas on this.
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
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!
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.
