Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm struggling to create a histogram to meet my requirement.
I have a table of data:
Category | Value | Qty |
A | 9 | -192 |
A | 8 | -103 |
A | 6 | 48 |
A | 1 | 198 |
A | 3 | 120 |
A | 2 | 481 |
A | 5 | -324 |
A | 2 | 442 |
A | 2 | 489 |
B | 156 | -246 |
B | 258 | -216 |
B | 295 | -276 |
B | 257 | -151 |
B | 264 | 48 |
B | 115 | -82 |
B | 289 | 150 |
B | 114 | -128 |
B | 270 | -126 |
B | 222 | -174 |
B | 139 | -73 |
B | 276 | 417 |
B | 236 | -400 |
B | 289 | -169 |
B | 214 | -392 |
C | 1121 | 321 |
C | 2778 | -123 |
C | 2875 | -2000 |
C | 1176 | 432 |
C | 1930 | 418 |
C | 2116 | -398 |
C | 1029 | 151 |
C | 1633 | -187 |
C | 2313 | -125 |
C | 1655 | 41 |
C | 2892 | -52 |
C | 1795 | 62 |
The page has a slicer with Category as the slicer Field.
I need to create a histogram that allows me to:
-use the min and max values in each Category as the limits
-place Value into bins on the x axis
-place the sum of Qty on the y axis
- use a dynamic bin selection formula such as http://blog.gnetgroup.com/bi/2012/07/09/create-a-bi-histogram-with-dynamic-bins-using-dax-in-powerpi... OR specify the number of bins using a what-if parameter
Unfortunately, the Groups feature currently does not give me the flexibility above. I'm also unable to create measures and place in into 'Axis' field of the chart, since only calculated columns can be placed into non-value fields on a visual. The histogram visual on the store does not work with negative numbers.
I really hope this can be achieved with DAX instead of Power Query.
My current solution is a major workaround - using R to plot a histogram since any input can be placed into R, including measures. It works but doesnt not allow me to cross filter like a standard PBI visual.
Solved! Go to Solution.
HI @rexii23,
Maybe you can use summary function to summary your records to custom category range, then use column chart with above custom category to create visual.
For sorting requirement, you can use sort by column feature.
Sort by column in Power BI Desktop
Regards,
Xiaoxin Sheng
HI @rexii23,
>>The histogram visual on the store does not work with negative numbers.
I'd like to suggest you to submit an idea about add support with negative numbers on custom visual histogram.
Regards,
Xiaoxin Sheng
Is it possible in any way such as via a calculated table to turn these measures to columns so that the data can be visualised via the existing bar/column charts?
HI @rexii23,
Maybe you can use summary function to summary your records to custom category range, then use column chart with above custom category to create visual.
For sorting requirement, you can use sort by column feature.
Sort by column in Power BI Desktop
Regards,
Xiaoxin Sheng
Yes, I got this working by using a summarized calculated table and inner joins. Thanks
Another feature I wanted to implement is to use the 'what-if' paramters to allow user selected bin sizes. From my research, I have concluded that there is no way to do this since calculated tables are initialised once and there is no way to put a measure into a calculated column for this idea to work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |