Power BI: Transforming Good to GREAT

hello and welcome to my video my name is Reid havens and I'm here today because I want to show you some of my best techniques for transforming your average power bi report into something that is unique and stands out in the sea of reporting before I get into that however let me just take a moment to explain Who I am and why you should listen to me now I'm a principal consultant and blog editor at powerpivot procom and a part-time instructor at the university of washington now all of us here at powerpivot Pro are bi ninjas and we love to consult train and teach everything there is related to excel and power bi all right now enough with the introductions what I want to do now is show you an example of a power bi report that is unpolished and a bit messy then what I'll do is walk you through the various techniques and best practices to let us polish and refine this report as I go through each of these practices also explain in detail why I'm doing them as well because I want to make sure you have a good grasp of the how and the why for each of these all right let's get started now the first thing I want to go over is the removal of too much color now one of the common pitfalls of reporting either in Excel or power bi is the fact that people often over saturate their reports with color they want to fill every pixel of the entire report with some kind of a color scheme and even worse what they often uses colors that aren't complementary or they don't use colors in the correct method so this one as you can see right now is essentially following a victim to that common pitfall it's just the entire background is colored every one of these reports has color within the the background of the charts themselves there's a lot of contrasting rows in here so let's go ahead and clean this up a little bit so I'm going to start by removing the color in the background go over to the paint roller and go to page background I'm going to set that back to the default there you go essentially removing all the color from that perfect and I'm gonna do the same thing with all of these gonna get rid of that excess color that we have in here I'm just gonna select and turn off the background same thing turn off the background here turn off the background on this one now for the table what I like to do is I actually use the color themes for this one so we're gonna go ahead and switch that up a little bit again under the paint rollers section you'll notice if you actually go to matrix style gives you a whole bunch of selections of types of visual styles you can apply to here more often than not honestly I've try to use most most of them as you can see a lot of them use a very stark black and white contrast color that I would particularly like the only one that I really found that works well for me is the minimal and that actually cleans this up a bit the one thing that it it actually is doing is it's adding a little bit of a lime specifically right here and right here even though there's no color in it at the moment so I actually do want to add a little splash of color into there just to give it a touch of color because that color can be good you just you only want it you want it subtle not so much in your face so I'm actually gonna go ahead and set that to like let's let's set that as wrong one set that is a blue so I'm gonna set the outline color as a nice blue there and let's go ahead and increase that just a little bit so that by the way this is just simply setting the the color in here and this is also I can expand this there we go it's the outline weight so the weight lets you determine how thick the line is right over on these two spots there we go that should about do it now some of you may already notice that there's some distinct sections in this report that I made now in general I like to split reports in Nick three primary areas and that's the title and banner section at the top a filter is in slicers section on the left side and then the remain report and data section where all your charts visuals and graphs are so what I'm going to do is walk through each of these three sections and polish them up one at a time starting with the header section at the top so one of the important reasons that it's good to have kind of a title and header section is that's a good location where you can essentially put your data in in lights and highlight the information that you really want to be seen so that can be in the examples that we see here like our total sales or total profits average sales per day and order dates single values that you want the customer to be able to use and look at and essentially get at a glance where they they want some additional detail maybe in the data section but they really want to just at times be able to come in and skim and see that information at the top of the page up there in the top banner section so I'm gonna go ahead and start with that and kind of clean up these cards a bit make them a little bit more [Music] quality and have the visuals pop a bit more and as well what I want to do is actually want to add in a company logo and a few other things to really make this section stand out so I'll start with some of these visuals here and I'll actually start with the first card so what I'm gonna do with this let me scoot it over here so you can actually kind of see it as I'm editing it I'm going to go to the paint roller again and I'm actually gonna take off the category level because I'm I'm not really a fan of the of that label B down at the bottom wrong one there we go category level off and what I'm gonna do instead is actually turn on the title and as you can see there's actually some of the cleanup I've already done in here but what I like to do generally is turn on the background color to something and as well change the font color to kind of like a white or a light gray to make it really stand out now by itself it doesn't look that impressive or anything but what we can also do is to go along with that is you turn on the background color there you go to match it and then go ahead and change the data label color perfect so now essentially it's it's an actual card like it floats around it's distinct from the other ones and it's a it's essentially visually distinct from the other ones so I'm going to go ahead and actually rename this to total sales because that's the title that I want it to have there we go perfect now there's a couple other really nice little tricks that go with this so I mean I did all of these edits I could go and do it again on here here and here but that's gonna take some time and one thing we like to kind of promote here at PowerPivot Pro is the being kind your future self it's doing things that will benefit you later on or reduce the amount of time that you're doing something and a great function in power bi desktop that lets you do that is in fact this beautiful little tool called the paint format painter so if I select that and I click another card it actually automatically applies all of the formatting the titles and everything else to that and this one is actually average sales per day so let me go ahead and change that so you'll notice that if you already have a title in there it will keep that old one and the title isn't dynamic by the way it is static so it's important to make sure that it's named correctly so this is average sales per day there we go let's try that one more time now this one might get a little perfect so last one and this is last order date there we go now there's one last thing that I'm gonna do is you'll notice that all of these are of various sizes this is actually different different size and the rest of them which to meet I will notice that a customer might notice that as well I do like to have these about the same size so I'm gonna go ahead and just make sure these are all there we go the same size as each other one nice thing to do is you can just drag them on top of each other and resize until they fit essentially the other thing that I have been doing you actually go under view snap objects to grid makes that significantly easier so this is a wonderful little tool that essentially as you can see if you select these there we go it actually pops them around with it off it makes it much harder to get that exact size because it's freeform so let me go ahead and go alright so I'm gonna do the same thing with average sales make sure that's appropriately sized perfect the last ordered date there we go all right you zoom out a bit and just I'll go ahead and wedge these all in here and it's even make it even makes it really easy to space them nice and cleanly perfect now the other thing that I want to do see scoot scooch the company report over a bit I'm gonna go ahead and here let me put a company logo in there and this could also be a product logo anything else it's it's something that just gives it a little bit spa a little splash of a brand image so I have something actually on the desktop for that let's go ahead and import I'm actually gonna grab it from a class that I teach right there it is Northland logo perfect so it's just I can let's consider this our company image for now nice and easy to add or just throw that up into the corner there we go and same with these actually I'm gonna I'll make you a little bit bigger so they can fit with the rest of this just drop all of these down so one thing I do like to do is I'd like to try to save as much space as I can in in my header section and not make it any bigger than it needs to be and this can actually sleep down and I can probably make you just a little bit bigger there we go yeah it's the one great thing about power bi is it does the canvas limits you to the amount of space that you have which does force you to really think about exactly what you want to display because you have a finite amount of area however it does make it so you really want to squeeze everything in there as much as you can which is why I'm kind of just edging things up just a little bit there we go get just the right amount of space now there's one other thing I want to do to really help separate this from the rest of the of the charting in there so I'm gonna go ahead and add a little line that actually lets me kind of separate everything it's a really cool divider line I'm gonna go to shapes and select line and again that's right here just allows you to add any shape I don't use too many of the other ones but the line is super helpful to create that little dividing section I'm going to go over informatic is right now it's a vertical line and I actually want it to be horizontal change that to 90 degrees there we go and let's let's make that a bit consistent with the rest of the colors so I'm gonna make that like a nice light blue let's go ahead and stretch this one more time there we go perfect and we got our header section now there's one thing I actually forgot to do that I want to make sure to hit on before I move on from my header section so the things at the top that you'll see here we have about four sections we have or four cards or I should say total sales profits average sales per day and last order date now some of these can arguably be a little bit more important than other ones like profits and average sales for they are more of a are a better indicator of kind of the the pulse on the company that you really want to just see so I'm actually going to use some colors to draw out the fact that these are the more valuable bits of data that we want to show on our report and I'm gonna do that using color gradient now the great thing with color gradient the way that we perceive it is often darker colors are usually perceived as something of greater quantity greater quality greater value or something that's just more important so I'm actually gonna keep those two in the middle the profits and average sales pretty has a dark color and then I'm gonna shade total sales and last order date is something a little bit brighter to draw the eyes towards the middle and the important numbers we're gonna go over to total sales and I'm going to go to the paint roller and under title I'm going to change the background color to a little bit lighter and I'm gonna do the same thing under the background itself a little lighter again there we go wrong one beautiful and I really easily just used that format painter to clean up that other one perfect now for this section what I want to do is I want to switch over and switch gears into the filters and slicers selection now one thing that's important about these is really understanding the types of slicers that are available to use because I'm like Excel which really just has one major slicer just like the click button type there's a lot of different slicers that you can use in power bi so let's explore some of those a little bit so if we look over here right now we do already have some slicers in the area but they're they're like the traditional drop-down or selection type which is you know you pick a button and it will give you the selection for that but it's you know it takes up a lot of room there's a lot of white space between these and it's just it's not a very effective or intuitive way to use these at least compared to the types of slices that you can now use in power bi so I'm going to start by cleaning these up one at a time and getting a kind of a better nicer cleaner section to go with from all of these so I've used a few of the different ones and overall my favorite type honestly is the drop-down if you actually select this specifically this little arrow little down arrow right there that does let's give you the option to pick between a bunch and now it's power bi gives you a few options to go from you get a list a drop-down now you're seeing between less than or greater than or equal to all these three bottom ones those are because the technically year is an actual number so it's assuming that hey because this is an actual number column you're slicing on if you want you can provide a range or things like that so it's actually pretty nice like between would give you the option to actually do a slider of the years if you wanted to do some kind of a range normally speaking this is better if you have say like housing prices there's an or or other some kind of currency or product price it would be really good for that for this I'm just gonna go ahead and select drop-down because I really just want to be able to pick a single year at a time and I'm gonna do a couple of other cleanups so let me let me bring this over here so you can see it as I clean it up a bit one thing that I really like to add is a header line so there's a little outline section under header that lets you actually add a line so I'm gonna select bottom to put that into there now you'd think like okay header line cool like how would I change the color on that it should be in the header section right like give maybe font color home no background color I know that's not it like there's you know where's the where's the line color that I would want to be able to edit well for whatever reason they decided to put it under general in an entirely different sections so it's not super intuitive of where the location is but this is where you would go to find it it's already set to a comment that I like and again outline of to is normally what I use that I use this as so I'm gonna go ahead and bring that back over here and drop this up a little bit just to clean it up now there is one other type of slicer that I really like to use and they just added this over this summer so I'm actually gonna ctrl-c and ctrl-v because I want to keep some of the formatting and I'm gonna put a second slicer up here at the top and there's something that I'm gonna drag into here so I'm actually gonna replace the year value with the date so that's an actual date datatype now if I actually go over if I look at it it's you know it's all the dates that are in here right now but if I hit that drop down you'll notice a couple of other little sections unit in here there's a before and after a list which is what we started with and this is a really really really important one that I want to point out is relative so let me go ahead and select that now what this does is create a slicer that lets you actually select the last one years select the last one weeks days whatever it might be so you can actually now do relative dates based off of that one thing to point out is the calendars by the way that does the complete date so the last one week goes back exactly week from the from the current day so it can go back one mid week or one within mid-month or anything else like that or mid-year but if I selected calendar that goes back that number of entire complete months that entire number of complete weeks or that entire number of complete years so that's a little subtle difference between those two but this thing is very powerful and it's super useful to clients cuz it historically you would have had to use Dax or something else to be able to get the same amount of information like we're rolling 12 months or rolling six months or any kind of window like that you would need to use Dax filter II and in calculate but now the customer can essentially just come in and pick their last their next for this any of their years and do that now with a slicer which adds an entire differently a different level of dynamic nough stew this that's really really powerful so that's what I really like about this relative date slicer and one thing I'm actually gonna do as well as I'm gonna rename this so they actually know what it is a little bit better for the customer I'm gonna rename and call this just relative date there we go perfect now one thing that I actually cleverly do as well with these things is I separate them additionally with a little bit of a line that makes them even more distinct from each other because I whenever I can or if any way possible I really like to group things together to show that two things are related so relative date and year they're both related to each other and any other types of slicers so I'm gonna go ahead and actually throw in this little divider right here and separate that for the next section that we have pop that in there we go perfect and I can use that format painter again I'll actually format the employee sales region and what I'm gonna do with this so I want to get the color a little bit different than it is on the rest of them so I'm gonna go ahead and actually color this a slightly different color to further point out the differences so I'm gonna give that like a slightly different blue there we go and I'm what I'm gonna keep one of these in here for sales region because there's one other thing that I want to point out that's really important like I could do employee sales name and add an additional slicer however that just takes up valuable space because honestly you could use the employee name right down here if I wanted to sliced for Michael or Margaret or any of those all I have to do is simply select that person's name right in here and this acts as a slicer as well which saves me the valuable space that I would need to have it over here so that's why I'm only going with employee sales region instead of just the the individual name because it can already sliced for northern here and then even further if I wanted I could slice on Laura's name alright let's go ahead and do the format painter again and do product category let's get that up there perfect the last two sections I'm going to include is actually the shipper and supplier put that right down there here we go let's get these down just a little bit and I'm actually gonna copy and paste these last two because now I even have more room as you can see to actually fill in the rest of my slices down at the bottom I do that drop down really does give me extra room to be able to polish these things up which is just it's a very very handy feature and again they're the biggest reason why I prefer to use the the drop-down list compared to any of the other types in here so I'm going to go ahead and look for a name because I'm actually gonna use two I'm gonna use shipper and I'm gonna rename that to shipper name and I'm gonna do that one more time I call that supplier or same thing name and I'm gonna bring in supplier perfect and rename that again so it's nice and friendly and we got those two right here shipper and supplier and last thing that I want to do go ahead and let's make this a nice gray there we go so we again have that color separation that really makes these things stand out there you go yeah so now we got a nice distinct section and the very last thing that I'm gonna do in here I'm actually gonna scoot these up a little bit just so they're in line with the rest of my report titles there we go as you can see I like to try to wedge these things in here nice and compact just so there's a lot of room left and I'm also going to put one more divider line I'm gonna rotate it I got a nice zero Degree and I'm gonna put that right here where'd it go there it is and perfect look at that there you are you know we have a nice clear section for all of our slicers over here in the corner oh and looks like I forgot to change the category color and I'm even gonna rename that just for good measure call that product category so we are very very clear on exactly what this is and I'm gonna give that a little splash of red actually let's go with the let's go with this one so it's not there we go so now we have a nice distinct blue for our years sales region gets its own section for employees product category gives its own section for product details and then a shipper and essentially the order information down here at the bottom perfect now the next thing that I want to go over is going to be some chart esthetics specifically I'm going to want to adjust my three visuals that I have on this page I want to kind of polish them up a bit maybe add some features tweak some settings generally take these things and make them a little bit more presentable or even so a late week the the visual type a bit based on the type of data that's in here so let's go ahead and start with the top one here go ahead and zoom in so what we have here is our total sales by forecast and dates like this is the chart that kind of you know it works it's a line graph but it's not really telling us that much like there's no data points there's not really any formatting it's a very generic lay a plain looking chart so I'm gonna run through some of the practices that I do on most visuals and that's turning on data labels and editing the the chart title a little bit so I default it normally Harvey I normally has total sales or caste whatever the title might be it has it in the upper left-hand corner I'm of the school of thought word I kind of like it center-aligned I know that's debatable by some people but I think it works best for me so I'm gonna go ahead and tweak those settings a little bit I'll find that over here under the paint roller section under title so I'm gonna go ahead and adjust the alignment into the middle and one thing that I want to do is well I'm going to change the font color to white and that's because I'm gonna add a background color too to the chart title I'm gonna go ahead and go with that blue that I've been using a lot and you notice that what this does is it actually it adds a really nice frame to the to the object here so it helps distinguish it from the other objects onto the page and really aligns it there nice and cleanly so you can kind of see where this starts at night and the next one ends the other thing that I really want to add into this is some data labels as well onto the line it's gonna go ahead and turn that on and by default you'll notice that it uh it's kind of intelligently going for the the highs and the lows so power bi recently added a feature if you're looking to here called label density so let me zoom out a bit and show you what that looks like say if I have that at 10% it goes with a lot of the low numbers that's are probably a little bit too too shallow if we do it at a hundred percent it shows every single data label so we want to find a nice medium because honestly what we care about at least that our client wants in this is not necessarily every data point but kind of those magical number of data points the ones that are really high or really low like the outliers you know the dips and the peaks so 75% a nice little medium for that that really allows us to to work from that level now the other thing that I really want to add into here as well one last thing to kind of make this really standout and provide some real value to the customer if we go ahead and actually look over here under our analytics tab there's some really cool that we can add we got trendlines constance mins max a whole lot of different types of values that we can put into our chart now the one that i want to focus on on the bottom is a really cool one called forecast so you can notice the open it up hit the Add button let's go ahead and call this something besides forecast one I'll just I'll just call this forecast and you know what I'm gonna do half a year so that's six data points for me because I want about six months out 95% interval that's all that's good I'm not gonna tweak it too much and I've hit apply you'll see what this adds in is it actually it creates a forecast line so it helps predict what the the data is gonna do based off and historical values and it shows you know the forecast and the upper lower bound based off of the percentile that we have in there and this number is something that will change you know if we select different years there you go so it's a really cool little feature that lets us see that number that that changes then with the chart itself and it can be very useful for clients especially ones who are wanting to do a lot of forecasting into predictions it's a nice clean easy way to get additional data into there and help project forward rather than constantly having to look at the past when it comes to the the report itself now the next part of this report that I'm going to want to clean up a little bit is going to be this chart down here so this chart if we take a look at it ask ourselves some questions its employee's name and what it's doing is it's a stacked column chart so it's looking at the total orders by the employee name and if we think about it a bit we can interpret that the customer the client is probably wanting to see employee performance per month and see kind of how they compare against each other now looking at it by default we can kind of surmise just by the sizes like okay there's it looks like Margaret might be the the biggest one on this column if we can compare to the others okay we can see that thirty two does appear to be the largest number but there's no easy way to tell or track performance month over month and follow the journey so what power VI has done recently is they did release a new type of chart called the rivet that easily lets you see performance and kind of have a journey follow along with it so let me instead of talking about it too much let me go ahead and show you what that would look like so I'm gonna switch this over to that that visual type so the one that I'm talking about is located over here that's a ribbon chart so I'm gonna go ahead and select that and it changes it and now look at that we can immediately watch a journey that's almost kind of like snakes or ribbon working its way through the report and that marga the one that we were looking at earlier we can immediately tell she was the top performer for two months she dipped down in quarter one increased back up in quarter two and quarter three and you can follow her journey through this and see how she's been performing see which ones are the lower performers at the bottom and all of that so it's a really cool chart and it's one of my favorite things that they've added recently because they haven't put in too many new visuals this year but this one is absolutely my favorite now I want to polish this up a bit too I want to add some data labels do some other things I don't want to do those steps one at a time so I'm gonna use my little cheat button that is the format painter and I'm gonna copy and paste or you use that too to copy my formatting settings from another chart and paste it into here so up at the top you'll notice this little button right here on the Home tab so I'm gonna select a chart that already has my formatting I'm gonna select format painter I'm gonna go down here there we go you'll notice that the title got fixed it got colored in the font changed it added in my data labels so that's great now there's a couple other settings that I can still modify as well I like the the ribbon that's down here but I want to kind of make the column stand out of it you'll notice that it's a little hard to tell the columns are right here and here like there are distinct columns between it but the lines between them are also the same color so it's kind of hard to tell where the connector ribbon starts and where the columns in so let's go polish that up if I go into the format painter button over here and I look at where as a plot area sorry ribbons perfect there's a couple of options that we we have spacing match and transparency so what I want to first do is I want to actually and let me open this up let me go ahead and increase it transparency did let's try 60% there we go now we can actually tell the difference between the columns and the ribbon here so it's good let's make me maybe make it a little bit lighter let's try 70 70 there you go mm too much yeah I think I'll stick with 60 percent that looks pretty good perfect and the other thing we can do too is we can actually add a little bit of spacing and let me show you what that looks like if we put that in there we go so it actually puts a little bit of a gap between all of our our ribbon spots right there so it helps separate them and show them as a little bit distinct from each other so that really no it helps outline the columns to show that it is a stacked column chart but then also in the background still has that little bit of a connector between all of them but it's not quite as obvious so it does add a bit of visual discernment between the columns and the connectors that go between them so this is kind of how the way I like to design it and I think it works a lot better this way now the last chart that I want to kind of edit or polish up is this one over here in the upper right hand corner now this is going to be a nice and easy edit all I'm gonna do is use that format painter button that I had so I'm gonna select a chart that's already been formatted go to format painter and just select that other one done I got my data labels in there I got my title on that as well centered colored in now there's one thing left that I kind of want to do you'll notice that the the axis labels are not really in a number that's particularly convenient it's way too big of a number like it's in the millions and it's all in the decimal places at this point so let's go ahead and tweak that on these two just to just to polish it up a little bit more so I'm going to hit I'm gonna go to the y-axis and I'm gonna change my display units to thousands instead there we go that's much better and one more time I'm going to do that over on here to change that from auto as well mm perfect now there's one final thing I want to edit on here before I go into showing you how to create some cool things within the data model itself so what we have is still our table left now it's not necessarily something you might think about when you think of chart aesthetics or chart design but there is a way to add additional valuable information into here using conditional formatting something that power bi has started to use within the last year and been continually building upon it and adding to it so I'm going to create some conditional formats on three sections I'm gonna do it on total profits I'm gonna do it on the month in month over month average sales difference and I'm also gonna do it here in the sales day difference all three of those are going to get some conditional formatting so if I actually go down to toad approximate I hit the down arrow there's a section for conditional formatting that I'm gonna tweak a little bit so I'm gonna add on this one I'm gonna add some data bars into here now it's gonna give me a pop-up window there we go with a few settings in it whether or not I wanted to change the highest and lowest value from a from a value to a number few other settings but overall I'm I'm pretty okay with it as is so I'm gonna go ahead and just select okay there we go and that goes it go it goes ahead and populates this information in here essentially creating a little chart in every single row so kind of creating this beautiful hybrid between between a chart and a table itself now I want to do a similar thing over on month-over-month sales date difference and the sales day difference percentage so the percentage I'm going to add the same type to I want to create a bar chart on there so I'm going to open that up and go to the same conditional formatting and add a data bar and I can go ahead and just set it as is don't need to make any changes there we go and what that now does it's create a bar that shows immediately shows the positive as a negative so it provides really quick insights helps the the client go in and see where the positives and negatives are and at a glance see outliers and their data now the other thing that I want to create as well it's one more value on the month of Vermont sales day difference no I don't want to create a bar on this one I instead what I want to create is a color label that's going to either show red for negative or green for positive so let's go ahead and do that I'm going to open it up go to conditional formatting and I'm gonna I have two options I have a background color scale and I have a font color scale so one of these I'd much prefer to the other one as I mentioned I I try to use as minimal color as necessary to be accent 'iv show certain things just enough to get the data a little bit more intuitive but I don't want to use too much color so let me just show you quickly what background color scales looks like but it's not something that I typically use and this was really the first option for conditional formatting in a power bi so if I add that in give it a second like yeah it's just it's a lot of color honestly it's a little bit too intense I don't find it particularly pretty so I've avoided using that one now recently this year what they had it they added in is the option to do that same type of color design but they have it only on the font itself so the font changes or some of the font color will change but it leaves the background empty so I want to do that let's take a look at that again look at that so we still have the same color coding but it's now only doing it on the font even further to improve this like I like this but I don't want it to blend together I don't want it to go from a red to a more red to a more green I really want it to just be two colors I want it to be a red color for all negatives a bright red and a bright green for all positives I like essentially these two colors down here this is a perfect example of a nice bright red for a negative and a nice bright green for a positive and I want the rest of them to all be that exact same shade for the positive and negative numbers so there's a little tricky can go in and kind of set those settings so I'm going to go to conditional formatting and I'm going to go back to the font color scale and instead of doing a lowest in the highest value if I change these two numbers and I do a minus 1 and a 0 so what I'm telling it to do is if it's if it's greater than 0 that which is the maximum it's gonna be that solid green on this corner over here and if it's less than the minimum which is the nut minus 1 it's going to be this solid red so essentially it's now just going to be two colors and it's going to eliminate that little gradient in the middle so I hit OK and watch the colors look at that we immediately get that bright green and that bright red between the two and creates that great contrast and help show on both the the actual month-over-month sales day difference and the percentage we now have a bright color for the for the dollar value and a really good call-outs with the bars for the the percentage as well now the last thing that I want to add to this report is a grouping table for our Dax calculations so by default and historically the kind of the practices that that I've done and a lot of analysts have done is you normally put your Dax calculations in the table where it's getting its data from so most of our data is coming from orders so you can see like all of my calculations are sitting in here however it's not the the easiest way to organize them they're sorted alphabetically there is no search option to search for you know Dax and it doesn't do anything there's no click clickable button that lets you filter just those so it would be really great if I could have some kind of a table or other little field over here that could organize my calculations together so power bi almost has like a hidden feature that lets you create a Dax measures table and you can take all of your calculations and you can put it in there and then it holds them in place and then it's gonna do a couple of other cool things if you tweak a few settings that I want to show you guys what it does it's it's a pretty awesome little tool you've probably seen it if you've read us some of the post that I've done on power powerpivot Pro where I talked about in some of my top five practices but I'll show you guys how to do it here again as well so I'm gonna go up to the home tab and I'm gonna select an option called enter data and what this is gonna let me do is it's gonna create a table for me this is a template to create an entirely custom table it's used for a lot of other things disconnected slicers that we like to talk about a lot here at powerpivot Pro this is a great way to do that but in this scenario what I'm gonna use this for is just to create an empty table as a placeholder to put up to put all of our Dax calculations in so the only thing that I'm gonna do is name this tax measures and I'm gonna select load let that spool up it's going to take a second create a connection of the model and generate a empty custom table that's gonna go right here there's my column one everything good to go so what I'm now gonna do is I'm gonna move all of my Dax calculations I'm gonna select them and I'm gonna choose the home table as Dax so let me zoom out and I'm just gonna kind of I'm gonna quickly walk through all of these and just shift them over to this table keep going a few more almost there total profits moved total sales alright one time thing little tedious but no problem you got him in there okay cool so we have a table it's in the model I mean it's still not it gets better I like it uh you know I'm I'm even gonna do one up and I'm gonna go ahead and delete the column because I don't need that you go ahead and delete it and I get this is phase one you know this is already better than it is we now have a table specifically for our calculations but it's it's not at the top of the page it's not in any order that I'd like it to be it's still sorted alphabetically with the rest of them so there's a little trick that happens in power bi you notice that I deleted the column now that's for a very good reason one I don't actually need it this is just this only needs to be a placeholder for my calculations and number two something's gonna happen when I close the workbook and reopen it now don't ask me why it needs to be restarted a power bi desktop in order for this feature to turn on but let's watch what happens when I do that okay say it my report and I'm gonna close it I'm gonna open it back up there we go perfect opened up have our report hanging out here now take a look over in the corner and look what it's done it has moved this up to the top of the list right here now what it essentially has it acknowledges as it says hey okay so you have it you have a table that's empty it has no columns or at least no columns visible and all you have in here is Dax calculations so I realized that what you're trying to do is you're trying to create a tax table so what I'm gonna do is I'm gonna change the icon from a table to a calculator and then I'm gonna put this at the top of your list so it's now sorted non alphabetically everything else below it is still going by alphabetical order but the Dax calculations table will always go at the top of the list so you have this beautiful separation between your calculations which are nested at the top and all of your other tables that are below so it creates a really nice and like enterprise level looking model without having to kind of hunt through the rest of your tables to get those other values and on things like that it's a practice that I do every time and it's something that I do a lot now there there's a few caveats however Marco Russo has pointed out on a blog that I talked about this with that there are certain things that don't necessarily work very well specifically at the time of this video Q&A gets a little glitchy and the questions and answers with Cortana on power bi com if your Dax is on a separate table compared to the the table where it's getting its data from but other than that this works splendidly and 99% of the time I'm happy to use it so that about does it for this video we've walked through a lot of things let me show you the other report we've gone from here we've gone from a report that's very bland had a lot of things honestly not going well for it it wasn't using particularly well designed slicers there's a lot of are way too many colors there was not a lack of a lime it just so many things that honestly we're wrong with this and it's just it looked so unfinished and we worked our way through a piece at a time to create something that's truly a lot more polished has better visuals in terms of that story we're trying to tell an example that that ribbon chart some forecasting and a lot of other things that really just make the report pop so I hope you learned a lot from this take with it take from it what you will and I appreciate your time and I look forward to seeing you in my next video

Channel: Havens Consulting

Views: 35,563

Keywords: Power BI, DAX, Reporting, Havens Consulting

Id: 3Cq5W-KzJL0

Length: 47min 34sec (2854 seconds)

Published: Thu Nov 30 2017