I was doing some online shopping last week and saw a price slicer with a histogram to show the concentration of price points. I thought it was pretty cool, so decided to see if I could build this in Power BI. I came up with a solution that works, and in this article I show you how you can do it yourself.
Slicer with Histogram
Here is the final product (below). Note how the histogram shows the price points, including those that have not been selected with the slicer.
For illustration, I used the Products table in the Adventure Works database with the List Price in the slicer. When you select the list price range, the histogram updates to show the price range selection.
How to Create a Slicer Histogram
This is how I created the slicer with histogram.
- Placed a slicer with List Price as the Field. The price list field is numeric, so the slicer is a “slider” by default.
- Placed a column chart with List Price as the Axis and Product Key as the Value (count of product key).
- Placed the column chart below the slicer and adjusted the size to align with the slicer.
- This histogram displays the number of products in the selected price range
- Duplicated the histogram, changed the column colour to grey, and turned off edit interactions for this copy.
- Placed the grey copy of the histogram behind the main histogram.
I have produced a short video showing you the end to end process here.
The Video
You can download the Products Table here.
Thanks for this nice tips. On Bi 2020 Jan., even after specifying X axis limits manually, Bins widths keep changing dynamically on slicer change. Any idea how to prevent bins width from changing?
In your case with the amount of data you have, it does not trigger bins width change I guess, but with fewer data it probably will.
Great idea
hi, can u give me few scenarios, to prepare dashboards
To make the histogram axis dynamic, create calculated table to your model with DISTINCT([histogram axis value]), and add a relationship back to the fact table. Then set the histogram axis to the newly created column instead of the one you use to filter, and axis to “show items with no data”. Now you don’t need to manually set the axis min/max to match the filter values anymore.
Good idea
Another trick: this can be extended to work with line charts too.
You have to use the ‘Line and clustered column chart’ visual and then add the measure twice; once as a column value (make this colour see-through, e.g., white) and once again as a line value.
Very Nice tip
Wow. That is clever.
Your posts are always a good mix of DAX and practical presentation/Power BI functionality tips. Keep up the good work.
Would you be able to provide the sample data used to create it just so I can try it myself? I currently do not have sample data to use.
Right below the video is a link to a .csv with everything.