A VISUAL Every Analyst Should Know | HISTOGRAM with Dynamic BINS in Power BI

[@HowtoPowerBI]

Learn everything I know about designing Power BI reports datatraining.io/powerbidesigntransformation

[@ukaszbalcerzak3191]

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!

[@ajinkyatrimukhe]

That last part of bins lables was unreal.

[@HowtoPowerBI]

😁😊 you watched all the way to the end !!? extra thanks to you! 😉

[@elizascience7171]

I'm thinking of a different approach for my beginner level. Make Groups of 25, Groups of 50, Groups of 100, etc. Then make field parameters to get a slicer on the group sizes and voila, the user can select the bin size. I've tested it and it works!!! even though the sorting just defaults to the static Y axis, but I needed that anyway, so works for me. thanks so much for the inspiration, you've sparked my imagination.

[@pk231]

You are back with the full flow bro. Kudos for making such extraordinary contents.

[@HowtoPowerBI]

😊More to come! thanks for watching them

[@BobbyRauch]

Easily my new favorite Power BI KZbinr. 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!

[@calinranger1291]

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.

[@ahmadhassanbakr]

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.

[@worachartchantakraiwat5002]

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 🙏

[@yaynative]

I'm gonna have to watch this one 2 or 3 more times.

[@mnedix]

If only my management wanted this kind of analysis. Kudos for the video, I saved it and I will practice it soon :)

[@alexgry4763]

Same issue friend! We can create great things, but all they want is a f*cked up E-Mail with some pdf files and an Excel spreadsheets attached...

[@HowtoPowerBI]

lol let me know if you have been able to convince them :D

[@ilfacone]

phenomenal. Im becoming a fanboy of yours Bas. Just great work as always.

[@Schnappy21]

Amazing video dude! Love those little tricks you use in visuals and data labels. I always learn a lot watching.

[@rigobertogarcia]

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.

[@ibrahimalshweair7866]

Thanks Baus as always. I would’ve love it more if you had the last bin open ended, like “more than 300” for example.

[@HowtoPowerBI]

yea that of course works too :)

[@DJadject]

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.

[@HowtoPowerBI]

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 :)

[@emmafeng55]

This is great! Small but very helpful tip for power BI user. Didn’t think of that before. Thanks for sharing ❤

[@stephencross4978]

Awesome solution and you also helped me with another issue I was having

[@HowtoPowerBI]

now I'm curious :p what was the other thing .. don't leave me hanging here ;)

[@rukkyneel2174]

No words! Just great, awesome and marvelous. Great intelligence. Thanks brother.

[@HowtoPowerBI]

👊😎😊

[@jeroenjong9802]

Amazing, love your way of presenting, Bas!

[@HowtoPowerBI]

so nice to hear that 😊 thank you for watching Jeroen!

[@dishantkothari8937]

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

[@centugurdag7776]

now i have seen the smallest attachment icon at the right top corner.thank you

[@Fernando_Calero]

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.

[@juanignacioXI]

Simply outstanding Bas!, thanks a lot

[@ahmadhassanbakr]

Bas, great video as usual. Thanks.

[@andrewnewey9354]

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.

[@HemanthKumar-lb4xt]

Another awesome technique... 🙂Thanks for sharing

[@dr_zieb]

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.

[@Aquaelus]

This is just Amazing. Im hyped already in my next Report to try that out myself. Thank you 🙏

[@HowtoPowerBI]

Best of luck!

[@shanmugavelayutham1394]

Thank you Master ❤

[@HowtoPowerBI]

You are very welcome

[@alexgry4763]

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?

[@mcnater]

agree here...good point.

[@HowtoPowerBI]

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

[@PeterKontogeorgis]

Top bins as usual Bas!! 😂😂😂

[@martithon]

Would a more efficient way of writing the filter measure be something like: INT(MOD(BinStart, BinSize)=0) ?

[@Daithi354]

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?

[@MrRobvanzutphen1]

Awesome as always!!

[@shahzadkheros]

Impressive work

[@HowtoPowerBI]

💪👀

[@krahul456]

Wonderful Tutorial !!! Thanks a Lot !!

[@HowtoPowerBI]

Glad you liked it! thanks!

[@detritus666]

Super clever solution

[@nlvq]

Great video here lots of tricks in one tutorial -

[@HowtoPowerBI]

thanks for watching 😊

[@datayoda74]

Excellent video

[@DapperDude19]

Amazing Video. Lot of leaning

[@HowtoPowerBI]

thanks for watching! :D

[@centugurdag7776]

does it have to be that complicated to download the data set ????, i got membership, but i could not find the data set of this histogram video ?

[@Rogustofeles]

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.

[@RobertoMassucco1978]

It's a preview feature that you could enable

[@HowtoPowerBI]

you need to go to file, options, options and settings, preview features, and check it on there

[@Rogustofeles]

@@HowtoPowerBI got it! Thanks! I love your videos, helps me soo much in daily work.

[@mattjacob7134]

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?

[@caiquebeck]

Very, very, very nice tip... I'm looking for something like this... "Transmimento de pensação" - do not try translate it. Thanks a lot, Bas, for all!

[@ssomtom]

Fantastic

[@krcarlosm]

I having trouble to understand why to put the "_" before the variables. This is something useful?

[@dutch-man]

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.

[@doriginal69]

I like this but I want the through number to be below the bars, not on the bars. This would be great for a 30, 60, 90 rpt.

[@mazchen]

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?

[@mihaizamfirescu5357]

@ 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

[@mrutyunjayapradhan3599]

Great ❤

[@HowtoPowerBI]

thank you 😊

[@mazchen]

18:31 why do you need the "-1"? Isn't the "

[@maryammoghadam1860]

Thankssss

[@sandyag907]

Amazing tricks