Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rexii23
Advocate I
Advocate I

Histogram with slicers, dynamic bins, min and max

I'm struggling to create a histogram to meet my requirement.

 

 I have a table of data:

 

CategoryValueQty
A9-192
A8-103
A648
A1198
A3120
A2481
A5-324
A2442
A2489
B156-246
B258-216
B295-276
B257-151
B26448
B115-82
B289150
B114-128
B270-126
B222-174
B139-73
B276417
B236-400
B289-169
B214-392
C1121321
C2778-123
C2875-2000
C1176432
C1930418
C2116-398
C1029151
C1633-187
C2313-125
C165541
C2892-52
C179562

 

 

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.

 

1 ACCEPTED 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.