Hi Man, thanks for another great content. Two ideas: 1) in measure Bins Filter , variable _BinsToKeep you could use MOD() = 0 2) in measure Nr of Products within Bin , var _SalesNr - instead of the IF in the iterator maybe you could just leave the boolean expresion (the test) and the SUMX will convert implictly TRUE/FALSE to integers. Or just wrap the logical test in INT() function. I suspect that using IF in the itreator may slow down the measure when there is big number of products. cheers!
I followed your video and implemented it for my report to see the distribution of the task completion time for 3 separate workflows using a custom field toggle. The dynamic bin allows users to adjust the bin size with each tick of 5 minutes increment. I don't see how I can turn in the bin label switch but I used the value detail instead and it works great. It's inspiring....Thanks 🙏
Excellent video, Bas, as always. This graph is very useful for visualizing statistics of quantitative variables and with all the tips you leave us, it will alleviate the task of constructing and visualizing it properly. Thank you very much, Bas.
Hey @Bas, an option for a smaller or narrower column chart or with more columns where the custom label is not visible could be to add it as a tooltip and keep the starting bin as the x-value.
Hi Bas, this video was perfect for my needs. I've been analyzing the time it takes to complete tasks, with the x-axis representing different time units like calendar days, business days, weeks, months, quarters, and years. I believe integrating aspects of your approach will improve the simplicity and output of my application. A change I would make to your approach is not removing all columns with zero values, but rather removing columns greater the maximum number of sales for a product. For instance, if the 25-49 range has zero product counts, but 50-74 has some, it's essential to retain the 25-49 column to avoid the visualisation becoming misleading. A couple of videos that would be really helpful for me: Percentile chart, showing the cumulative percentage of products sold for each bin as a line overlaid on the histogram, with percentages on the y-axis. Using something like the PERCENTILERANK function in Excel (no such function in Power BI). Dynamic y-axis with calendar days, business days, weeks, months, quarters, and years.
thanks David 😊 the bins wouldn't disappear, but your approach could also work. I like the cumulative line idea thx! for the dynamic axis check some of the fields parameter videos on the channel :)
Adding a BLANK() measure to a visual just to unlock a Grand Total is some next level black magic. Microsoft officials should watch your guides and make these kinds things possible in a normal way. ;) Also, the histogram is great, but I would still go for a calculated column. Enumerating the products in the bin is valuable, but identifying them is even better! Parameter based X-Axis would not provide us a filter context to perform the drill-through unless we cheat and create some additional SUMMARIZE Table of dim Product which would be connected to Bins Filter, having Nr of Products as a value... That would probably work, but it is an overkill IMAO. Could that be done easier?
if you can have a calculated column that gets the best bin size then I would go for that indeed - not always possible though. The drillthrough you could achieve with a filter measure on the drillthrough destination visuals .. that sounds like a video on its own :p
Hi Bas, I'm a huge fan of your tutorials. As a junior Power Bi user I was wondering how you added that sidebar in Power Bi desktop to quickly select the panes for data, visualizations etcetera? I really would like to add that to my Power Bi desktop.
Hello Bas, thank you so much for all your videos. Can I ask a question, how to make the histogram to react on other visuals? Such as, if I click on one bar that shows 44 sales orders, can I check which 44 sales orders are?
Thanks for the video I have a scenario where i want to show 0-10 till 90-100 but afer that if count is > 100 the last column will be > 100 in histogram Can we still make histogram bin dynamic is there any way to add that >100 bin column
Some have already cautioned about this but I thought I'd resatte a potential problem with this visualization. To remove the blank columns in the chart to the right of the maximum value, Bas removed the zero from his if statement which will return blank when False (20:25). But that applies to all columns, not just the ones greater than the maximum value. Therefore bins in the reported range with a frequency of 0 won't be reported. For example, say we have the following frequency count: 0-49: Frequency = 10 50-99: Frequency = 20 100-149: Frequency = 0 150-199: Frequency = 5 200-249: Frequency = 0 250-299: Frequency = 0 .... The graph will only report the follwing bins: 0-49: Frequency = 10 50-99: Frequency = 20 150-200: Frequency = 5 You solved the problem of endless empty columns to the right but created a much more serious problem in the process. Not sure how you'd solve this, probably by calculating the range. Two other considerations while I'm here: 1) You need to know what your possible range will be which isn't always possible; 2) Stragglers/Outliers are going to extend your histogram to the extreme left/right. There's a need for "Bin_Start+" label. Things I think everyone should keep in mind but still by far the best dynamic histogram chart video I've seen! It's crazy that histograms aren't a default visualization from Power BI.
Hi. I see your point, but actually modifying the if condition made the non used bins values blank, thus they will not show. In your example, the values are zeros, these will show. If you recall, when the is condition was returning zeros, they were visible.
This worked awesome!!! Just one issue I am having, if I use a slicer to select a product group, any products not part of that group show up as 0 order qty and get dumped into the "0" value on the histogram. I've tried applying a filter to exclude 0 for order qty but it doesn't return the desired result. Is there an easy fix for this that I am missing?
You removed the "right part" of the chart up to 1000 where you didn't have data by removing the "0" in the condition. I guess that would also remove bins in between with no orders. Can you think of a way to keep those gaps?
i have this working on my report for whole numbers but seems to have issues when i tried to used decimals. seems to be any issue somewhere in the Bins Filter measure with the rounding step possibly. anybody try this with decimals and have any luck?
Sometimes you might want to use words that are reserved by PowerBI. A word like "measure" could not be used as a variable name, because that word is reserved. However, the name _measure could be used. Adding the underscore is just a good practice to avoid issues with this.
@ 18:24 I guess you should not deduct 1 from _BinSize_End because in the SUMX filter condition you have ... < _BinSize_End; the change should be OK for
Your way of sharing is fantastic, and this is another amazing example of that - well done! I wonder if you could assist with the same concept, but I'm interested in seeing price bands over time. Example, if we have (dynamic) bins in increments of 100 and we have selected 3 years of data, I would like to see a column for each year for sales where the price was 0-100, then columns for each year for sales with prices of 100-200 and so on. I would like to be able to drill down from year to quarter to month, but let's just start with the year granularity to keep it simple. Not sure how to do this.
Easily my new favorite Power BI RU-vidr. Like this, any time I'm working on a problem, it seems like Bas comes out with the solution for it with new content. Thank you!
How to set tabs shortcuts icons on the right side of pbi desktop ( I mean visualisation, data, bookmarks etc. ) ? I use pbi on pro license and I don’t know how to do that.