Building a KPI Scorecard w/ Custom Visuals in Power BI with Reid Havens

good afternoon good evening depending where you're calling from it's kind of strange I actually have three in the same time zone actually I'm assuming that I don't know that for a fact are you back from Texas free I am yeah I flew back and got in about midnight last night so I'm enjoying my coffee and adjusting to the to the time difference a little bit other than their smoke what beautiful weather huh I you know hey at least it's cooler the temperatures dropped yeah it's been a beautiful weather here in Seattle so and I am actually offered two-week vacation and go back out fishing who it is so who would it guess that chuck was gonna go fishing so Reed actually I nominated five or six times to be an MVP and it's not saying that he doesn't have the credentials it's just a skip in our processes why you're not at MVP I'm guessing and we need to figure that out but he is super active he goes out and blogs the PowerPivot Pro he actually goes out or helps run our user group I delivers dashboard today is he actually is a professor at the local community college and at the University of Washington and actually did a guest lecture for me while I was in the MAL dive so Reed you've got quite a good quite a resume once you tell us a little bit about more about yourself and what are you gonna cover today what are we gonna look at yeah absolutely so you covered quite a few bases I've you know very much enjoy doing a lot of the the public speakings and things like that and in general I've actually found that overall like I've really been curating a lot of videos in the last year or so and I've found that's actually a really great medium to to share and do a lot of walkthroughs the the thing that we have in front of us specifically today is something that I ended up just kind of randomly poked around in and it with a bunch of custom visuals and everything else and ended up just kind of as a pet project coming up with this idea of creating a KPI dashboard just as a general template and thought like hey this would actually be a probably something a lot of people would find interesting or useful to see at least there's a lot of those kind of design a sugar coating of the designs and everything else that goes with this so I ended up you know I'm now actually in the middle of kind of doing a multi-part video series on it and you know I thought this would be a great thing to share during a webinar just the you know to create this out of a template and I understand a little bit of Dax a little bit of actually how to use some custom visualizations it kind of just covers all of the bases on a lot of really cool power bi tips and tricks and I know you didn't work on it but actually the the dashboard that you're showing right there looks a lot like my team scorecard I literally just talked Martha a meeting and it looks like they look very similar they're swimlanes and the columns and the metaphor you look at it glance so yeah this will be good again the fact that we're doing very similar things means that there must be some reason for it right oh I see yeah great great minds think alike yeah well in this case devastation on my team not me but yes exactly right yeah so I've kind of gonna I broken it up into chunks and there will kind of be four parts that I'll walk through today but what I kind of just want to show you the extra functionality of the report itself before we kind of dive into it and I think cover the yeah because I'm not going to create every single card that will be way too many but I will certainly cover enough to to kind of teach teach out of fishes they will say and get all of that but yeah what all of these are is everything on this page is a series of individual objects I tried for a while to use some of them there's the KPI matrix and a bunch of other visuals that get something that looks roughly like this but it still wasn't quite as customizable as I wanted it to be they're just it felt like there are some things lacking and I'm very picky when it comes to the visualization components meaning the you know the colors the whether or not there's you know spacing between stuff even just having white space and in the background and all of that like I just couldn't I couldn't quite find the the right visual for that I did not want to custom code my own that would be its own rabbit hole that I didn't want to go down so I instead it's not a to you do a combination of tiles and I think this might actually take the record for we're visible at least not including bookmarks the most number of tiles that I've our visualizations in individual visualizations that I've ever had on a single report page and it still runs pretty well the one thing I noticed is if I open the report it might take three to five seconds for it to actually fully render but you can see here if you actually click the slices that everything all the docs recalculates within you know one two seconds so it still operates really fast and there's a lot of different things that are going on there's a mixture of images essentially or almost like emoji that are being returned yes some dacks tricks that we can discuss as well today the fact that even some of the arrows and they are in color coding that's being in I mean coot included in some of this because I I know one thing that I often get requests from customers is I love I like the card visuals is there a way to do color coding you know like I wanted to have some KPI colors in there to organize that but you not have it in a table and that's going to eat through the use here of some of the custom visuals that we will also see probably about halfway through this session today and then of course just the standard sparklines that we have over on the on the right that are I'm also using custom visuals that both by ok viz you know they from our Karissa Roberto Ferrari who helped them get those created and refreshed every month there's a lot of cool stuff that kind of goes on in here that I I think I just I found fit very well overall in just this the scorecard design the primary purpose being really each row is information about a specific metric so information about sales about information about return amount and you know kind of just so on down the line but it's grouped by that within key metrics related to whether or not it's ranked total value though these are all just general ideas of something that for this specific report it fits for say stores and that information but I think taking this to more of a meta level just this general layout and it sounds like you already have one like this Chuck I think just works really well for information I mean in this grid fashion for cards yeah yeah I'm a again you draws your eye at the right place it has all the right dynamics oh yeah cool then um yeah I think I think what I can do is like I mentioned I split it into about four parts I'd love to walk through those I'm gonna try to to leave a little bit of room at the end for is something that Chuck you and I discussed before and that I didn't know was a feature that would might be a cool little thing to showcase at the very end but what I'd like to do is really move over here let me open up my part one and like to kind of show a slide of like what I teach yeah it's the same thing when I do trains or classes if I actually spent time making every object on a report you know that's it's a lot of spinning the wheels and rinse and repeating so I'm gonna actually focus on as you can kind of see now being called out these three sections here at the top I mean even more specifically I will be looking at walking us through this first row here they kind of cover the basics of just creating the cards and the kpi's here and then the one thing that's separate that will kind of wrap up towards the end before I cover some of the the format and and visualizations is the color coding on these and I kind of split it up into chunks and created a bunch of versions of reports that will allow me to kind of skip ahead and essentially he had a fast word button you don't have to see here and why you know I drink your coffee and then watch me do all the fine-tuning and so that will let let us keep a good pace and cover more content during the eye during this hour all right let me come over here and I'll actually before I start do this I'm gonna I'm gonna mention the very first thing I don't want to talk about so I spent some time trying to figure out how to create one of these like there there's a text box that you can do in power bi and that is something you can you can add in but I've found that getting it perfectly centered was always kind of frustrating for me it's you can put a space in there and things like that but it's it's it's hard to get it exactly in the middle and I am I gonna stickler for detail for that so what I actually did is I'm using a card right here that is in fact a tax measure that's simply just returning a text value like it's just equals the equal sales amount I'm in quotes and I've I found that that act was a really good way to get that nice centered looking title card that to me was not easily as easily attainable using the text box so that's kind of we're going to start with and then I want to work our way from left to right and just kind of walk through a little bit of this and I'm not gonna spend too much time explaining every single component of the decks but I'll stop long enough to at least explain the general concept but I don't want to go through like the your decks one on one thing of any other stuff that we'll be covering today all right come over here to part one and like I mentioned I'm actually gonna use for this because I'm going to use that card visual that's up and you might notice a few other veers at the bottom we'll get to those once we start talking about some of those KPI custom vigils at the end and I'm going to create a new card visual don't go ahead and pop this up here and if you haven't used it yet you might notice the fear there's kind of snap functions the biggest time-saver in the world for me is the snapped objects to grid that allows things to be aligned really quickly and easily when it's in here so this is a nice little little tip there I'm just gonna stick this in here like I said what I'm going to put into this is a measure that is just gonna be a nice and easy named call-out there's at least two B sales mounts texts and easily done just sales amount in quotes perfect back out bring it over into the field section a teeny bit of formatting is needed for that but the cards don't have too much in terms of the complexity for that so I'm actually gonna let's temporarily just knock you over here so you can actually watch the transformations in a zoom perspective I kick off that category label but drop this down to I believe the like nine you have nineteen worked and let's just give it a quick splash of color lighting that font up and I will give it something a little bit of background there we go perfect so I and I do this for other things too occasionally if I want to create any kind of card that has a text value in it I will usually do this the the one thing that this won't do unlike a text box just as a as an FYI is it I've not seen any way for it to do text wrapping um correct me if I'm wrong check but that's I don't believe that's attainable any in any way by using the the built in single card visuals correct I'd have to take a look I actually haven't play with it I've been building a lot of custom visuals lately so you know that would be where I'd start was actually just art playing with my own but I was like look and see if I can't get back to on the shot window itself I'll sit so if I ever need a custom text box tax visual I can give you a call then okay you do anyways so absolutely I'm expecting it cool perfect yeah so I'm just gonna continue down the line this next one's actually going to be a couple step process so the store ranked what I wanted to do is I wanted to first attain the rank but I wanted something that was more valuable because to me the store ranked by itself was kind of giving me some amount of information but like it's it's in comparison to what it's almost like knowing my monthly sales growth was 20,000 ok well was that 28,000 against 100,000 sales it gets I need something to give it perspective so I actually will go through and create the initial calculation which will determine this and then I'm separately a critic calculation that's gonna grab the number of total stores there are so that way you know what their rank is against the all-up number and that's gonna end up in a card with the docs calculation that is going to be a concatenation of both of those values together all right over here and the beautiful thing with everything in power bi is copy and paste is your one of your best friends I will reuse a lot of what I just did here and put that over there and now that I've got it sizing and with each of these cards I'm going to kind of keep it just nested over here temporarily so now I don't have to keep switching back and forth a couple of quick fixes though kick that kill the background put the text back to something a bit more reasonable there we go and drop that down a little bit more so you can actually see it in and like I mentioned I'll loose I'm gonna do three steps so it's three calculations total that are going to be combined together so that first one is I really want to determine the rank of whatever said store that I'm selecting over here so that's gonna be sales amount rank I'm gonna be it's gonna be rank X there go on the table itself I want this to be ranked over the stores so this is gonna be all I think it's store name there you are that's gonna be the table and then the expression is just sales amount there you go now one thing just to mention I'm not going to do it for every single one but it's usually a good practice just to make sure you apply that formatting if you need to and it's a whole number already set it's good to go so that's the component number one number two because I'm going to want to calculate the total count of all the stores so that is a store count that's going to be calculated in count and the reason I'm doing calculating not just a distinct count I'll mention in a second name there we go now in in here I want to include the all function um as part of my filter for the store name mean I don't want this number to ever change from the model so anytime I select store name I don't want that to filter it out I wanted to stay at essentially whatever the 299 in this case will be but I'm also creating this as a calculation and not just simply concatenate in that final value against the text of of 299 because what happens if my a number of stores changes if one gets added to one gets closed so this will continue to shift with my data all right to work it out now final piece in that is going to be the three of them together so I'm gonna go ahead and create the store rank text now I used to use concatenate a lot but I found that it's easier for multi multi parking connotations that especially if it's three or more just use the antimatter sand or and symbol so that's gonna be [Music] the first one girls am at rank and any quotes of a space leading and trailing followed by the store count there we are take a look at this on the card yeah there we go let's just take a quick peek to see that actually working there you go you can see what it's ranked at for this store so Anchorage as an example is 76 best sales out of that and so on and so forth down the list so it you a good perspective of where they rank for that value and let me just kick that font up just a little bit good enough all right I'll keep going down and as we go through these it's going to start to get a little bit kind of cooler features and more dynamic in terms of the stuff we're going to be doing with Dax Syd total value be a pretty quick simple and easy one to calculate that simply just the sales amount that I'm going to bring in here and do one quick formatting I've never just like and maybe I'll just mention this is like a five second thing to you check it bothers me that the default formatting for cards when it's in the millions is like it rounds so much and I it's one of those things that gets it's it's almost too extreme of a rounding as a default setting you never understood why dope they don't include like at least one decimal as the default for that yeah so in Richard Vanderpool saying you know a lot of this we could now do in a table itself and actually use format it for that I like what you're doing now yeah yeah and exactly I'd only removed the displayed it's from I change it under the day level use I at a minimum if it's it to auto I'll kick it up to two decimal places at least and or if you know if there's room just might as well just play the full number and oh and get rid of those decimal places so those are let's just start going at this point beautiful okay here's the here's the fun one the e the monthly average now in some of these is you start the coaster see a little longer to save time I'm gonna copy and paste and cheat a little bit I do have a set of code but I will use but I'll still explain exactly what those are so what I want to do for this for monthly averages I really want to know per month is going to be the what is the average calculation or the average sales amount over any amount of time so that's actually going to be using an X function which is a it's a row iterator I'm gonna create like a virtual table for this that's going to calculate or iterate month over a month I'm gonna come up to measures like the new measure again I want this to be sales amount monthly average there's no wrong ways to name early backs calculations as long as the name at least alludes to or implies whatever is being calculated inside of it and as I said I'm gonna do an average simply I want to average over the month so I can't just have it over the original table because that's at the de level so I'm gonna average X will be a row iterator and what is my table gonna be I want my table to be a distinct list of the column Cosby month and year for that table right here that's exactly what this is calculating this is gonna return a distinct list of every month and year for my calendar table and what is it averaging it is gonna average sales amounts yeah we'll put a put a bit of format sugar on there and set that to currency and zero there we are and now if I replace that let me expand this out just so we can actually see the titles good enough tells amount monthly average there we go all right now this last one that I want to do is I want to be able to return and now this is actually something that can be done automatically in the in the KPI matrix where there is a there is a checkbox to say return latest but I in this case there is no function for that at least for the four measures in here to say return first or last once it's in a calculation form but on what I want to do is I want this whatever latest month is being calculated has had sales in it so that's what I want to calculate here I'm gonna break this out into two components I'm gonna do a I'm gonna first obtain the max month that any sales was or any whatever month had sales in and then after that I'm gonna do a calculation to return that they're doing no manager as well again that's one that I just copied a second ago and it's gonna calculate the maximum can year here and what I'm doing just to make sure that it never gets filtered by anything I always wanted to return and remain unfiltered which is why I'm going to do that all function in here and it's also filtering it to only those for the salesman because in my calendar table could have another two to three years of data in the future which is often how you should build a calendar table out you know usually wanted to stop at the day of because then it might be it'll become stale as soon as the the refresh pushes past that date now this will make sure that it only goes for it as far as there was ever in singles amounts all right the only other thing I'll grab right here okay one sec need to grab it from grab it from this guy there you go okay I want to create latest one sales calculation come up to new measures and there copypasta that again one more time would love it if those things automatically opened up for you whenever you copied long code all right say a sales latest and I'll just quickly run through so I'm gonna calculate my sales amount and I am going to filter my calendar table and I only wanted to calculate wire that month in a year is equal to them max month that I just calculated so this will dynamically always return the latest months go ahead and set that for currency one more time I think there's a fun calculation I'm I I had fun creating the KPI so that's gonna be next oh yeah I did like because it there there's like three or four processes to go into that but it's I created a cool time window for that there you go there's the latest months so we have eighty-three thousand for the latest month their average is about a hundred and fifty and get a lot of really good useful metrics related to this sales above here now you might be wondering like do you have to do this for every row and I I did you know I ended up probably with I'd say you know forty or fifty measures that were in the actual model itself but that's the great thing with Dax you know is they the cost in terms of storage to have them in your model is very little you only take a performance when they calculate I've AK I've built enterprise level power bi or or tabular as if the models that have had hundreds and I think the most that I've ever seen it sounds like a little bit over a thousand docs measures for just all business scenarios in there and they still ran fine if you ever end up with a report page that has a few hundred of them calculated all at once you might hit an issue but you know it's that's the great thing you can just you can throw as many in there as you need and as long as they're written well they all have it have a use for them so it's I really like the ability to just shock on them in as much as I need to create custom report pages like this all right just to do a quick check up to this point was there any questions that nobody had before I switched over to that KPI oh there's actually been a great as Bennett comments Paul says he loves that grid layout that you've got the MU Roger was asking which shapes but I think you actually showed that like you're using cards for these particular shapes itself and then Richard Vanderpool was saying oh we could've actually done a lot of this with the SVG's and the new mate our matrix categories and I said Yara you're you're going to get to that in the end you're going to show that the when we get a little bit later into it so not a lot of questions we have an answer a lot of comments oh hey exactly yeah and there are there are some ways to do things also utilizing the the new abilities in matrix tables themselves and yeah it goes back to the you know just at the end of that question the how much you want to be able to customize or add like the lines between so the reason I went for this specific approach is the fact that I think I can customize the UI more so like I get Myka do you can do literally anything with the way you've got it designed here exactly people think it's a gritty they think it's a some crazy table but that's just a formatting metaphor and it's a very powerful one that we all understand so I exactly yeah yeah you know in it at the end of the day I often goes back to the you know I are you building this for the for the client or does the client need that level of customization because they you know it the development time will be higher I find that for me the the payoff just comes from something that that's much more unique but then this is also you know if your customer has is more focused on the output and doesn't care as much about the visual sugar then yes certainly there are better there are cleaner quicker approaches that's well maybe not look as fancy but still deliver approximately the same results so it just all depends on the the business requirements at the end of the day and and what so what's prioritized alright so the let me you actually show the card here so this one I want to talk about a little bit so there's a couple of things that lead into this now the the title could be better named I just call the KPI because again this is kind of more of a template but what I've done here with these two charts that are inside of this is I've created a card that looks at or I should say a KPI that looks at the last six months trend of sales data on average and specifically the month over month growth so over the last six months was there a general upwards trend meaning greater than zero or a negative trend less than zero let me see if I can just show you the example on these I can find one quickly that has there we go yep yeah so you can see that sometimes they go down sometimes they go up the the one thing that I personally find someone annoying is the fact that the growth is given red and a decline is given blue I I don't know if that's more of a financial thing or not but I would you read to me is is usually indicative of negative potentially so it's the and I can't customize frustratingly it's still nice to show trends but I I think they could better colors to show positive versus negative growth right so I'm gonna come over to part two we're all lead off like I said I kind of liked I like to hop in my door and in time travel so I'm now in part two of the reports that I have which is had the rest of the visuals built out here for the other three rows of what we've already covered and now I'm going to talk about that KPI right here that's gonna be a few things that are gonna lead up to it so I'm first gonna create a Dax calculation that will let me calculate the prior month and from there I can create the month-over-month sales growth and then I'll finally end up with that six month trend that I will have is the actual thing that will then go into a keep yeah so there'd be a few tax measures that lead into this and kind of build off of each other but that's the other great thing with taxes they can be used is that like a Lego block or branching tree structure now I'm going to start with prior month remember buddy has ever done time intelligence you will you're probably familiar with the date add function and that's exactly what I'm gonna use here the PM for prior month sales amount me calculate sales amount I'm gonna use add and I'm and any time you're using time intelligence functions to it's really important that you have a separate calendar offer two reasons it's and of the primary one just as a quick a mention of this the fact that calendar table has have continuous states meaning there's no gaps anytime you use something against a records table or a sales table if there's missing periods where data was not recorded time intelligence calculations like the ad get really grumpy because there's missing spots where it you can't iterate over and I don't know the all the the actual coding stuff that goes into that but I know that you it will be a very painful process if you don't have a separate calendar table for these kind of things so I always recommend to people the first second table that you ever am or additional table that you add into a data model if at all possible have that be a calendar table if you're ever working with dates alright calendar table and what I won't want to do it's almost a misnomer that it's called bait ad is I really am subtracting so this really I in my opinion should be called bait modify but it's one of those like you know you work with a piece of software thousands of hours you eventually find the little things that that bug you so minus one and months I want to shift back one month close the parentheses and I'm skipping over a few of the formatting things in prior month is never going to be a little bit hard in this workbook so I I have no need to actually come up and set a format for that cuz it's designed more just to be a harvest the bed refer for other ones or a measure that would be built up all right so I got that and now I'm gonna do we're gonna create the mom measure for a month over month right sales them out nice and clean one so this is just gonna be low sales per sales amount there we are - prior month perfect another we have this I'm gonna go ahead and kill some of my code again I have written here it is right here here we are alright that's like new measure it did expand out this time one sec I need the name of it too just to be here you go didn't crack grab bite the whole thing all quickly just mention what I'm doing here oh I'm creating a six month trend the first thing I want to do is I want to determine what was my last bills date for my sales table and I'm with declaring that as a variable because that value is gonna be fixed doesn't need to change no I'm going to calculate that average month over months so let me see if I can draw here yep I want to know what my average monthly sales amount was specifically what is the beat range that I'm looking so I'm using base between and the the the bait range that I'm looking at month in year and it's gonna start here what is my first date it's gonna look at the last sales day for my sales table it's going to the start of the month for that and then it's gonna subtract five so it's gonna go back five months from the start and then it's gonna do the same thing with that last sales date here again from that variable up at the top and it's gonna look at the end of the month but that will now create a six month window that will roll with this so it's a rolling six month window for that month over month sales amount that is be an average alright and this are actually same thing I don't even need to format it because that is also going to be leading into a kpi here so the final one that I want to use and I'm also get a pop up a web page in a second because Matt Allington is actually the inspiration that I used for the unit car where he was the one that that led me on to it last fall when he wrote an article on it so let me first put in the KPI thing here and then I want to briefly show that site that actually walks you through how to create these which is pretty pretty fantastic um and at least at the time Chuck you can correct me if I'm wrong but the eunich our function was not available in Excel it's only it was only available in power bi you know if that's changed I believe that's still the case okay yeah so so this is something that is that is pertained only to power bi desktop and matrix tables because I I believe just pivot tables to not support the eunuch our function currently I don't know if it's in process or not but this this here is a power bi specific function from Dax there aren't that many because they behead those who've worked with both they are very similar platforms but there are a few things every once in a while where they did they add a function specifically for power bi that either will eventually come or doesn't have a plan to come over to excel I mean this is one of them so let me kind of break down the the pieces that we have here we're seeing again the variables at the top and a couple of them to call out so I have two variables here in here for chart increase and decrease and what this actually is is there's a function called unit car that can take any unit car number which is an assigned number that relates to an icon essentially that is returned and this is for that chart increase or chart decrease ones that you've seen and then I'm also I claim the variable for that six-month trend what is that value and then that leaves me after I've declared those a very nice clean statement here at the bottom using switch true which is essentially a nested if statement or case when in sequel and if my 6-month trend is less or greater than or equal to zero show increase and if it is essentially less than zero it actually should be that there you go otherwise they're double dipping show a sharp decrease so that's gonna be the KPI that I have right here all right many years that I've used Dax it's amazing how often and how many cool things they continue to add to it it just always thoroughly impresses me this is definitely one of them I I had I probably spent I would say a couple of hours just figuring out what emojis and you know cars I could put in here comically enough the the poop emoji that is on phones you can you can actually put that in power bi and it renders it in full color that is a power bi supported feature I just be careful when you try changing the colors so there's different different browsers and different platforms support the colors of those emojis and some don't so interesting yeah yeah so what watch out when you are searching like the background colors and whatnot those anyway sorry right you know yeah that's a great tip um and I realize also you can skip over what I just clicked on so by the way when I'm using right now I will hover over just to show the name I am using something called card with states by okay vis that is something that's already in here but that can be just searched or and downloaded directly in the in the Microsoft Store and it's it's a it's very similar to the the built in car but just with a lot more features to customize and tweak in terms of color States and things like that so that's what I want to use for this alright go ahead and just quickly hit that align up and snapping the grid makes it super easy for me alright now I'm gonna bring in let me search for that keep yond I'm look pretty good for time perfect and I'm gonna put that right here come on there we go now of things to clean up a little bit so right now it's a little too big to ax Eve to show up in there but come over to the handy to any paint roller kill that category label and now let's just drop this down a couple of sizes see if we can that's still visible one two three four keep going nope there you go beautiful and it returns it in there and just to show that it's working just quickly click on a few of these see if I can find one there we go yes pop - positive - negative yeah so we can see that it's working and tilted and as mentioned already like yeah these can also be included in in matrix tables just as well so if you actually have a table that you want to have with a lot more rows these can be included in there as a column and they are they work in the same method so sort of the internet but for theming rod you can you actually click on one of those lines so you can show them how you'd interline or add another line that your that your pseudo making it great for oh yeah not actually being point for so okay good good good okay yeah so the yeah I forget the term but the so likes equal bi likes to Causton there thanks for per formula syntax sugar i kind of call that design sugar so all the design sugar at the end of this which is the what makes this pop as a report that will be the the very last thing that will cover ya and cole says removing clutter or focusing context okay got it make sense yeah yeah yeah yeah exactly it what can you do to draw the eye basically alright so yeah you know the that KPI there will be our first step though the last thing i want to put on this one before i kind of hit that pop into bit of DeLorean again and fast-forward to the next section I want to show you about that how to add that spark line here to the very end section we can kind of already see preview it in some of these grayed out ones down below so it's one of those things where it seems like I'm promoting okay viz but it's the fact that they really do make the best custom visuals you know I've used a few others and at least the ones in the marketplace that I often will come back to there she's it's just a really really good quality one so they're they're spark lines fantastic and I want to use that as well so I'm going to create that and I think pretty much all of theirs are free except for one recent smart filter they unveiled but yeah I love the fact that 99% of the custom visuals that are in the that are available for power bi all open mostly open source and free free to use okay so let's do a couple of things here and I want to show you kind of a non-ideal way and then a more ideal way to do this cuz yeah we got time so in theory I could just put the date on the axis and what I want to show is I want to show the trend for sales amount or any metric that I have on my rows so I can add this as the values to but that is you can kind of tell us way too much granularity like it's that's that's noise at this point then rather than using the date and everything else is already at the month and year and you know again the fact that I have a calendar table is making this extremely easy to use I'm gonna use month and year which helps a lot there's a couple of little formatting tricks that I'm gonna do on this just to get it to pop a bit more I don't need the category label because that's already all my um at the beginning of this line so I'm gonna get rid of that I'm actually I'm gonna kick up to the year if I can scoot this down so we can watch here we go better gonna kick up the weight to about one higher and then in here under the points I want to turn on current point so we're gonna get three points now it's gonna show us our highest or lowest and whatever the latest one is just draws it out of it more and I I've actually found that I really like the area as well that's you can zoom in just a little bit but it does is it anything below and it kind of shades it in just to help further distinguish that I can give it a little bit of a color characteristics in there see if I can find one that's it looks a little bit better too there we go yeah spark lines are great and you know at the end too if we have time check I know you mentioned some things about spar clients in actual matrix tables as well with some images I'll be curious to see if you yeah if you have anything to show for one or two minutes of that but in general I think spark lines are great and useful thing to have just just to give you some some high level information and at a minimum it gets people asking questions which is always a good thing all right is there just before I hit the fast-forward button again was there any questions on that you have before going on to the next section no everything's good to go all right fast forward one more time Boop there you go and as you can see pop these in now same same concept but what I want to focus on is right here I want to add a little bit of variation to this and I want to create these something more akin to if I can come back out I wanna be able to do something like this I love to be able to create and especially for a return amount because I actually created some artificial metrics to this but I really want I have three buckets that I want to be able to call out for these values here for the return amount percentage which is the how much dollars were were returned versus dollars spent ie I want to create three general buckets I want to say that if it's 1.2 percent or higher that's in there red if it's between 0.8 percent and 1.2 percent like you can see in front of you that's gonna be yellow and anything point 8 percent or lower is gonna be colored green and then I also want to have a an arrow associated with it that will also be colored as well now there is something important to note about this that I have at least I have a general broad understanding of it but there are certain eunuch R symbols that are returned that aren't and that aren't an image or an icon they're essentially non alphanumeric text so in those cases they can still be color formatted I don't know if you know any further information on that check that the like these ones at the top are not at all a font in any way that is essentially an image B mode and I have no way of adjusting but these ones I can separately color via font color or States or things like that so there there is in my it from what I've seen two types of unit cards that can be turn ones that can be returned ones with the ability to get color shaded and ones that cannot so the color shade once is what we're just talking about a second ago and again on some platform just want to be careful of that and then I'll actually show you a pointer to how you can actually draw your own little lines that you're pointing to the top that those are images because you don't have to use static images you can actually dynamically draw those lines okay and it can can you do that in card visuals or would that be in a matrix table that would be a matrix or a table okay okay either of the the table visuals talked about it would be anything that you could actually render an image so you can actually go out and use anyway so I catch it let slicer or anything that you can actually just the image itself you can anything that you can get a URL to so anyway it's that way at the end of we have time perfect all right supports me yeah yeah it's you do custom apt or custom development if you ever have an inkling and want to create a card visual that returns an image that would be fantastic meaning just a box that returns a single image for Dax because I can kind of do this do that already with matrix tables but that would be that would be an amazing thing to have now that you can do Dax with images if there is there's a custom visual that just returns one single box image yep all right yeah let's let's apply a little syntax sugar or a format and design sugar to these things and put some states into here so I'm going to start out with and I'm actually gonna use this one because that's closest to my layout and I think I should be able to keep both on screen so I'm gonna change this from a card and I'm gonna go ahead and change that to cards with States well the same one that I used for these KPIs gotta change that to that and again I'm gonna go ahead and cut off the category label and I believe if I recall I think nineteen roughly has it at the same size there you go so yeah I mean at this point now they look nearly identical however the cards with States has a couple of other settings that we have in here so there is a section for actual States and then an option for effects and a level a whole bunch of cool things you can customize in your way more than I can cover probably not even a few things that I myself haven't even explored yet but I want to create those three states that I mentioned which is the 1.2 and above 0.8 21.2% and then anything below that so I'm going to say greater than or equal to and then you just walk your way down and create the states so state a it's gonna be red if it is greater than 1.2 greater than or equal to and I translate 0.01 to as a decimal I'm going to go ahead and give it an orange for kind of the the caution the zero zero eight and then lastly kind of be other way fun fact growing here changes that number which I've mistakenly done multiple times now an interesting little feature in there and then green and this is uh this is the one that took me a couple of times of thinking to figure it out but what you really want to do is you want to put a number in here so far out of the range that it buckets everything between point O eight and any theoretical number this could have a return so in my case I just put a very large negative number in there that's the best way that I've learned how to do it there might be a better way but that will just make sure that anything smaller than this and this number will never ever get this small so that's a very wide window to make sure that all those other categories are green because there isn't any actual spot in here for just else like if an if statement that last condition if no others are mint doesn't quite exist but this is this essentially is doing the same thing all right and you can see that it's colored it in red here and if you click around a couple of these other ones there we go you can see that it's changing the three colors we got the green the orange and the red popping up depending on which state we are experiencing on these cards now ideally out whenever I can I try to save time on having to redo things I wished my my dream of dreams that this format painter would work between custom visuals but alas it does not though the to me the workaround to save the time is go ahead and just delete those and then just copy and paste and then just swap it with swap the measures out because now all that extra work that really most of the effort was put in setting up those states now I can just swap it out for the values which I shall do here just super quick Bills amount actually that's a return edge err such a cheer this is the monthly that's there and and there you go save us a little bit of time to get to that point now this is the one where I actually had the most fun making of all of him is actually figuring out that I could create the the unit car plus do the color shading which it sounds like it's somewhat cautionary depending on your browser but I at least testing in chrome it's it's worked pretty well in in Firefox so I want to change this visual now here the three arrows that will correspond use three states it's gonna point down if it's in the red it's gonna kind of point to the right kind of neutral if it's in a yellow and then it will point up if it's green or all right kids what I said but the opposite direction I I remember it specifically actually having to go back and forth on this as a quick tangent because it's is it going in the direction that the data is literally flowing or is it going in the metaphorical direction that we think of up is better down as worse and I think I ended up deciding on a attaching the arrow direction with the physical direction that you know positive and negative but I I believe I switched it back and forth a few times hence the mental confusion that I still have to this day all right let me go ahead and cheat a little bit again and come down here I'm gonna copy my code gave myself a bit of time now this one already has everything needed for it because what I want to do for this is I want to base my new calculation that I'm doing here I want to base that off of the latest month so I want those two to correlate to each other and again I guy I know it's it's not necessarily the clearest clearest marked if I was to actually build this for a customer I might include a couple of extra sections in here maybe a text cop some further information to kind of explain what is going on with a KPI so it's not just ambiguous data but the the Doc's wouldn't change at all all right we are we're nearly spot-on for time to measure [Music] it expand sometimes and sometimes it doesn't I I can never you never figure out why it will open up on some and not others alright return percentage latest the very similar processes before this time I actually have three calculate or three variables being returned for three unit cars for the up the middle and down and whatever that latest percentages as well and then the calculations to the bottom follow the exact same logic that we had in the states the point one point two percent above the up and it's not equal to that takes the next next condition four point eight for the middle and then lastly returns that final one down if it is less than for that okay I'm going to go ahead and did I click enter I did not [Music] beautiful that into here now what this does is that returns it into this section and currently it is pointing up because that's the high number associated to this and we quickly just go to that data label and kick this up I think to I found thirty sixty the one thing that I've interestingly enough that I found is a kind of a weird little thing with this is you'll notice that it kind of pops around it kind of shifts up and down as you change the text size I was trying for a long time to figure out how to get it into the middle is it's kind of a bit top aligned the closest I could find is kicking this of large enough to about a size 36 get that approximately into the middle but there is no specific alignment that I found that works and really weirdly if I if I line it to the middle it moves it up if I line it to the top it moves it to the middle so I'm not quite sure why it's doing that but as long as the end result looks good you know don't ask questions I'll show you a quick thing and then we'll I'll explain why it's not work as we expect it to so I'm gonna very quickly just runs through those states again put them in here for the traffic light remember careful about scrolling scrolling down in here because you might end up changing this number when you do that one the hard way if you down to that and we're got a zero on this one one more zero there [Music] finally that - okay it looks like it's right I checked you can can you tell me why that's a not coloring appropriately yet or can you have a guess uh the constraints you haven't put the time the upper and the lower bounds I actually I put all the constraints in there yet but the the one thing that this is doing is it's expecting state a value and like the actual state that's in here right now is simply just three calculations but that's one of the reasons that I love the states just cards is you can have a measure return essentially it returns one output but it can base the the color coding on an entirely separate measure so that's that's actually a brand new feature that we've introduced in July where you can go ahead and say I want to actually reference this other column for the value itself yeah exactly and and good and matrix tables can do the same thing so I actually have a video that walks people through how to do conditional formatting based on text you can actually do have like category a B and C blue purple and orange and you can have rose color color code based on whatever category they're assigned to from from the data so like lots of cool ways to do that but in general the ability to display one thing but format on another is really really useful to have and like that and that's what this is this card has and then the matrix and a matrix table and I believe the regular table as well both have in power bi desktop so now we have that corresponding thing where all three of them are here see if I can quickly yep flip around a little bit and I yeah there we go I was trying to get the yellow ones yeah so it corresponds with three of them and we have both symbols and colors aligning with those values that that will change with the KPI now I know we're about 1053 so there's a couple of things that I'll briefly mention on the the formatting including those lines but it I'm gonna try to keep that to about three or four minutes just because I want to cover that last cool feature that they added and power bi desktop today um so let me quickly come over that's for didn't go to part four and I'll mention to you just a couple of things ooh that was a bit of a preview of the next one right here No yeah I think the most important ones really are and I think the the least intuitive they're not that I've found so a couple of things here that we have the the lines are in fact just lines that were added to the the table themselves they can be moved around and yeah there's a lot of overlay in power bi desktop but once you publish them especially if you hide the headers they they disappear so or at least the the borders around them disappear and that's just found on to the Home tab under shapes and you can just add a line to your table and I just you know you best basically resize them tape them up move them as as you need to to get them there and then if you need to rotate to get the horizontal ones now the one clinic kind of clever thing that I did to get this separation here I can go to the this section is I've actually created a bunch of text boxes with titles and an empty text box to create these little things that talk so I'll just quickly point that out that simply by adding any empty text box and I'll just quickly throw this over here yep so I don't put any text in there I just simply turn on a title just call this sales amount you know I'm breezing through a little bit I just want to make sure to save time for one thing there we go so something like that and then now sized appropriately these can now become little title cards for each one of these and the biggest thing that I like about that is that it allows for these gaps right here there we go let's try to hit the right button I love to have using here and you might even see that I have a little bit of a background color on mine especially like a gradient color in there what I will do at the end of this is in the YouTube comments I'll put a couple links in I actually have a few videos that walk you through how to add that background plus a couple of files that have a bunch of colors that are ingredient that I've been pre-made that you can download as well but all of these little things that are small things but individually don't matter that much but they add up into a final thing that I think really adds an additional pop and I like the the fact that you can kind of see a little bit of a border using that background between all of these which can to be achieved in other single catch-all visualizations but it's just one of those things again just how much do you want to customize and I really like customizing and having the freedom to do so which is why I had to take a lot of these approaches so yeah like that's I think basically covers the majority of the things that I wanted to talk about today we we went over these three sections in here which now that you have those is gonna allow you to do everything else below here I'm you know because the rest of its kind of rinse and repeat but it's I think it's a really useful layout and certainly something that I know a lot of people have asked for it just I do want to wrap up and cover that kind of new feature that that came out but is there any any questions that anybody had about what we went over before I hop over to the other category yeah Jesse wanted to know if you had a favorite app for actually looking up the emojis and those ASCII characters and we went out and found one called Unicode table actually we he found it a great link so I was actually just playing with this but I'm just curious if you've heard of your own favorite yeah yeah so there there are a couple of sites that um that have a good solid list of them I'll link to the other one so I don't spend time looking it up now but I will say that there is a there's a blog that the one that that led me on to this called Dax if you just google Dax you in a car function power bi it leads to Matt Ellington's post and he actually down here has a great short list of useful characters that are that could be relevant to specifically the power bi reports including the chart increasing and decreasing a couple of emojis thumbs up thumbs downs things that would be relevant in reports but there are thousands of them out there plus to use but this is a good starting point all right good questions yeah let me hop over this yeah so I know we only got a couple minutes but you can see the gorgeous visages of myself and Chuck here in front of you so what they have and the they released in this month's update with power bi is the ability to categorize to acts by by categories like and I was something previously only available to do on columns and and rows in there where you can return an image but it was a fixed row assigned to something so what I've done is I actually have this related to KPI so Dan Chuck it's the down arrow read you see the up arrow here you know he looks a little bit frustrated in the in this photo too yeah I'm normally frustrated right yeah so you know and you can watch here too as I as I click around you'll see that these are changing because these are Dax calculations that are returning one of two URLs using the switch function so it's operating almost identically is this KPI so I can even show there you go check out that measure because that actually see what he's doing there he's actually going out oh wait where are you concatenated to the URL itself I think I can show you the this is the measure that I have rareness there it is okay so you can see here where he's actually just hard-coded one one or the other but he could obviously built that string so imagine where you're actually not building a string to a jpg but you're actually building a string to an SVG file because you can actually take arguments of hey I want it to be this high this with starting at this location oh man oh yeah so then he opens up I'm gonna put a link in there and wouldn't Tim ever field Evers build I'm pronouncing it horribly I apologize David will come back and walks into a blog post all right weapon aren't exactly that but using that whole paradigm you should've been use my man coding and build your own images I mean just to go absolutely crazy crazy so anyways I'm gonna leave it to give it back to read to close yeah so in general that I connect that about covers that I'll I'm actually kind of excited that I got to show this at the end of it it's you know I'm its anytime a new power bi feature comes out it's like a mini Christmas so yeah there's a lot of really great ways to customize stuff you know I'm the this template I will make sure there's gonna be a file download link that will link back to my site that you guys are welcome to download this as a file and and use as you want rip it apart and you know if you find things useful for it feel free to continue to you know just to use it adapt with it but yeah overall you know for all of you that are joining a thing thanks for listening in I do have a blog subscription that you know you're welcome to go check out it usually as about once a week has a video series on various stuff like this and pretty much anything related to power bi a lot of useful features you know and all a really really good content on there but otherwise yeah I know we're past the time so I just wanted to to say you have a great rest of your days and I'm sure I'll see you somewhere in the near future on the interwebs all right well thank you rush Reid and I go ahead and close it and bring it to a close you have a great time cool

Channel: Microsoft Power BI

Views: 62,738

Rating: 4.9513888 out of 5

Keywords: Power BI, PowerBI, Business Intelligence, Business Analytics, Analytics, data, business data, Microsoft, #hangoutsonair, Hangouts On Air, #hoa

Id: Sq4oRnCGUAE

Length: 59min 50sec (3590 seconds)

Published: Thu Aug 16 2018