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.
@ 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
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.
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.
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 RUclipsr. 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!
![Business Analyst Full Course [2024] | Business Analyst Tutorial For Beginners | Edureka](http://i.ytimg.com/vi/czymrnQV2p4/mqdefault.jpg)
Learn everything I know about designing Power BI reports datatraining.io/powerbidesigntransformation
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!
That last part of bins lables was unreal.
😁😊 you watched all the way to the end !!? extra thanks to you! 😉
I'm gonna have to watch this one 2 or 3 more times.
You are back with the full flow bro. Kudos for making such extraordinary contents.
😊More to come! thanks for watching them
Bas, great video as usual. Thanks.
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 🙏
Thank you Master ❤
You are very welcome
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.
phenomenal.
Im becoming a fanboy of yours Bas. Just great work as always.
Thanks Baus as always. I would’ve love it more if you had the last bin open ended, like “more than 300” for example.
yea that of course works too :)
If only my management wanted this kind of analysis. Kudos for the video, I saved it and I will practice it soon :)
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...
lol let me know if you have been able to convince them :D
Amazing video dude! Love those little tricks you use in visuals and data labels. I always learn a lot watching.
No words! Just great, awesome and marvelous. Great intelligence. Thanks brother.
👊😎😊
Awesome solution and you also helped me with another issue I was having
now I'm curious :p what was the other thing .. don't leave me hanging here ;)
Amazing, love your way of presenting, Bas!
so nice to hear that 😊 thank you for watching Jeroen!
This is great! Small but very helpful tip for power BI user. Didn’t think of that before. Thanks for sharing ❤
Top bins as usual 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.
Simply outstanding Bas!, thanks a lot
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?
agree here...good point.
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
Another awesome technique... 🙂Thanks for sharing
Impressive work
💪👀
now i have seen the smallest attachment icon at the right top corner.thank you
This is just Amazing.
Im hyped already in my next Report to try that out myself.
Thank you 🙏
Best of luck!
Wonderful Tutorial !!! Thanks a Lot !!
Glad you liked it! thanks!
Great video here lots of tricks in one tutorial -
thanks for watching 😊
Awesome as always!!
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.
Super clever solution
Amazing Video. Lot of leaning
thanks for watching! :D
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?
Would a more efficient way of writing the filter measure be something like:
INT(MOD(BinStart, BinSize)=0)
?
Excellent video
Thankssss
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.
@ 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
Amazing tricks
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.
Great ❤
thank you 😊
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?
I having trouble to understand why to put the "_" before the variables. This is something useful?
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.
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.
It's a preview feature that you could enable
you need to go to file, options, options and settings, preview features, and check it on there
@@HowtoPowerBI got it! Thanks! I love your videos, helps me soo much in daily work.
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 RUclipsr. 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!
Fantastic
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 ?
18:31 why do you need the "-1"? Isn't the "