Visualization Tips and Tricks in Power BI

all right I think it's live guys if you can hear me just give me a shout out in the comments that you're able to listen to me you can watch my video as well hey Darren what's up good to see you here man hey I actually see my father here hi dad how are you doing I'm good hey Dennis good to see here alright guys I just want you to give a quick intro about where are you from just give me a shout out in the comments that where are you joining from the world and thank you so much for kind of taking our time on the weekend and joining in so thanks justjust give me out just give me a shout out in the comments I'd love to hear where are you from thanks Aaron just give me a little comment in the comment box I'd love to hear where are you from hey no no good to have you here from Portugal what time is it there hey the banker good to have you here they praveen good to have you but to have people from Israel as well I'm happy from Germany soul good to have you here man hey at that there what's up welcome Roenick welcome sandy good to have you here man no no hi what's up hey Shivangi good to have you here alright guys as you come in why don't you just maybe poppin in the comment box where are you from and of course I love to hear in case you have been working with power bi already how long has it been that you've been working at power bi hey David good to have you so I'll probably just maybe start in about two to four minutes still time about to what minutes so I'll just probably start in about two minutes hey widget good to have you I'm filing calling you widget all right for those of you who are new and probably coming here for the first time and you do not know who I am and the work that I do so first of all thank you so much for coming on to the webinar and maybe you are interested in to learn about power bi visualizations and that's what I'm going to talk about but a little bit about me I my name is of course Sandeep Chhabra and I stay in Gurgaon which is near to the capital region of India it's near Delhi if you have heard of that and I actually started out in the financial services industry in Mumbai I was working for a boutique investment bank and I kind of left my job in 2013 and then I kind of started just to teach people how do you work effectively on spreadsheets because my work was a lot of spreadsheet work and number crunching work and I just probably learned that skill a little better than the others and thought I just maybe thought I just kind of share that with the other people and that's how I can start it with goodly if you are curious about the name goodly it's actually my father's creation who's also somewhere there in the in the people who have joined in so you should actually thank him for that he actually I lent the name from him so yeah I started out and you know I my focus was a lot of financial excel and teaching finance people a lot of excel and kind of stuff but then I kind of deviated from that and kind of elevated in myself and started learning how to people use Excel in operations and other stuff and I kind of then moved on to learning power bi I think about in 2015 that's when I first laid my hands on the power tools of Excel that's when I picked up our query and axe it was a pretty tough time actually learning Dax when I started with Dax it was really really difficult and I think in 2015 there wasn't there was not a lot of resources out there not a lot of blogs out there not a lot of good material out there that can actually teach you teach you to actually well so I kind of struggled a lot I I actually remember that waking up nights because I was just making a small mistake in a comma problem I was just terrible so it's better that I don't recall those days but yeah so I kind of work my way up from there and since now my work is a lot of training and consulting to companies I travel a lot everywhere in India no not now as of now but yeah although I do travel a lot and I do these workshops live workshops for companies and do consulting assignments for power bi you know a company-wide implementation of power bi where I kind of write a lot of backs for them create dashboards create reports and that's what I do and side by side I obviously run this blog which is good lead on co-wrote in and that's how you got to know about me maybe and that's where I write about everything that I know about power bi you know interesting stuff that you can probably follow along and formulas and a lot of interesting stuff about power bi solutions to different problems and things that I so yeah that's a little thing about me hopefully it a lot of people have started asking me that hey what should I do I'm kind of on the verge of losing my job and I cannot say about a lot of people but especially for the people in the data industry and people analyzing numbers I will just say that one thing that is stayed with me always is you know sort of the the I can say that my interest to kind of rack up skills repeatedly one after the other and that's what I will recommend you strongly so I'm not too sure about what's gonna happen in the future but if you worried a lot of people have been asking me emailing me people that I don't know about as well and I just want to give like a general thing that just don't worry and just keeps killing yourself and when things will be okay and thus settle down so actually you're going to do absolutely fine so it just keeps killing yourself alright hey Raj what's up good to have you here all right I think we have a lot of people about 50 odd people I'm glad so many people could make it and thanks once again for coming up here on the webinar and let's just kind of begin I'm just gonna actually start my screen all right so let's just do that let's just do that and let me know if you're able to take a look at my screen and geyser one very very important thing I want you to download the files that we will be working on there's a link underneath the video so just go to that link and it will lead you to a place where you can download all the files that we'll be working on there's just one bar VI file and you can download that this this is actually a completed power bi file so you can just enjoy this and maybe follow along once I am doing things on the screen alright now when we kind of talked about visualizations in power bi my first advice to everybody is to stop thinking about visuals when you're asked to make a report that's what you should do at the start of it at the start of creating any report in power bi what you should first think about is how do I bring you know kind of do very very smart and intelligent calculation that will add a lot of meaning to the report if you first start thinking about visualizations then you kind of get caught in you know some questions that will actually shoot you should handle them later which are questions like what color should I use what chart should I use should I buy the visual or not and all that kind of stuff now you will add a lot of good value to your visualizations if you are able to write good tax and if you're able to write Dax you will be your dashboard would be full of metrics and analysis that other people cannot do or they are hard to do so I will rather give you a very counterintuitive advice to focus your attention at the start of that you know kind of report creation on building very intelligent you know calculations like things that your management wants but maybe they haven't been calculated as of yet because maybe the data was too heavy or they were just doing it in Excel and Excel doesn't really have that kind of sophistication even if you have done that in Excel maybe it's too clumsy and there are a lot of hidden workings around that the person was made it he can only understand it so I would rather give that advice to you that please focus on bringing a lot of intelligence into your calculations using tags rather than actually focusing on the visuals and believe it or not for all the templates and the visualizations and the graphs and the charts and the consulting assignments that I have done so far I have just used the standard visuals which are actually there in the visualization library I have never actually bought any visual for and probably barring one or two times where they were the need was very specific I have never even recommended that you actually go here and buy a visual these are good enough visuals there are actually some good visuals around but yeah I mean you can actually do some pretty decent job with these two these visuals as well all right now let's begin and I am going to do a very very simple kind of suggestion to everybody is that I'm just going to open up let's say a dashboard this is one of the dashboards that I recently created its own coronavirus tracking coronavirus in India I cannot refresh this every single day so you get the updated numbers it's there on my blog in case you want to download that you can even get the power bi file it has some projections and some nice little stuff right here so the trick is let's say for example you are maybe working on a dashboard either into your power bi file or maybe you are working on the dashboard maybe on the web you're taking a look at the dashboard and maybe you don't want you want to take a look at the actual numbers so if you take a look at the projections charge here I do get to see these lines in the projections but maybe if I hover the mouse is then when I actually get to see the numbers I don't get to see all the numbers at once so what you can do is you can actually select the visual so let me just kind of select the visual here just kind of working slow one second so let's just say that all right so I'll just maybe show it to you here so it's not actually showing up the selection for some reason on the screen so let's just say that I end up selecting this visual which is which is a line chart and the line chart has also got some projections here that how the cases are going to grow so I select this particular visual and I use the shortcut alt shift f11 and what this is going to do is any visual of power bi it's gonna actually convert that into a table structure and you can actually end up seeing the exact numbers as to what these numbers are with the labels and stuff and you can actually go back to the report now this little trick actually works in power bi which is you know the web version of it I have not actually opened barbiere this is a web published report and it actually works in power bi desktop so maybe someone shares a report you want to make it more explicit you actually want to read the numbers what the numbers are it's a fantastic way to kind of uncluttered the chart the chart is too small and maybe you just want to take a look at that so even if I actually click on the map here and do alt shift f11 the entire map is going to be converted into a table structure and you can actually take a look I'm not sure what's happening here but it actually does so maybe I selected the thing here so let's just write that again so I do it here alt shift f11 yeah so it works so you can see that it at the map actually converts here into a table and this is actually great all right that was easy and maybe I just kind of start with other interesting tips and now we kind of hop over to power bi the file that I have for here recently I think one or two editions ago Microsoft launched a kind of they gave an update into power bi and they said that hey now you can actually actually redo your titles of the chart and you can make them dynamic right so let me just kind of make one chart and show to you what up whatever I mean so here I have a very simple data model if I just show it to you here we have a sales table here simple standard sales table nothing to complicate it we have here a transaction ID column that means one single row of the sales table means one single transaction and we have a products table here and obviously the product code here is linked to the sales table product ID right here which is right here and obviously we have a standard date table which I actually like to call it as a calendar table and that's pretty standard a couple of columns here and that's how my data model looks as of now so you can see that we have products we have calendar and we have sales right here so they are connected like that and I'm gonna make a very simple chart right here so I go to the fields here and and I just drop in the category right here and I have already written a very simple measure for total sales I'd man maybe I can just show it to you that so right here I have total sales measure which is actually nothing but very very simple ATS actually the vlookup of the price and I multiply the price with the units and then I sum it all up that's my total sales measure and I actually drag that total sales measure into my visualization here and this is as of now a table which I would like to have it as a chart and I convert that as a chart now you can see that when I make a chart it gives a pretty standard title right here which actually delivers the title as total sales by category and you know what maybe I want a dynamic title in the title itself I'd like to highlight the maximum category that I have so as of now if you take a look at this title which is actually the title that I've already worked on here the the title is mid-segment topped with a $850 the sale so if you kind of hover your mouse on mid-segment product you will see that mid segment is actually 850 and the title is actually going to change so if I maybe just hop over to the month of August and take a look at that you will see that the title just changed and says mid-segment again topped with four hundred and one kind of value in sales if I kind of hover the mouse on top of that you can see that mid-segment is again four hundred and one so how do you actually kind of build a dynamic title and that kind of changes with the slicers and other things other things that you have in the power bi report so what I'm going to do is I'm gonna use conditional formatting and maybe I'll just write a measure I already have written one measure right here which is this and maybe I'll just kind of write it from the scratch so you can just take you know carry it along all right so let's just understand so what I'm trying to do here is first of all I am trying to get the name of the best-selling category so let me first try to get the name of the category right so I'm just gonna create a new measure and let's just call this measure as dynamic title all right and I'm gonna call I'm gonna call the function called concatenate X and if you take a look at the concatenate X function it asks you for a name of the tables hey which table would you like to call the category from and obviously the category of the product category simply means is it a premium product is it a mid segment product is it a low segment product so that category is actually there in the in the products table and the expression here is going to be the product category and I can close the bracket or close the bracket press ENTER and concatenate X will actually go to the products table you know we just show it to you so it will actually go to this products table and in the products table if you take a look we have categories so we have a giveaway we have a low end we have a mid segment we have a premium and you kind of combine all these categories together right so that's what we'll do as of now so we are still kind of working with the with the measure here so I just maybe dragged that measure right here and I obviously have to convert that into a kind of card here so that you can see it and you can see that giveaway is concatenated with low end concatenated with mid segment and again again the concatenation is happening and since the category of multiple products can be repeated and so you have you know kind of repetitions here and I don't want to have the repetitions so what I will do is as we have come back to my measure and in my measure I'm gonna say hey rather than actually repeating the category why don't you kind of make the categories unique so I'll add the values function around the category what the values function is going to do is it's going to go to the category column remove two duplicates and just have unique categories so now we have unique categories only and that is going to be combined in the concatenate X let's just take a look at the result so we have now unique category so we have give away we have a low-end and we have a mid segment and a premium product all of these are kind of kind of stuck to one another you know so maybe I'd like to kind of use the the other input which is a delimiter right here it means that how would you like to separate one concatenation from the other and maybe I'd like to use the end here so and like one category and the other category and I press ENTER and you can see that it's gonna kind of put a space before and after and it's gonna have the and and and alright now I'm getting the names of all categories but hey I don't need the names of all categories I need the names of one category which is actually the top-selling category so here is what I'm gonna do this calculation should actually work for only one top-selling category alright so what I'm gonna do is I'm gonna write the calculate function the first part of the calculator is obviously what would you like to calculate so I would like to calculate this thing but this thing is as of now being calculated for you know the entire table and I don't really want to have that I just want this calculation to happen for only one category so I'm just going to maybe write top-end function here the top and function there is an N value here so I just want one n value the table is again going to be the products table and in the products table I just want the actually we just have to write the values function here so values and I just want the category right here close the bracket and the order by expression is going to be total sales all right let me just explain that to you what did I just do so the topping function actually accepts two three inputs the first part of top n is how many top rows do you want do you want one row do you want to Rose do you want three-dose since I'm looking at just one top category of the product I will just say that I will just want one row the next part of the top end function is that you would you want one row which table I don't want one row from the entire products table I actually want one row from one column of the products table which is the category column and that - after removing duplicates hence I write the values function and then just don't give me any top row just give me the top row in the order of total sales so the order my expression the the way in each in which it will order the top row is going to be total sales expression that's what I do this is going to give me top one row and in just in case there is a clash or there's a tie between two products or two categories which are and actually having the same sales it's actually going to give you two rows and in that case you will land up with two rows and you will end up having two Conville use concatenate isn't actually the good thing that that's what we want so I'm going to press ENTER drag it down unless you see what happens and you can see that now if I just kind of zoom out a bit I get to see only one category which is actually mid segment that's that's good now I also want this label to be more explanatory like you know mid segment my title of the chart cannot just be mid segment it has to be like mid segment topped with extorter of sales so that's a little labeling is required so that's what I'm gonna do so I'm gonna come to this measure and maybe right here I'm just going to come here and say that I am still willing to do the calculation for only one row and this is the calculation which actually gives me the label this is good enough but along with the label I want to write some other stuff so I'm gonna say mid segment and I'm gonna obviously write the ampersand which is the concatenation symbol topped with topped with and then I will say again and % and then I will write the total sales measure so whatever total sales you have right so that's what I will write so that's it and maybe you know what I can also format the total sales that I control the way it looks on the screen and I'll say that hey why don't you maybe apply a dollar sign to the total sales and also maybe a little comma is gonna be good so that's the little format function so format whatever value total sales returns and then you add dollar and also apply commas just in case you have any all right so that's what I write hopefully it should work so let's just press ENTER did you see what happens here so mid-segment topped with 401 and now once I have generated you know this kind of text field now this text field is gonna go sit inside the title of the chart so I'm just gonna click on the chart right here go to the visualizations here and in the visualizations I'll kind of hop over to the title right here and in the title which is currently on you can see there's a little FX button here now this FX wherever you see in power bi it clearly signifies that this is conditionally formatted I mean you can actually use conditional formatting and measures of Dax to be able to format this in the custom way so I'm just rather than writing the standard title right here I'm just going to actually use the FX button right here and it says hey field value the field value is actually we have calculated the fee value and I'm just gonna write the dynamic title measure here right here and I'm gonna say okay now one very very critical thing let's say for example most people are going to get stuck here the measure that you write in order to generate the dynamic title needs to deliver you a text value if it delivers you a number it's not gonna be valid here so let's just say that total sales which is actually a measure delivers me a number that means the output of that is a numeric format and if I try to select a number here so if I just say total sales here you can see that it will not really allow me to select that so it actually needs to deliver your text to be able to select that so that's a little catch here you need to be careful about that okay so I'm just going to write dynamic title right here and say okay and now you can see that the same title appears right here maybe I can just name is the screen and that's what you get to see here now when you kind of change that to July or to any other month it's just going to be dynamic now there is a catch to this trick okay I've found it later I'm still not sure how to solve that but I'll kind of let you know just to be sure as to you you were aware of that okay so watch as of now so maybe just get some space here okay all right so I am just gonna make another visualization real quick maybe another kind of a bar chart and maybe just put something else let's just say Auto type in my axis and let's just put total sales against that and you can see that we have two different types of orders that we get we get a no support order and we get priority orders now let's just say that along with these slices we also have another voiceover and obviously you understand that once you kind of click on the visual everything cross filter so once I click on the visual you will see that the mid-segment here is actually not 943 it's actually 861 and the title still doesn't change so I'm not sure why does it happen because that's the measure that we created and if the if the value is changing in the measure it should actually also change it in the chart title but it doesn't change that means any kind of cross filters coming from any other visualizations that you might have on the screen they will not actually filter it by the cross across like the cross highlighting or the cross filtering but they will definitely respect the filter so whether you have a drop down filter or like a trick let's style filter whatever filter you have it will accept that but it will not kind of accept any cross filtering from the visualizations you can see that the title here is working fine it's actually 347 if I kind of hover my mouse on top of that that's the value that I see against highlighted you can see 347 but it doesn't really reflect her in the it's still the full full bar value not really sure I'd have to figure it out so in case somebody knows that in the comments maybe you can just help us all here alright so just be worried of that maybe you can kind of write that somewhere in the in the title as to explain your users that this will not get cross filtered as you filter your your charts and stuff alright let's move on this one is superb and amazing all right so let me just kind of make make a chart so let's just copy this chart the one that I have already made so ctrl C on that and come here and ctrl V alright if you have been using power bi for a while now and if you don't know what tooltips are you are absolutely missing out on a very very big thing I mean visualization here so it's kind of very subtle but gives you a lot of lot of value that you can actually provide to the user who's actually reading the visualization so if I just kind of hover my mouse on top of that you can see that there's a little black box that appears that actually gives me the same value which is one five five nine which is actually the value right here now this is cool enough but what you can do is you can actually also expand these tooltips and you can add more things here I generally tend to do that so maybe first for everybody who doesn't know about tooltips maybe I can just quick cover that real quick so let's just say that from my sales I will also find out total units total units is nothing but the sum of units column in the sales table really simple measure and that I am going to add that in my tooltips so I select the visual I carry the total units right here and I carry that to the tooltips right here now along with total sales I also will get to see that how much units have you sold in the mid-segment category and you can have now two things shown here so a lot of these kind of what-if questions what if somebody asks me there what if somebody asks me that which is kind of related to the topic that you're already showing in your dashboard you can actually put all of them together in a tooltip and it's kind of brilliant now these tooltips are believed to be not customizable but they are absolutely customizable you can customize these tooltips in any way that you can alike alright so as of now this tooltip when I hover the mouse on on the top of the chart it actually gives me a black box on which there are numbers written on top of that but hey I don't want that I will design my own tooltip and the way that it should look like on the screen so let's just do that the first thing that you need is you need a separate sheet where you will design your own tooltip so pay attention here so I'm just gonna maybe add another sheet here which is actually page one I'm just gonna double click here and call this as my tooltip all right that's the name that I give the name doesn't matter actually you can give any name okay once you have created the page as my tooltip you go to the visualizations pane here and in the visualizations pane make sure that nothing is on the screen and you go to the format tab once you go to the format tab and nothing is on the screen you're actually formatting the page itself alright so this entire page so I'm just going to go to the page information and I will make this entire page as a tooltip so I will turn on the tooltip that's my first thing the second thing that I do is I go to the page size and I say rather than having a 16 by 9 which is a pretty large size I would want to have this small like a small tooltip that appears so I will say that the type is going to be a tutor all right so this becomes small and this is still not small because this is kind of expanded to the full scale of the page what the other thing that I have to do is I have to come to the View tab and then I have to go page view and then I have to click on actual size and this becomes really really small now the thing is that I'm kind of still not using the new layout of Bobbi I in case you have recently installed power bi and you have a beta to your power bi you will find that the layout of power bi has changed and the things that I am doing somewhere in the View tab might be at different places in your ba-ba windows to just find that although I have updated my power bi but I have not updated the new view because I like to use this quick access toolbar that I'm very very fond of and I don't want it to go away so all right once you have created this little tooltip page now we start designing if we start putting things that I'd like to see so maybe from the fields I'll go here and in the sales table I have a channel right so that's the channel that I have so we have three channels of sales we sell it through affiliates we sell it through organic medium people just come and buy from us and we sell it through promotions right so three channels we have and against the three channels i'd like to input total sales so these are my measures and you know what i would like to convert that into a bar chart all right so I make a bar chart and maybe I also want to have data labels on top of that so I get to see the labels I don't want to have these 23,000 Kay and stuff like that so I want the denomination to be not thousands or something none so I get to see the full value all right that is the tool tip that I design if you want to clean this up I suggest that you clean to clean this up you remove the total sales here you remove the axis here you do all of that stuff so that the visual is large and people can actually see the visual neat and clean you just get rid of the titles all that kind of stuff you can also kind of rename the title now I go back to my page here and you know what I'm going to attach the tooltip that I just created to this chart so as of now I get the standard tooltip so I'm just gonna click on the tooltip I go to the visualizations I come to the format in the format I go down to the tooltip now in the to tip you can see that the tooltip is currently on but he here this should actually be report page the type and the page here should actually be my tooltip that's the two tip that I just created so yeah I can just I just get I just got to know that I have a my own picture on the side so you would not be able to see whatever I'm doing on the corner of the screen so hang on sorry about that so hopefully you'll be able to take a look at now okay so here I click on the visual I go to the format tab in the format tab I have a tooltip right here in the tooltip make sure that where is that the reports type is report page and the page is my tooltip that's the tooltip that I just created I'm gonna do that you can see that I have linked that now I come back to the entire screen and now let's just take a look at the visual now if I hover my mouse on top of that I see that in the mid segment how has the product perform across three channels and this will obviously change this will obviously change and the tooltip will actually respect any kind of filters that will come in from any other things right here and it will also respect any kind of cross filters that might just come in so that is totally cool all right all right so I already love this actually you may be I'm just going to show you at a couple of places where I have used this very actively so right here so if you just actually take a look at this dashboard that I created let me just do a full screen here this was a recruitment dashboard that I created a while ago for recruiters to manage their recruitment process effectively and this has all the details into it and now if you kind of take a look at this particular dashboard if you hover the mouse on top of any particular metric right here it actually shows you a custom tooltip that what position are you hiring for you know what location are you hiring for who's the recruiter and stuff like that if you go on to the next page actually it actually gives you even more information about the candidate once you hover the mouse on top of that so you can see that it gives you information about the candidate where the candidate has you know source from it even gives you information about the nodes that you took while interviewing the candidate so you can see that we have some interview notes as well I have also used that at one other place here which is where I have kind of used some subscriptions so in case you buy some subscriptions to do some hiring so it also tells you that your subscription is running from which state to which state these are all so these are all calculations driven from Dax then I put them on a tooltip page and then carried forward and link them to my to my visual alright so that is one thing all right now this is actually a table like a matrix here so you actually have to turn on the the tooltip in a matrix visualization so let me just show you that real quick so if I just come back to power bi and just in case if you are happening to work with let's say a matrix visualization which is what we will discuss next the tooltips are not by default on so you have to come here and then go to the visualizations and right here in the format tab scroll down and right here you'll have to first turn on the tooltip and then link it alright coming to the next trick the next trick is about pivot table formatting so or maybe any kind of table formatting a lot of people actually end up creating tabular reports or tabular information and I think tabular information is fantastic because you get to see you get to show a lot of information in just one small table format and you know just kind of format the table nicely so that it looks a little pleasing otherwise tabular information is great for giving accurate information and giving you loads of information so maybe I'll just show you a couple of tricks in in pivot tables that I have been asked a lot so let's just say that first of all if you want to create a pivot table likes layout in power bi you do not pick up a table you actually pick up a matrix visualization which is sort of the pivot table for power bi so I'm just gonna click on the matrix visualization and you get to see rows columns and values the same things that you actually see it in a pivot table in Excel so I'm just going to go to the fields here and I'll maybe go to the total sales right here and drag that right here and maybe I'll also add my ear from the calendar and I will also add let's say that category from the products table so category added right here all right now this is actually a pivot table and maybe I just don't want to have the categories on the columns I would like to have it on the rows so I have it on the rows all right now you can see that it obviously starts to show this and you can see the plus and the minus sign here and you can actually expand 2011 and you can take a look at the categories and things like that now a lot of people would want this in a classic layout cell that's what you know if you have been traveling from excel and you've landed into power bi you know what a classic layout which means which actually means that you want separate column for separate fields so we have 2011 which is actually a year I won't separate column for that and category should be placed in a separate column and then total sales should be coming in the third column how do you do that in power bi so we actually select the the matrix which is the pivot table of power bi you go to the visualizations and over here in the format tab you write the word step ste B once you write the word step it actually shows you the step layout which is actually the sort of the classic layout for power bi so you have the step layout which you can actually turn it off and once the step layout is off you actually get the layout that you actually want so that's what it shows you and obviously if you want to have these plus/minus button turned off they actually good they actually help you expand or contract the pivot table but in case you don't want to give the user the ability to contract the pivot table or collapse the pivot table you can actually come here and write a plus sign here so just write a plus sign and you get these plus minus icons here which you can actually turn them off and they are off your pivot table and your pivot table looks like this there is another feature that I would like to talk about which is actually right here which is in the grid here in the grid there is a feature of padding row padding which actually means the spacing between the rows or lot of people don't know about it and I'd like to kind of pad my rows nicely and give them ample amount of space between one another so that the numbers don't look clock with one another so that actually spaces out the pivot table and the numbers are more legible to anybody who's reading them and you can also increase the font size just in case you want so from here in the grid again right in here in the grid you can just kind of go here and maybe increase increase the font size here so right here just increase the font size so maybe I'll just make it 13 or something all right and this starts to become a lot more legible all right one thing that I'm kind of kind of pissed off about power bi is that you don't have the ability to maybe write anything in your custom font I mean I cannot use the fonts that I have installed on my laptop to write anything in power bi that's that's not good actually and I'd also don't have the ability to draw anything on the screen all that I can draw is triangles and rectangles and that kind of standard shapes but I don't have the ability to draw anything on PowerPoint like PowerPoint in power bi that's kind of sad so there is a workaround although which is there in power bi so you can see that I have kind of laid out this screen in a slide format you can see that it's like a slide there's a little shade at the back here which you know in case you want that you can have that and all of these visuals and you can cross filter and you can kind of call it as a slide format only so how do you actually create this stuff in power BM so what you can do is you can you actually kind of feel funny even telling you that but yeah that's that's the workaround that I have for you so you can actually create that in PowerPoint I mean you can actually literally go to PowerPoint so this is also sixteen by nine the size of your canvas here is sixteen by nine and the slide the size of the slide here is also sixteen by nine so if you end up doing everything in the space of the white space of the slide here it will end up fitting right there in your canvass of Pompeii so whatever you want to draw you can actually draw it here you can actually see that this is a text here and I can pick up whatever font that I want to have this is actually a shape here I can just make whatever shape in case you would like to add company logo just kind of make some boxes right here just whatever I mean you would like to do you know just give your dashboard a little look and feel you can just do that right here and then once you have done that you right click on this particular thing which is actually a group of objects and then you save this as a picture anywhere on your computer and then you import that into power bi so that's what I have done so if I actually come here I can go to the Home tab and I can click on the image right here and I can import the images from Part B and that's the image that I have actually imported into power bi so if as you double click here this image comes into power bi is very small I can actually make it larger here and that's the image that I had here so that's what I had it and you can obviously kind of make the image go back and start making visuals on top of that so in the format tab and you can just send it back send so I sent it back and you can make the visuals on top of that all right so that's what I have found so actually I use this trick in one of my recent dashboards I'd like to show it to you it's pretty interesting actually so here in the coronavirus - put that I created so I wanted to write the word in in a stylish format in the font that I like which I use it sometimes in my in my graphics and I was not able to do that so the hence I kind of wrote all of this stuff in my own layout and I also kind of took a picture off of somewhat virus looking like like a thing and I gonna kind of build OHS out of that and I created this cycle which is the only image that I have on the screen and that's what I have so it just gives you a little custom look and feel and then you get attached it to your power bi reports looks really really cool all right that was my next tip actually the other tip which I would like to share with you is actually this visual which is actually top performing products visualized it's not actually a visual it's actually like a like a Dax function that I have written and then actually creates this particular visual so maybe I'll just show it to you what this visual is so let's just say just hover on top of this so here in this particular visual it's not really a visual it's actually a very simple table it says three best performing products it has the names of the three products it has the sales of all these three products and it has the total of all of these threes numbers down it at the bottom right here now if you end up clicking on any any of these you know kind of slicers or anything this visual is actually going to update so right now take a look at the total is about $13,000 if as you take a look at the top three products of 2011 the number is definitely going to change it for $6,800 so this is absolutely amazing a lot of people have asked me how do you do that so this is pretty interesting actually so maybe this involves a lot of tax functions and writing out there a lot of that stuff so I have it here and you can actually take a look at the functions that I have written right here this is the the function the tax code which actually gives me the the names of the products so which is right here and then I have another function which actually gives me the sales value and then I have another another little kind of function which actually creates a small little chart that is going to get refreshed automatically so if I kind of get these two filters so if I just copy these filters from the top and just actually paste them right here don't sink the filters just paste them right here and if you just take a look at how this works so zoom in so take a look if I actually click on 2012 even the charts will slightly change and give you an updated value so this is pretty interesting actually so I want to cover this with you briefly if time permits in the end this is slightly technical and you can this will actually give you a lot of value that you can actually give into your dashboard so anybody clicks on anything and then you can see the top three products by the month by a category by the quarter by the Year by whatever right so this is pretty interesting so I as of now move on to another tip which is interactivity of slicers and then I'll kind of maybe if time permits come back here and then maybe just take you through the top analytics which would all right um a lot of people actually don't know about it so maybe I'll just share this is actually a very very interesting tip take a look so I have two slices in the top one is for the year the other one is for the month and you can see that all of these visuals are filtered by the month of 2012 and June but if you take a look at the line chart here the line shot actually shows me all the months here right it shows me all the months it's not actually getting filtered by June I even if I click on July everything else gets filtered but the line chart so there is an option in power bi where you can turn off the interactivity between the two visuals right so the how do I do that let me just kind of delete this particular thing and do it again show to you so first of all I create a line chart so I create a line chart keep it here on the screen in the line shot obviously I have a measure which is total sales I just drop it right here and I where is my months so I take the months put it in the chart comes up right here and you can see that as of now as of now this chart which is actually a line chart is not really a line chart because you're showing you just one dot because it's actually respecting the filter taking that filter to the chart if I click on September this will change is September and so on and so forth on have actually there for September so let's just click on January so it's so used sorry it shows you data for January shows you data for February March so on and so forth what I want to do in my dashboard is that I want to have a line chart that no matter that the user actually clicks on a particular month I also want to show the sales of the entire year as to how those sales has been trending throughout the year even though the person actually clicks on the line charm so I would like to have that capability done so what I will do is I will actually click on the slicer that's my one then I go to the format tab and I have the option of edit interactions which is where I can actually turn off the interactions to the line chart so I can actually turn it off you can see that it's none here if you want to turn it back on again you can click on the filter it will start to filter if you want to turn it off you can actually turn it off from here none and once you do that you can see that the line chart starts to appear and it shows you all the values right up to loggers that's where we have the data until and this is actually cool and maybe I just go back to the format tab and close the Edit interactions button so I close that and now even if I actually click on April or May the chart is going to stay still but if the filter is coming from any other visual that means if I end up clicking on premium it is going to respect that filter so that means that this line chart is belonging to the entire year for the premium category for 2012 that's what it means now we're all can you use this technique so I believe that people who are just getting started with Dax you can actually use this to your benefit so I'll tell you how so you can see that I have two cards here right so one we have total sales and the one the other one is total sales filtered so this is actually showing you sales for the entire year and this is actually showing you sales which are actually respecting all the filters right so what I'm going to do is I'll actually click on the on the slicer then I go to the format tab and click on edit interactions and you can see that I have actually turned off the interactivity between this month slicer and this little card right here now what this means is that whenever the user is actually going to click on any of the months he will not only see the filtered sales of the month but he will also end up seeing the total sales of the entire year for 2012 for premium category as well it's still accepting filters from me if you actually want to turn off the filter from here as well you actually have to click on this chart go to the formatted interactions and then the interactions from here to here so that's off and this will actually show you the entire sales of 2012 now the only filter it's currently working on with is the ear filter all right so that's what you can end up doing any even if you now click on any other thing here this thing is gonna get filtered and this thing is not going to get filtered it's a quick way to kind of turn off filters from one to the other and that this works absolutely fine but just in case anybody ends up deleting this and maybe just placing it again one drawing it once again this the the interactivity that you turned off is going to be turned on again and you will have to do the work once again and that's why you know kind of huge tax to turn it off in the measure so that it doesn't really come up once you drag that measure into the visual alright so let me just turn off that and turn off that alright let me now I I think it's it's still five minutes so maybe I'll just quickly explain you the top analytics visual as to how did I create that it's actually a part of my Dax course it's it's actually also the part of my Dax course and I have created a very very exhaustive blog post and video series to exactly explain the top analytics visual right here so if you maybe just go to the blog maybe I'll just show to you real quick so just say read my blog and then if you just click here come on come on just go to the search tab on the top and right top product analysis top product analysis in PowerPivot that's what you write and you will get an entire blog post that I have dedicated to this thing and this is exactly doing the same thing that we I'm just going to discuss now there are also four videos you can actually watch the video step by step but here is what I'm just going to give you a quick explanation you can just take a look at that later as well so obviously there are three visuals here working so three actually measures here working so first of all I want to get the names of the products that's what I would like to get remember that the names of products is actually a text value it's not a numerical value so a sum account is not going to work what you need is a function that actually delivers you a text not a number hence we are going to work concatenate X so I'm just gonna come here and maybe show to you the calculation that I have here so top product sales I say that hey why don't you find three products from the products table in the order of total sales and once you find these three products you concatenate them now once you concatenate the products the products are going to be stuck to one another so one product and no space and no comma and then the other product and the other product so the the delimiter that I'd like to provide is an enter and hence I have written unique character ten unique character ten actually means enter right it's the ASCII code for that and then I say hey why don't you sort them in total sales the order is going to be descending and so on and so forth and then you can see that after I write the concatenate X I have also written a couple of other things at the bottom and these other things are nothing but the the total which I have actually generated here in the end so I'm actually generating three products here but I'm not concatenate them I am actually calculating total sales for that and hence I kind of show the show the thing here total sales value right here similarly I write another measure to find the dollar sales here it's again concatenate X so let me just show to you so top values that's the one yeah that's the one so I'm gonna say hey concatenate X X once again top three values I'd like to concatenate total sales press an enter after each concatenation the order is descending and then I kind of make a little bar chart this is amazing and let me show it to that so where is the bar chart right here again I say hey concatenate X concatenate three products and then I say that hey why don't you maybe take the total sales and find the percentage over total sales the total unfiltered sales which is right here so this is unfiltered sales and this is actually filtered sales which is coming to the row context and once you find the percentage you multiply that with fifty a percentage is a value which is actually less than one maybe 16% is 0.6 50% is 0.5 so I take that percentage value the contribution of every single sales and I multiply that 450 and that is the number of times that I repeat the bar the bar that gets repeated so you can take a look at this it's pretty interesting if you actually understand this this is just nothing but the formatting part of it nothing to complicate it alright okay one more one more tip before I kind of close and start asking kind of taking your questions I have personally found myself that I'd like to get a certain font color or maybe a box color into Bobby I and you know what if I actually end up recoloring this this box that I have made on the top I go to the visualizations I go to the background and if I kind of do the color right here if I can go to the custom color I can make colors but it will be better if I knew the hex code of it right and you can obviously know the hex code that's totally okay but you can also generate the hex code using PowerPoint right then we should show you that so let's just say that maybe I am going to go to my blog and you know what I like this this color and this color is what I like I'm just gonna come back in PowerPoint and just kind of paste add color now I'd like to know the hex code of this color what I can do is I can actually make a box or a triangle and I'd like to fill the triangle with this particular color this is as of now a picture right this is not really a box or something so what I'm gonna do is I'm gonna click on the box I'm gonna go to the Home tab and in so I I will click on the triangle then I'll right click here to see we have it here or not yes so I will right click on the triangle and then I'll click on the fill option right here and I have a tool called eyedropper once I click on the eyedropper I can take my eyedropper wherever and this is actually going to pick up the color that you're marking it on and it will fill it up with this color now as of now if you just go to the properties of this so it is that shape format and right here right here go to more colors so if you actually go to the properties of this you can see that oh it just doesn't show it here so you can see that it actually shows you the RGB combination here but in case your PowerPoint is updated is actually going to show you the hex code all so that has been the recent update in PowerPoint it actually shows up in my laptop but I'm not doing the webinar on my laptop so it doesn't show you here so actually now the hex code also comes if you just update your PowerPoint so you can actually copy the hex code from here from right here and then you can come back to power bi and you can just go to custom color and then just paste that hex code here once you paste the hex code here it's going to appear everywhere in your power bi so let's say even if you want to change this font color and you maybe just click here and you want to change something you can actually come back here and just go to the font color and then you can just start to see that color that you updated to add it into power bi so that's a pretty interesting way that I do it a lot I mean if I like any particular color somewhere I actually use an eyedropper tool to get the color get the hex code and then generate it if you still don't have the the hex code appearing into your power bi window you can actually just take the RGB numbers there and just put it on the web and it will give you the hex code for that which you can actually use it with power bi okay that was a lot of speaking I have to need some water and I actually invite questions if you have any so be more than happy to answer any questions please feel free just open up my chat window and ask actually my mother says good my mother is also here they don't work in power bi I don't know why are they here but it's good to have them all right folks any questions I'd be more than happy to answer any questions so we have kitchen here maybe if you change edit the introductions to the filter and not highlight title may also change Christian I'm not sure you can just test it out and let's just see if that works or not if it does work it's it's actually good great how to highlight top three with one color I'm not sure if you can do that I am really not sure actually the the thing that you write in the top three is just one measure and I cannot start to highlight parts of the text in different colors using conditional formatting so conditional formatting is going to be applicable to one part so I can entire I can highlight the entire block but not highlight the first product the second product in the third product so yeah is filter the same as slicer in a PivotTable yes the filter is the same as slicer in a PivotTable but cross filtering is different you can actually click on the chart and the other things actually get filtered so I don't know if that answers your question any adding you would suggest to learn dax measures so ok maybe I'll just give you some some thought about it so the first thing that you have to understand is that there is an there is a little thing called a new quick measure this is actually sort of a tool or like a helper built by Microsoft which actually starts to write measures for you right but this can be a little dangerous because if you don't understand what the measure is actually doing you would not be able to edit those measures but you can make a good start here see if that measures are working you'll also have to test your measures that this particular thing creates robustly across all your reports once this is fine you can actually take a look at the code and go read it and then hopefully you'd be able to start to create measures but you'd have to learn the fundamentals of how actually Dax works to be able to write good measures in Dax and you would not be able to write very sophisticated measures with the new quick measure tool that that is there in power bi already with you I suggest that you take a look at the course that I have which is a Dax course that I have it's currently closed for enrollments for now but it will open once again the link is down in the video at the bottom so you can just take a look at that maybe register for that just join the waiting list and once it opens up you can just take a look at that if it interests you please join that okay section says how did I create a chart view in the pivot table such in the file don't map don't forget guys the file is there underneath the video please go ahead and download that file and you can just actually take a look at my measure although there is a full series of videos that I have done on top analytics you can watch the videos one by one and you would be able to understand that what have I done how have I done it and also this this stuff is actually possible in Excel power pivot as well and that's the example that I have used although here I have used power bi instead is there a limitation of the rows of the data in power bi I don't know how many rows are you actually talking about I personally have worked up till I believe 40 odd million rows of data that's what I have worked on personally but if it kind of goes beyond like a lot of lot of rows maybe 100 million 200 million rows or even beyond that then you can use something like aggregations in power bi to aggregate or reports and this can go up into billions of rows after that and you can gauges can kind of get reports aggregated like pre calculate certain things based on aggregations and then show those things so maybe you can just google around that but I have seen people working in billions of rows in power bi so Litton says your tax knowledge is great how many scenarios have you covered in the tax course so I can start the DAX course in two actually three parts you know and I actually teach the DAX in a way that I personally learnt it myself and that was easy for me to understand because I was an Excel user I was never a programmer after had commerce in my life have said it finance substrate some marketing as well but have not said any bit of computers all that I was doing in my school was paintbrush now coming to the tax course I've actually divided the tax course into three parts part one is that you really need to understand the fundamentals of how things work when you start working with tax it's not like an f2 button in Excel you press the f2 button you see the formula open up and then you kind of rewrite the formula or tweak the formula to a plus/minus anywhere here and there the tax actually works on filter context and you really need to understand this concept really really well in order for you to design more complicated calculations that's what I cover first the barre fundamentals of tax once the fundamentals are covered I talked about different cases simple to complex cases that you can actually there are practical cases in business that you can actually use and actually deploy in your reports and those are obviously complicated cases but if your fundamentals are clear you would be able to understand those cases now these are two parts what you need as the third part is a continuation like a continuous expansion of your knowledge which is how you care it is by learning more and more tax functions so you kind of you know rack up more and more functions in your mind and kind of expand your own library of understanding more and more tax so there's a third part of the course which is where I cover a lot of these functions and how the core functionality of these functions work so that's how I kind of design the course and I do answer a lot of questions as well for the people I also have like office hours which is where I do these one-on-one sessions not one-on-one actually we gather around me kind of get all the people and we'll just to be sessions for all the people who join the course anything more than 10 million roses creates so yeah sure that's nothing actually you can that papier will be able to handle 10 million rows pretty easy that's not a problem sourabh just added 1.5 crores yesterday was a bit slow but it works fab Saurabh I will highly recommend that you go and check the the cardinality of your data model and it will work even faster what that means is you go find out columns in your data set which have high sort of uniqueness that means let's say your transaction ID so if you have 1.5 crore rows of data and if you have 1.5 crore transaction IDs that's a unique item every single row you don't want to have that unless you absolutely need that so you remove items with high cardinality and your model is going to be faster both in terms of performance and calculations as well let's just see somebody asked me a question on shortcuts shift-enter to go to the next line i've actually recorded a video on Dax shortcuts so you can just take a look at that there is not one there are a couple of shortcuts that I have discussed in that video just go through all of them they're pretty interesting and you will find them very helpful in case you were writing a lot of tacks actually alright um let's see if I have missed any question from the top all right um I'll make the way I indent in Dax is my using shift-enter not alt-enter so there are two shortcuts to come to the next row there is one as shift-enter there is another one as alt enter alt enter goes to the start of the row I mean start off the row Atta at the first position but if you press shift enter he actually comes to the same indentation as the previous indented value or the value left it actually all right Darrin asks can a slicer be used to change the values used in the visual for example using a slicer to change Darrin a great question I have actually written a very detailed blog post on this and this is also something that I have also covered in the course you can actually do that so maybe I can just show to you real quick maybe if you just want to kind of take a look right after the webinar ends so here if you just right in the search field on my blog change pivot table calculation table fail yeah that's the one pivot table field calculations with the slicer that's the post that I have written and that's what you're asking so based on the slicer I get to choose what is going to go in my pivot table and if you just go through this blog post I have mentioned everything right here these are the different tax codes that I have used and I have also explained how to do that in in power bi so you can just watch this video if in case you would like also download the part we have five in case you like that so that's a ready-made answer for you alright let's just see any more questions are there Kanishka I will highly recommend that you do all the ETL in power query till the time you limit your data I mean you don't start doing vlookups and doing calculations and percentages of total in power query till the time you can make the data clean in a transactional format which is where you can have a star schema maybe doing like one-to-many relationships between your tables that's the ETL that I will highly recommend to carry it out in power query and then just do all the calculations all the relationships and all of that work in index in power bi that ways your model is going to be clean and it's going to be working fast as well alright folks it's a little over 8:30 I'm still getting questions I'll have to stop here but but I've actually opened up one course on Excel it's kind of ironic all that we are talking about power bi and I've opened up a course on Excel I'll open up the DAX course if you're interested once again just go to the tax codes which is actually mentioned in the link and you join the joining list and so that I can give you a shout out when I open the course once again in about a month or so but for now I have actually opened up an Excel program and an excel dashboards program these are two flagship programs that I've been running for quite a long time and a lot of people have joined them and gained benefit from that so in case you would like to learn Excel in a very comprehensive manner please go ahead and take a look at that course in case you'd like to you know carry forward your knowledge from Excel and start building sophisticated dashboards in Excel using just Excel capabilities just go take a look at that there is also a coupon code mentioned at the bottom of the video which is I think off thirty or thirty or something like that just take a look at that I don't remember and if you just apply that on the checkout page you will instantly get a 30% off on that thing I'll probably make that course active for the next few days probably till the end of the week and if in case you want you can certainly join that courses can be very very helpful for you guys alright and last thing I certainly want more ideas on what what kind of webinars can we do more often do you want me to do more of Excel webinars or should I continue doing powered we have webinars just just give me a shout out in the comments and maybe I'll just plan the next webinar around the lines that you actually need so I can do Excel I can do Part B I can again do tax we have already done one Dax webinars you can just take a look at that as well I can even do power query so just let me know in the comments guys and I'll kind of organize something on those lines once again alright that's it thank you so much folks for coming along it's actually Saturday night in India it's about eight thirty eight in India and I actually go to bed pretty early so yeah thank you so much for coming it's a great pleasure actually to be talking to you guys interacting with you guys although we're quite far apart but yeah it's actually you know a good thing to interact and kind of share the knowledge feel free to let me know in the comments as to what webinars do you want me to do in the future and would they should they be on Excel tricks like simple to simple excel tricks to make your work faster and just give you more efficiency in Excel should I do more of visualization should I do more of Dax power query whatever it is let me know folks take care good night be safe stay at home it's pretty tough times outside and

Channel: Goodly

Views: 5,426

Rating: 4.9784946 out of 5

Keywords:

Id: rufDif4cWq8

Length: 73min 55sec (4435 seconds)

Published: Sat Jun 06 2020