Visualization HACK you wish you knew before... DYNAMIC BENCHMARK RANGE in Power BI

In this video I show you how to create a dynamic benchmark range area directly in Power BI. Enjoy the video and subscribe for more Power BI hacks!
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly [e-mail]
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
00:57 Forecast vs. Actuals Area Chart
02:00 Lower Limit and Upper Limit Measures
07:28 Formatting
12:31 Data Labels
15:45 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO​
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZbin: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI​ #PowerBI​ #DataTraining​
#powerbidesktop​ #powerbitraining​ #powerbideveloper​ #DAX

[@Alan.DL7]

I have no idea how do you come up with all these tricks mate, amazing!

[@evedickson2496]

So happy to have found this channel

[@owengrey3834]

This is almost exactly what I was looking for! I am currently having trouble translating this concept to create the range that would include the max and min from each month over the previous 4 year and compare the progress YTD to that range.

[@PeterKontogeorgis]

Geez.....first time I’ve come across your channel. That was beautiful. Love the attention to detail and production quality of the video. Very welcome addition to the other handful of quality power bi content!!!

[@HowtoPowerBI]

Thanks Peter! Welcome to the channel :)

[@sricharang9537]

Brilliant again Bas! Amazing trick!!

[@leandrocampolim3812]

mad respect for this channel.

[@Oivobernardo]

This solution is great! Thanks for sharing

[@ZachRenwickData]

Good job man, creative solution for this!

[@Bangles_and_Beads]

This is very helpful, thank you very much for sharing this knowledge 👍

[@LeighRadmore]

This is a top idea! Would you be tempted to create a tooltip page to overide the default so you can take out the Sales - Dummy measure?

[@MrSergb]

Very practical! Thanks a lot for the video!!!

[@HowtoPowerBI]

awesome, thx for watching 😎👊

[@naylord5]

Sweet baby Jesus, this is beautiful. Thank you!

[@twkyoscr]

Well said!😄

[@rerangelt]

Jesus Bas, you realy though it. This is some trick. And very well explained. Hi from Colombia.

[@pilarbaldominos2505]

Sooo many great ideas in a single video!

[@HowtoPowerBI]

Thank you so much! many cool things in Power BI to go... many more videos to come :)

[@nicopita77]

Amazing video and explanation !!! Thanks a lot.

[@HowtoPowerBI]

Glad you liked it Nicolas! thx for watching 😀

[@anirudhrao3e939]

Fantastic...!!!

[@viniciusfernandes3552]

Thanks bro! Great videos. Cheers

[@HowtoPowerBI]

thanks for watching Vinicius!!!! 😊😀

[@monicatanasescu]

amazing!!!! it's a great big little trick, thanks for sharing

[@HowtoPowerBI]

thanks Monica :) it came as Client request and totally got me thinking...

[@DaanDamhuis8]

Interesting video! Always wanted to see how we could create this in Power BI without custom visuals. For the Max Y values, you could also calculate it with the MAXX * 1.1 for instance, then you wouldn’t have to put it in manually

[@fabriziocau4085]

Nice one, thanks! Is there any way to overwrite the tool tips though? Other than that, the chart looks great!

[@franciscoantunes1589]

Amazing as always.

[@HowtoPowerBI]

Thank you! Cheers! 😎😊

[@pedrohenriquecarneiro904]

Amazing!

[@ramakrishnaravula4350]

Excellent

[@rauljimenez5485]

This is perfect for xMR graphs (process control, Nelson's rules, etc)!!

[@HowtoPowerBI]

Indeed 😀

[@juanignacioXI]

Amazing video Bas, does ur Sales - Forecast is just the sum of a column in your data? or it is a complex measure? Thanks in advance.

[@yekhtiari]

well done. I have to watch it again.

[@HowtoPowerBI]

thx!!! that's why the sudden boost in views 😁😂

[@sravankumar1767]

Nice explanation...........

[@jeffweir9358]

Fantastic video. Pity that Microsoft don't fix a few simple things in their product that would make bulk-standard things like this MUCH easier, including giving us the ability to set fill to 'no fill' and fixing that stupid bug whereby markers at the top get decapitated.

[@nseworld5521]

nice one, bro!!! continue watching

[@HowtoPowerBI]

really appreciate this, thanks!

[@famousjamous2006]

Nice!

[@prashantkushwahavlogs]

Cool trick. Thanks 👍

[@HowtoPowerBI]

Any time Prashant!

[@ThepExcel]

Nice trick!

[@HowtoPowerBI]

thank you!!!

[@BestICan]

Thanks!

[@HowtoPowerBI]

thank you Randy for supporting the channel!

[@yancaue6071]

Great! Thanks for the video! I was wondering if there is a way to count the values out of the range, a measure to put on a card visual. If possible, could you share it with us?

[@yancaue6071]

OutOfTargetArea = COUNTROWS(FILTER(VALUES('Date'[Date]),NOT ISBLANK([Labels]))) 👌

[@Kathirat3i]

1 Subscription from India 💌 👍

[@raghavgupta6032]

Hey, I have a requirement similar to movie revenue collection within first week of release, 2nd and so on. But as you know that release date of each movie would be different. I need to have line graph where movies on legend and on x axis week 1,2,3 and so on, y axis revenue. Would highly appreciate if you can make a video for this. Thank you

[@kingingway3308]

Awesome tip! Simply amazing. One question though, how did you plot the Actual Sales and Forecast on the same chart without breaking the chart? Usually, the Actual stops where sales stop and Forecast continues into the future. I tried to follow along but have disjointed lines after plotting Actual and Forecast measures. Please guide me on this. No file attacked this time :( Thank you

[@ray_an_e]

He made a video about that : kzbin.info/www/bejne/lYWkeKN3f56lirM

[@xodrinker]

nice one Bas.

[@HowtoPowerBI]

Thank you!

[@ibrahimalshweair7866]

Love your content and explanation. Hope I’m not being annoying when I ask if you could share the pbix file with us. It’s not only gonna save me and others some time, but also I am sure it will boosts channel views for you too! I appreciate your work anyways 🙏

[@HowtoPowerBI]

thx Ibrahim! for each video you can find the download link in the description 😉🙂

[@Alan.DL7]

Hey Bass, I was checking again this video to implement it and I noticed something at 15:41, maybe is just a visual issue only but why the Data Points for 0.66M and 2.21M are not visible anymore?

[@Ayaan_khan21]

I have a small query I thought that x -axis has to be always to be a time based column but i worked on a datset in which i have to put countries in x-axis whereas years and profit on y-axis and secondary y-axis respectively . I think it depends from scenario to scenario. please comment if i am wrong

[@leenplof]

Nice trick, it builds a nice story for the consumers. Definitely going to steel your trick 😬

[@xinll]

thx Bas for that brillant video. Unfortunately, i just miss that area transparency option in the version of my power bi. Any ninja trick for older versions? all the best Falk

[@PeterKontogeorgis]

Don’t think so, as I believe the transparency is hard coded for the area chart in earlier version.

[@xinll]

@@PeterKontogeorgis thx Peter.

[@ashishmohan4707]

Hi Bas, Again amazing real time based business problem Very useful video and very practical need everyone for every project ......very big thanks to share with us. If it can possible can you plzz provide us excel data file which you used. Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂

[@HowtoPowerBI]

thank you!

[@granand]

Hey Bas, Thank you very much. Can some one tell me, if I can use this Sales of Dept vs Total Avg sales of the company ? etc

[@HowtoPowerBI]

Don't see why not? mabye give me more details of how you are trying to visualize it. Before implementing this technique make sure to check this out (might be easier way of doing it -> kzbin.info/www/bejne/Z4rMYqukabeDg8U)

[@granand]

Hey Bas, when are you writing a book ? I cannot wait to buy one.

[@HowtoPowerBI]

didn't get to it yet -> 😛never seem to find the time. You have already a good title for me? 😄

[@anjuchhabra9102]

Hi, I was trying the same in the latest version and found that we can't remove the color from the Share Area - the transparency. And due to this, I cannot remove the fill color. Pls, suggest if there are any solutions.

[@arslanahamd7742]

Interesting video. Here's my suggestion , please provide the datasets (direct downloadable) of every video

[@HowtoPowerBI]

Thx for the feedback

[@Psyresidente]

Great and amazing Visualization ! I would like to customize this by using the TARGET RANGE to show the values based on the percentage of variance between Actual and Forecast. Is this possible by modifying in somehow the measures ? it would be very useful! At the moment I'm trying things but not getting the solution. My brain is not huge than yours :)

[@HowtoPowerBI]

thanks a lot! I walk you through an easier approach in this video kzbin.info/www/bejne/n4WzaKaDmb5letk for the labels you can use the formatting expression of calculation groups see here kzbin.info/www/bejne/Z4rMYqukabeDg8U

[@nagarajuyadav2162]

​@@HowtoPowerBI hi sir how to create paginated reports in multiple languages?

[@ronaldtijssen7492]

I wanted to use this workaround but I think it doesn't work anymore since the last update of the new format pane. I don't see the option to change the area transparancy anymore. Hopefully I'm wrong.

[@chaitanya38419]

I am unable to see area transparency option on my area chart.. Any ideas why this could be ?

[@tony-ma]

Hi is it possible to clean up the hover tool tips? Coz all the data labels are in the hover tool tips.

[@HowtoPowerBI]

Hi Tony, I would use custom tooltips 🙂

[@keagankemp6275]

Wow

[@kadwaltheshark]

Can you also share a trick to clean the tooltip in such an example without having to create a separate tooltip sheet in powerbi?

[@HowtoPowerBI]

you are bit limited with the default tooltip options - i think you don't really have much choice here than to resort to custom tooltips .. be aware that there is now an easier way to set up a dynamic benchmark range -> kzbin.info/www/bejne/n4WzaKaDmb5letk

[@EST1865]

Dope

[@edgartorace5934]

How to put a weekly hierarchy... with Saturday as the starting day of the week on this format?

[@HowtoPowerBI]

kzbin.info/www/bejne/eZ_OhqSLr8mah7c

[@spen2431]

vvvv What he said 👍👍👍

[@HowtoPowerBI]

thanks :)

[@totvabe1]

I've made it but have one problem. When two consecutive dots are out of the area the 'Labels' line between them become red, not only the dots. For the rest is perfect!

[@HowtoPowerBI]

I made that connecting line for the label disappear --> under shapes, customize the series, choose the labels series, and put stroke width to 0

[@totvabe1]

@@HowtoPowerBI Yeees! It works properly. Thanks Bas!

[@IoriYagamiKOF98]

pretty nice, but now if you hover over the line chart , the user will have a bunch of "aux" measures :P

[@HowtoPowerBI]

I would use a custom tooltip or turn off the tooltip

[@bandb1047]

you are founder of Poweer BI

[@pabeader1941]

Love the content, not the background music. Very distracting.

[@HowtoPowerBI]

Thanks for watching and your feedback!

[@PeterKontogeorgis]

Funnily enough I really liked the music...very relaxing.

[@rakeshchaudhary1110]

Please provide me practice data ..it's your drawback

[@avizcarrav]

EZ

[@stanley_wealth1]

I accidentally deleted the channel rack. I’m lost