 So hello everybody and welcome to the Debron Consulting Power BI and Excel webinars. So this is our Power BI and Excel clinic and you're welcome to another month's episode. This episode is going to talk about Excel. We have five tips for Excel. We're going to do a lot of Power BI in this episode but five tips for Power BI as well and then two awesome Power BI visuals and we're also going to have a competition and we're going to win some prizes. So I'd like us to just check out the tools to see if it's working. Can you type into the chat? You should see something in the chat right now. I'm just saying hello. All right, good everybody. So here we are. Again, a lot of you joined this webinar from our Meetup group and we have a Meetup group in Nigeria, Modern Excel and Power BI Meetup group. So if you go to meetup.com you can find us. I think we are about 1,200 members now or 1,100 members. So join the Meetup group. We do live webinars, live Meetups as well as these online webinars every month. And more importantly, join the Pog group. This is a new thing from Microsoft called Power BI User Group, Pog, where you can get free answers to all your questions on Power BI and Excel as well. So this is Nigeria Modern Excel and Power BI User Group. It's a Pog group so we have two groups. Please join those groups. So what are we doing today? Okay, this is just a bit about me. I use Excel, I've been using Excel for a very long time. I work at D Brown Consulting and the Principal Consultant there. I have a lot of experience using Excel, financial modeling and training as well. ATD trainer, master trainer and speaker at various events. So that's just quickly me. So this is what we're going to do today. We're going to do, we're going to have top five Power BI tips for the month. We're going to showcase two visuals, two Power BI visualizations, top five Excel tips for the month. Then we have the August update for Power BI desktop. For those that don't know, Power BI is a business intelligence tool from Microsoft that brings together various technologies that makes reporting and visualization very easy for analysts. So a lot of the technologies in Power BI are also in Excel. But many people don't know that these technologies are in Excel. So what Power BI does is just makes the visualization very, very easy and very intuitive and allows other people that know how to create these visualizations to create free visualizations for us, the users. So there are so many wonderful visuals in Power BI and it's quite popular and quite powerful. So what Microsoft does is they update this tool every single week. So every month they compile all the updates for the various weeks for that month and they do a Power BI desktop update. So I'll tell you the list of things they did for this month, August. Then there's a prize draw. Try and find a winner for the prize draw. And then we have an Excel challenge for you. So this Excel challenge is quite an interesting challenge. If you get the challenge and you actually solve it. Sorry about that. Someone had to mute somebody's line. So as I was saying, we have the prize. We have a challenge and anyone that wins that challenge, the first person to get that challenge and send us the answer will get a free course from D Brown Consultant, a free training, live course, online course, whatever you like. And then claim your template gift of the month. So we have a small gift for you at the end of the webinar. All right. So let's get started. So top five Power BI tips for the month. That's the first thing we're going to do. So top five Power BI tips. So tip number one. So tip number one for Power BI tips is improved metric visual with new conditional formatting features. So there's something called a metric visual in a matrix. A matrix actually. This is not a matrix. This is M-A-T-R-I-X matrix visual. So improved matrix visual for Power BI. Let's see how that works. Just give me a second. Let me quickly get that up. Okay. So that's number one. Improved matrix visual. So I'm going to put up Power BI for everyone and we're going to use that to do the improved metric visual. I'm going to turn on your screen now. So I have Power BI up. This is Power BI. Again, for those that are not too familiar with Power BI, as I said, it's a visualization business intelligence tool from Microsoft and it's free. So this is completely free. All you need to do is go to www.powerbi.com and then you can go to downloads and you download Power BI desktop, which is this tool. So for this tool to work, you need to bring in data and stuff. So we have other videos where we've shown you how to use Power BI. You could go to our YouTube channel. Just go to YouTube and type dbrown consulting and you'll get our YouTube channel. So there's this new metric visual or this metric visual called a matrix visual. So this matrix visual in Power BI, if you look to the right, I'm pointing at it right now. If I click this visual, it's like a table visual. So this is kind of similar to a pivot table in Excel. It's basically the same thing. So let me just quickly create a report. Let me see, create store, let me put region. Sorry, I clicked on the visual first. Click on the visual, take region and then let's take under data, let's take revenue sum. Revenue sum. Okay, let me increase the font size. Okay, so values, it can increase value size. There we go. And row headers, let's increase that. And column headers. So I'm just increasing the default sizes. They're quite small. So and then the grid itself. Okay, yeah, that's fine. So as I'm seeing, you can see it to the right. I'm just clicking on the format in tab and I'm modifying the format so that we could see the visual better. So those are things that's quite flexible. You could do nearly anything to the visual to make it look better and smarter. So the region, okay, so let's just see that. So this is our visual. And if I increase, let me put another category there. Let me put states as well. So I have both a region and a state. So let's check that out in a few seconds. Let's see. Text size. Okay, text size is fine. All right. So if you look at this visual, the new thing they did is they included conditional formatting. Now in Excel, we can do conditional formatting here, but they've improved the conditional formatting of these numbers. So I could decide that I want to text and to kind of color them based on their numbers, highest green and lowest red. So I come to the formatting tab here to the right. I go to conditional formatting. Then I can decide, okay, conditional formatting, what are we doing here? We're saying the font should be on. The conditional formatting on the font should be on. I switch it on. I switch it on and off. Now you can see the effect. Or I could decide to switch on the background formatting. This is the one that was there before, the default background formatting. So it's just filling the cell with red, green, depending on what values are there. Or you could just use the font formatting. So the font formatting, and if that's not okay, you could go to advanced controls. And advanced controls brings a box just like Excel, and you could decide to click on something like diverge. So we have three colors. You can decide the minimum is red. The center color should be yellow. That's quite popular for people yellow and then green. Although I don't really like that kind of visual. But that's really how it's done. Using too much color. Using too much color is not really a good idea. But that's how that visual works. So you could play around with this new conditional formatting features. That's how it works. So it's pretty cool. So that's the matrix visual. So tip number two. Tip number two is using tool tips to show more context in a report. Now this is quite a useful trick or useful tip. Tool tips. What do I mean by tool tips? Let me go to another visual. Let's go to this bar graph visual here. So if I go to this bar graph visual. All right. So last month we showed all the standard visualizations in Power BI and how they work. You can go online to the YouTube channel and check out that video. So if you look at this stack bar chart. We have north central, north east, north west. So if I maximize that for you, you can have a look. So you can look at this visual. If I hover over a bar, I'll see something there. I see north east and then the value for revenue sum. So this is the revenue sum for north east. Now what I always say when it comes to visualizations is always very good to show comparison. So wouldn't it be nice if I hover over this, I can actually see the value for revenue, which I can see, but I can also see the value for revenue for same period last month. I don't want the bar for same period last month here. I just want the value for how much it was same period last month. So for us to do that, I go back to my report. What it does is click on the visual. You go to the visualization fields tab here and there's a section here called tool tips. Hope you can see it. It's just around here called tool tips. So for tool tips, you just drop what you want to see when someone hovers over a bar. I want to see same period last year. So I go and look for my DAX. I should have written the DAX already for same period last year. And I just drag same period last year and drop it inside tool tip. So once I drop it in tool tip, it hasn't changed the graph, but when I hover, it now shows same period last year figure. So let me maximize that for you. So if I hover now, I can see revenue sum and I can see revenue sum same period last year. I can do percentage change. I can do percentage of a budget. I can just add lots of data there. So anyone that hovers over any bar can see the extra data. So that's tool tip. That's the use of tool tip. So for example, I could put year to date. If there's any year pick there, I can stick year to date and drop it in there. So when I come to tool tip, I can also see year to date. So that's how tool tip works. Very, very useful tip. Let's go to the next tip. Tip number three for Power BI is simple DAX measure for calculating year to date. Okay, so we're going to write a simple DAX measure for calculating year to date. All right, let's get some data. Let me just go to blank, maybe August here. Let's come to this blank sheet here and let's create data. Let's say I'm going to pick, I go to my calendar. I pick year. Let me drop year here. Let's take month. For example, this month I take month. Then I take year. Okay, I need the year to drop on the month. Let's say year first, not values. Let's use the metric visual instead. So I drop my year after month or before month. So this is my year month. Let's increase the size of this. This is grid size, rows. Not the rows really. The values themselves, the text size. Yeah, so I've increased the text size. Let's drop revenue. So I come and pick revenue figure, revenue sum. All right, so you can see my data here. Let's just increase the size again. So see my data. I have 2014 revenue on 2015. If I come here on this visual and expand it. If I expand this visual a bit, you can see 2014 January, February, and the like. So I would like to see if I can see January, February, March. I want to see year to date by the side. So year to date by the side. I would like to see year to date by the side, which means January will be the same thing. February will be summation of this too. Now to do that in DAX, you need to write a measure. So you need to come here and say measure. Now I already have the measure here, but I can just write it quickly. So I do a new measure, and my new measure is going to be rev year to date, YTD. So rev YTD, and I'm basically going to say, okay, there's a function for YTD. So calculating year to date. Now in Excel, obviously we don't have this function, but in Power BI we do. So total YTD is the name of the function. So total YTD, TOTA, total YTD. So we have total MTD, which is total month to date. Total what to date, and total year to date. So I just say total YTD. It says, what are we doing total YTD for? The expression is revenue sum. I want my revenue sum. And then it asks for date. Where am I getting my dates from? I'm getting my dates from a calendar. Now for those that don't know DAX, the next tip is going to be very good for you. If you don't know DAX, the next tip, just wait for the next tip. You could do all sorts without even knowing DAX. So DAX is the language for Power BI. Data analysis expressions. That's what I'm typing. I'm using DAX. It's a little bit like Excel, but it's also different. So D calendar dates. So I'm saying give me the total year to date for revenue sum using the date in a table called D calendar. That's basically what I'm saying in this formula. So I've done that. I can come to my format. I want to click on the format. I can just enter actually. I can just enter. And this is my measure called revenue year to date. If I select that measure, you will now see to the right that it has revenue year to date. And you look at it, you have January, February. January is the same as January. February is year to date. Summation of February and January. Then once you get to December, it's submission of everything, which is 48, 464. And then if you look at the total, the totals are at the top. Now, one thing they also did in the matrix visual is you could have your total, your grand total at the top or at the bottom. Some people prefer their grand total at the bottom. So what you do is you click on the visual. You go to your formatting pane. You go to subtotal. And then you say your total shouldn't be at the top. Your subtotal should be at the bottom. So you can see 2014. The total has now come down to the bottom. So we now have 48. So this total, this is your year to date. The new month of the new year starts again, another year to date section. So that's how you use year to date using a measure. Just total year to date is the name of the function. That's quick tip number four. So quick tip number four, that was number three, I think. Quick tip number four is let Power BI automate your DAX with quick measures. So I just wrote a DAX measure. It's called a year to date. But Power BI has the ability to automate that for you. You don't need to write DAX or learn DAX. Power BI can write that for you. So we're going to do that now. So how many of us here, how long have we used Excel? So I'm just going to launch a poll. If I continue, I just want to know of the audience here, how long have you used Excel? The funny thing is, your statistics here shows 36% of us here have just started using Excel, 0 to 2 years. And also 36% have used Excel for more than 10 years. So we have very experienced and just some newbies in Excel. So for the newbies, don't worry. I mean, Excel has changed so much that all of us that have been using Excel for like 20 years are still learning. So you're not in any bad position at all. In fact, you're in an excellent position. You're learning a software that has matured significantly. The old Lutus123 and the old 20 Excel 2003 style of using Excel. So isn't it straight from using their best tool possible? Okay, so that's cool. Let me close the poll. Okay, so I've closed the poll. So that was interesting. A lot of us here have just started using Excel and a lot of us also are quite experienced. Yeah. Okay, so this poll is, let's Power BI automate your DAX. If you remember, I just did a DAX formula. I just did a DAX formula here. So we can, let me write another DAX. Let's just try and create another DAX formula. But this time we're going to use Power BI to calculate, do the calculations itself. For you to use Power BI to do a calculation, you first of all have to understand what it is you're trying to do. So for example, let's say I want to do, let me create a report of total revenue by state. And then I want to see how they compare to the state with the highest revenue. So I'm going to create a small table here. Just a table. So I click table. I clicked on this table visual. I just clicked this table visual. Then I'm going to get the list of states. Let me go to D store. I know this is where I have my states. So I've listed out some states in Nigeria. Yeah, so these are my states. I just selected states here. Then let's see. I want to do some calculations based on states. So if I come to states here, if you look at where I am to the right, there is a tiny, tiny buttons on the right. If I click those buttons, you will see a list of things I can do with states. Now one of the things is new measure. New measure is you writing the measure. A measure is the calculation, DAX calculation. That means you are writing it yourself. But I really don't want to write it myself. If you go to quick measure, quick measure is Power BI that is writing it for you. So when I click on quick measure, it brings up this dialog box. So this dialog box is like your DAX automation wizard, so to say. So when I click on selected calculation, all these list of calculations on the left are automated DAX calculations. In fact, you can use quick measures to learn DAX. It's very cool to learn DAX quick measures. So for example, I want to just do the average revenue per category. So I do average per category, whatever average per category. So I'm saying average what? What am I doing average of? So average, and I'll go to where my data is. This is my data tab. And I'm just going to say, okay, I want the average of revenue. So revenue sum average. Or I'll just say revenue column. The column is hidden. I think it's hidden in the column. So I just say revenue. Or revenue here. I just select revenue and drop revenue sum. So under the average of revenue sum by state, I say okay. And once I say okay, I have to get back to the tool. Once I say okay, it calculates the revenue sum average per state. If I tick that measure, you'll see the calculations have come into my spreadsheet. So those are the calculations. Let me make this a bit bigger. Let's get the grid. Tech size should be bigger. Okay. There we go. Right. So that's my average. Average revenue. Let me make this a metric. Because metrics are visual. It's easier to manage. I mean, yeah, reduce that. Okay. So that's my average revenue. What about some other calculation? Let's just check. I think we can do some other calculations. The other way to find your calculation or your quick measures is once you do a calculation, you can come to the fields. And then under the fields, we've just done this calculation. I can click the drop down and you can now say new quick measure. Just click the drop down of one of the calculations you've done and just say new quick measure. Let's quickly do another one. Let's see which one can we do. They have some time intelligence measures. You have your running to tell measure to tell for category filter that applied. So to tell for category filter applied, I could say to tell for a particular category that I have, revenue sum, and then I apply a filter. So I could just say to tell for, let's say region, to tell for region. And I'll use revenue sum instead of this. I'm going to use a revenue sum. So I'm saying give me the total regional revenue sum. Let's just see what that does. Again, you just need to play around with this to see what happens. Let me delete this one to give us space. Let's come here. Bring this out. Let's see what it brought out. Oh well, calculation actually brought out the same values. That's strange. Not this. Just revenue sum. I should have just done revenue sum. But you get the picture as when we basically can do all sorts of funny interesting calculations with this. And again, you can use it to modify. Let me undo and bring that other calculation back. Let me bring this table back that I deleted. Hopefully it comes back. Let's see. Yeah, okay. So this table that I did yet to date, you could decide I don't know how to do yet to date formula, for example. I want to see how the computer, how probably I can do yet to date calculations for me. So I could come here and say new measure. You know this is the calculation wizard. And I could say I want to do a yet to date of total. Yet to date total. So I want to do yet to date total. And I'm going to base my yet to date on revenue. Revenue sum. So I want revenue sum yet to date. But then there's a date field here. And there's one small flaw in this quick measure. The date field uses an internal date field for Power BI. But most times, if you do our courses, for example, you see that you need to use, have a calendar. You need to bring a date from a calendar. But if I bring a date from a calendar, this will not work. But don't worry, just still do that. Because this will not work. But you would see the formula inside this inside here. You see the actual formula I tried to use to calculate total yet to date. So by doing that, you can see this time intelligence quick measures can only be grouped or filtered by Power BI provided dates. Now they're changing this in the next couple of months. Power BI, Microsoft team are going to correct this. That means it needs to use its own dates. So the end of the day is you can use this to learn DAX because look at the measure we created. If you look at it, this is the measure we created. So since you see the measure we created, you can easily modify your formula. Just delete all of these stories here. And then now that you know this is the measure that works, you can just modify it a bit. So you can let the quick measures create the DAX for you and then just modify it. And once you modify it and enter, your calculation should populate like this. So it populates fine. So quick measures will help you learn DAX. So try and explore quick measures to help you learn DAX. It's very powerful, help you learn DAX. All right, number five, the last step for Power BI. So our last step for Power BI, before we go to the tips for Excel, is create scenario analysis with the what if parameter. This is a completely brand new tool that they just brought into August. I think it's just August or was it July? The thing is that in the latest August Power BI that they brought it in. So using the what if parameter. Now what is that? Let's see what that means. Okay, I'll still use this. Let me just remove some calculations we don't need. I don't need here to date. Let me just leave revenue. I don't need this second year to date. Okay, so if you look at this revenue, what if my revenue went down by 2%? What would have happened to revenue? What if my revenue went down by 2% or went up by 5%? It'd be nice to do those calculations. So what we used to do in the past is we do something called a calculated or what we call a disconnected slicer. So you do a disconnected table and then you create a disconnected slicer. Strange stuff like that. But anyway, how do we kind of put revenue scenario here? Revenue is less by 5%, less by 4%. So the first thing you should do is just click out here and we come to modeling because this is now analytics we're doing. If you go to modeling, there's a new section called what if. This is completely brand new. So if you have an old Power BI desktop, go download the new one. This is really, really cool. So new parameter. What does that mean? So I want to create a new parameter where I'm going to take revenue. I'm going to do different scenarios of revenue. So let's click on new parameter. A new parameter brings this up. A what if parameter. So let me call this revenue scenarios. So I'm calling this revenue scenarios. I use whole number. Let's say I want a scenario from minus 10, which I'm going to use as minus 10% later to 20. So minimum minus 10, maximum 20. Then increments of one. That means 1% increments. Add a slicer to this page. It means it's going to add a slicer to also slice this information. So let's click OK. So what happens here is this. It's what is done is generated this slicer. If I drag this slicer to the right, for example, you see minus seven in the box. So that's one thing it did. It created this slicer. But more importantly, it has modified my data model. It has actually created something called revenue scenario. If you look to the right, I now have a new table called revenue scenario. Let me show you that table on the left. If I click data, you would see that there is a new table in this my data model called revenue scenario. So look at what it did. It just listed out the minus 10 I had put for my scenario to 20. So minus 10 to 20. So these are all the values generated it. So this is a table. And you know, the table, I can even create a new measure, a new column, for example. I can call this column maybe a scenario percentage or something. So I call this scenario percentage where I'll just say it's equal to revenue scenario. Revenue scenario. So revenue scenario divided by 100. So I can make it a percentage. So if I do that, I can also just click on the percentage sign, click on this, click there, and then click on the percentage. Click inside there and click on the percentage sign itself. Just to make it a percentage format. Okay, let's do that. Okay. So we have basically created another column. So I just click there. Created another column, which I can use in the report. Now if you look at your relationship diagram, you will see that this is a disconnected table. Now we used to do this manually before. So all of this is now automatic with the what if, the new what if parameter. So this is disconnected from my data model. But I now have this and I can show you how we will now use this in a report. So if I come in here, come to the chart. I could decide, okay, do you know what, for this field, I don't want to draw revenue scenario. I want a percentage. Scenario percentage instead. So here I have the percentages. So it's showing 700% for some funny reason. That's 10% good. So you can see the percentages there. Maybe I don't want the decimals. I can easily go back to my data model, highlight it, and remove my decimals. I don't want decimals. No decimals. There we go. Come back to my report. There are no decimals. Right. Excellent. So when I click on, when I put minus 4%, I want my revenue to reduce by 4%. So we're now going to create another measure here. So I just come here and say new measure. And my new measure is going to be revenue scenario is equal to, I'll just say it's equal to revenue. So the revenue sum multiplied by, open my bracket 1 plus my scenario, whatever scenario I have picked, which is a scenario, revenue scenario value. Revenue scenario, revenue scenario value. All right. Revenue scenario value. Then obviously I have to divide this by 100. Okay. So let's see how that works. So we have our revenue scenario now. And when I put that there, I could see that this is my revenue scenario. I have, this figure is going to reduce by, so if it's minus 4%, that's this minus 4%. So obviously this is the reduction. Or I could just add revenue to that. So I can actually see the actual total revenue after the deduction. So add revenue sum to that. Let's see. Let's see what maths, something's up with my maths. 1 plus this, blah, blah, blah. Okay. I have to put a close bracket there. Yep. Close bracket. Oops. What happened there? Let's look at my mathematics. Revenue sum times 1 plus revenue scenario. Okay. 1 plus revenue scenario and then divide it. This is actually supposed to be divided by, okay. Uh-huh. Now this should work. So of course you need to get your, almost your board maths and your mathematics correct before you finish your formula. Let's take revenue scenario now. And this is the correct formula. Then you can obviously put your currency symbol. Just put my nearest symbol so it looks the same. So this is revenue when we, our revenue drops by 4%. I can now drag this. See the nice thing, I can drag this to 1%. You can see this is now when revenue is up by 1%. If I put it at zero, obviously it's going to be the same. Yeah. So you can now use this, this drag, just drag this and you can modify what your revenue looks like when you have this scenario. And of course I could put this in the chart, for example. You just do this in a chart. Oops. Sorry about that. Let's create a chart. Click on this and let's create a chart for this. Click your line chart. And you can see your scenario, this is your scenario when revenue is up by 4%. This is your scenario when revenue is what it is. And as you change your slicer, your chart updates. So you're doing scenario analysis. And there's so much you can do with this really, with just a tip up there of what you can do. Right. Okay, cool. So someone just asked, for a year end that is not January to December, this is Biola Sani. For a year end that's not January to December, how do you do year to date? So that's an excellent question. Let me show you. It's very easy actually. So if you, let me go back to a metric. So if you look at my, if you look at this, I'm sorry I'm going to undo. So if you look at this and I come back to my metric visual, let me bring in year to date. So if I come in here and I bring in year to date value, let's go to data. I bring in a revenue year to date. Now this is my year to date value. The formula for year to date actually allows you to kind of tell it what, not this one, the formula for year to date tells you what year end. You can decide and tell it what year end. So let's assume my year end is March. So if my year end is March, in an American dating system, I can just say for example three, I'm guessing that it's slash, I'm going to use here. I put it in double quotes. Three slash one slash, let's say 2014. The key thing is just needs to know what month year end it is. So my year end is March. Just put in there that my year end is March. So once I enter, let's have a look. If your year end is March, so let's say September, let me forward, let me sort by date here. Let me sort to this date. Good. So I've sorted this date. So if you look at, I have January, February, March, then let's see your year to date, revenue sum year to date, 16, okay, let's change this. So April is your four. So April is where it should start as your year to date. Your year to date starts from March. No, it starts from April, actually. April. So April should be where it stops. So let's change that again. Let's see if it knows this as one, three. Instead of three, one. Let's enter. Okay, let's see if that kind of corrected it. Three, eight, four. Yeah. So year end is March, three, eight, four. You can see it's starting from March right now. Revenue sum year to date. Revenue sum. Revenue sum. Let me remove the scenario. It's kind of confusing. By the end of the day, your formula allows you to choose your year to date. If you look at it at the top here, it says what's your date and then what is your year end date. So dates year to date. What is your year end date? So once you pick my year end date, April is March, or my year end date is June, then it will correct all the year to date for your year end dates. So my year end date is 31st of March. Let's see. That's it is September, April, June, and November. So 31 slash three slash 24, 14, for example. You put that in double quotes. So that should now correct your year to date. So I have January, February, and March. Let me sort this by date, by month. So I have January, February, March, and then April is when it starts again. So if I remove scenarios, we see that. Let me just remove the scenario. So if you look at this visual here, let me increase the grid size. So look at this visual. So I have January, February, March. That's the old dates. Then you have April. April is when your year to date really starts. April, so this is the previous year because I didn't have any values for that previous year. So April, your year to date starts in April, May, June, July, August, September, and that's how it flows. So it's growing starting from April. That's your year to date. And you can also specify that your year to date also affects the month, the day, and everything, and that works fine. So ask your questions and we'll continue there. But let me quickly get back to the plan. We've done the visuals for the year. We've done the visuals for Power BI. So custom visualizations for the month. So I have two custom visualizations that I'd like you guys to go check out. Very new custom visuals, and they're very powerful custom visualizations. So the first one is something called the monthly calendar visual. Monthly calendar visual. So it's almost like you're doing project management in Power BI. Although there's other very cool tools for that as well. So let's see how that looks. So monthly calendar visual. Let me come to a new page. I'm just inserting a new page here. So I've inserted a new page. Now when you want to get custom visualizations, you come to your home tab and then you go to, there's a section here for inserting custom visuals. Custom visuals. You go and insert either from the store or if you've already downloaded the custom visualization, you say from file. So if I click on from store, it's going to kind of navigate to Microsoft's, it's going to navigate to Microsoft Store online. And there are many, many custom visualizations there. I think there are almost a hundred. These are custom visuals, are visuals that are designed by Microsoft themselves and other people that can be used free of charge. Okay, there's an unexpected error. Okay, I've seen my internet access. And I think I've opened the calendar visual already. So you go to from store and then navigate to online store and then you can download any custom visual you like. So what, or you could just download it and say from file. So let's try this again. If it doesn't work, I'll just download from file. Okay, it didn't work. So this just not usually come on this way. So from file, I can decide to navigate wherever it is I've saved my custom visualizations. I think I have some somewhere here. I think there was a custom visual resources, custom visualization. I have quite a number of custom visuals here. Let me see if I dropped the calendar visual. Okay, I don't think I did. So once you download the visualization and you drop it, you can now open it, but I have it opened already. So let me just open that custom visuals for you to see and see what it does. So here we have the custom visualization called the calendar visual. So this is the calendar visual. So I come on, I like this calendar visual and the calendar visual will now ask you to drop certain things. What value are we analyzing? What's the date field? So my date field is my date field here. No data for selected year or month. So let's go and see what data am I looking at. Let's see what data do we have here. Let's go to the data or fact file to get some data. So I'll just say values. So I'm dropping values here. So I just dropped values. And what has happened is it has created a calendar. So if you look at it, it's created a calendar and this is the calendar visual and there are values in there. Although they're not looking too sharp, let's increase the size. I can maybe improve the format of my visual. Let's just increase the size of the data. Increase the size. So good. You can see the values now clearly. The alignment, I could change the font for the values if I like red, but I think the values are fine black. So these are values. This is like your revenue for these months. Now you can use, you can now shade the data. You can shade the data based on what's in there. So for example, my minimum value I could decide is going to be red. Red shading, my center value is going to, as we said, yellow. Now I wouldn't recommend these colors that I'm choosing. It's just that a lot of people like to do red, yellow, green. So my yellow is my middle value. So I'm diverged. I have to switch on diverged so I can be able to see yellow. So yeah, you could have this, but I think it's better you subtle colors maybe. Maybe a light shade of blue followed by a darker shade of blue than a far darker shade of blue. Something like that. Let's say, I don't even like this blue. I prefer another blue. Let's make it darker than this, maybe green then. So yeah, so you have these shadings. Now this is January. I can increase the font size. So you have to modify the title. Maybe you make the title a little bit bigger like this. Right? We don't really need the title. I don't think it's at value. Let's change the, let's put January itself. The name of the month should be kind of a better size label. So you can change the background as well. The lookup aspect, the borders, general formatting, the calendar format itself, we can increase the thickness, border thickness. That's border around it. We could, for example, font weight. Let me just make that 300. Make my font weight 300 and then the text size should increase. Okay, that's much better. Now you could see that this is January 1st, second thought. Now you can now bring a slicer. So if you don't want to just see only January, for example, I can come here and bring a slicer. For slicer, I'm going to create another visual, which I like, which is the tree map. So I click the tree map, and I'm going to put percentages inside the tree map. And the percentages, I'm going to break it down under my days to the right. I'm going to break it down by month. So let's just say year month. I have a year month visual here. Year month number. Year month short. Let me use year month short. So now it looks kind of strange, right? So this is year month. I can pick whatever year month is, and my calendar should change to that. So if I click on this, click on this one. Year month is 2016. Or let's create a slicer instead. It's kind of easier. Good. This is slicer. So if I create a slicer, I can slice my data by, I'm slicing it by May. This is May 2016. So this is what happened in May 2016. This is what happened in June 2016. Now this visual only shows one month at a time, the calendar visual. But you can just pick whatever month you want here. So this is quite a cool visual. If you like that visual, just click on the, on the hand sign, do you like this visual? On your chat or the, on the tool you have, just click on the hand sign to show that you like this visual. Do people like this? Does it make sense, this visual? Okay. Some people are putting their hands up. Okay. So it's quite, it just depends on your imagination really. You can improve, you can decide on what you want to use this best for. I mean, could someone tell me what they would use this visual for? Could you type it in the chat? So the question box, what would you use this kind of visual for? Yeah. Can we type? What do you think you would use it for? So earlier typing, I'm going to go jump straight to the next visual. So this is a nice visual. The next visual that probably I launched recently, a custom visual, which is really good. Let me just go to the slide so you can see what I'm talking about. Let's quickly get to the slide. Okay. So visual number two, we had just the calendar visual there. Visual number two is called the new Power BI KPI visual. The new Power KPI visual. So this Power KPI visual is from Microsoft. Microsoft are the ones that created it. And let's see if this works. Hopefully it works this time from store. Okay. It wants me to log in to okay. Sign in. Okay. Anyways, it wants me to log in. When you want to create a custom visual, either from store, under custom visuals, or from file. So if you go from file, that means you should log on to the store, Microsoft store. Let me see if I have the Microsoft store up so that I can just show you what I'm talking about. Let's see store, Power BI store. Okay. So the store, I have the store visual with me. So let's check out the store, office store. So if you go to the office store, if you look at the office store, you just go to Google and type store.office.com or just store.office.com you get to the office store. So the office store has all the custom visualizations for Power BI and also quite a lot of add-ins on apps for Excel. So we're thinking of doing a webinar on apps. So we just do a webinar on apps for Power BI, but probably apps for just Excel. We'll be amazed at the things you can add to Excel. But here we have Power BI. So you can see Excel, OneNote, Outlook. If I click on Power, it's not PowerPoint, Power BI. So when I click on Power BI, these are the apps for Power BI. So these are the apps for Power BI. You have your infographics apps. They're about a hundred different custom visuals. So you see this Power KPI visual. So this is the one that I was referring to. If I click on this Power KPI visual, you can click on Learn More. Then you'd see you can actually watch a video on how to use it. So you just play the video and it kind of goes through and gives you a very detailed explanation of how to use it. Yeah. So it's an excellent visual and let me just show you the visual. So what you do is once you come to this page, you can click on Add to Add It. Once you click on Add to Add the visual, now remember that when I was in Power BI, it says we could download from the store or we could pick from the store or download it ourselves. So when you click on Add, then you can now download the custom visual to your desktop. And once you download it, then you can use it. And you can also download a sample. So you can download the sample report as well. So it's really, really powerful. So let's quickly look at what it does. So we move on. So I think I downloaded it. I hope. Let's see. Here we have our custom visual. Yes, I think I did. So here we go. We have our custom visual. So this is it. This is the Power BI. This is the Power KPI visual. Power KPI visual. Pretty powerful visual. It has a sample report. And you can download this free. So you basically have KPI. There are quite a lot of things happening here. You have your line chart. You can see my tooltip showing different units. So I'm plotting my targets and I can see that, okay, I'm above target by 8% for example. Year on year I am up 14%. Units that I've sold is 895,865. My date is February 7th. You can change the format for that. So then you have all these lines here that shows your units against your target, against your prior year, against your variance, all in one chart. This is very, very useful. You could reduce the size of the chart and it looks like this. A small KPI top and you could use these small ones to create a very nice dashboard. Once you download this custom visual and the sample from the store, you will get tips and tricks. These are tips and tricks of how you change different things in the visual. How you include many KPI indicators into your visual. So this is a very powerful visual. It could take you a whole day to kind of get used to how you use it. So I recommend you download this. It's very powerful. So try and download it and use it and see how it works. So that's the new custom visual. Let's quickly get into Excel. We are fast as usual running if you run out of time. So amazing how time flies. But my first quiz is create a live connection between Excel and PowerPoint. So let's quickly do that and then get to our prices for the year. How do you create a connection between Excel and PowerPoint? So I'll have an Excel file open. Let me just quickly do that. How do you have an Excel file open? Let's see how that works. I have an Excel file open with you guys here. And I want to create a connection between Excel and PowerPoint. So if I have data here and let's assume I want to connect a section of this data. So I can highlight this data and what you need to do is basically use one tool called the camera tool. You take a picture of this data and then you take it to PowerPoint. So that's how you connect to PowerPoint. Take a picture of this and take it to PowerPoint. Once you take a picture and you copy what it is you want to take to PowerPoint, then you go to PowerPoint, you open up PowerPoint. So let me open PowerPoint. Let's get a new PowerPoint open. I have PowerPoint by the side here. So this is PowerPoint by the side. I come to PowerPoint and instead of just dropping it into PowerPoint, I've just copied this. I come to this dropdown for paste and I say paste special. When I say paste special, I can now say paste link. This is the trick, paste link and then select this Microsoft Excel workbook object and say OK. So once it paste a link, right now in my PowerPoint, I have that image. You can see it here, right? That's my PowerPoint. You just delete all this. So this is my PowerPoint. Now this image is live. When I change something here, so let me just minimize this so you can see both. So if I come here for example and I change this to David, right? If you look at your PowerPoint to the right, you see it has changed already to David. So this is live connection between Excel and PowerPoint. So if you copy paste special, paste link, you can create anything you do in Excel just updating PowerPoint. So that's the trick. Now unfortunately, we don't have much time for the Excel tips. So what I promise to do for you guys is I'm going to record these online and then I will give you the link for it online because all I want to do is go to the prize. I want to see who's going to win our prize for the month. So these are the tips I'm going to give you online. I'm going to record it and then I'll put it on the YouTube channel and we'll send you the links for this. So easy way to understand offset function. That was the next tip. Offset function is a very powerful function but we have a very easy way we're going to teach you how to use it. Then the next tip number three is going to use the offset function to create a dynamic chart. So an automated dynamic chart in Excel we're going to use the offset function for that. That's tip three which we'll do. Then tip four is using slices in a pivot table to create a simple dashboard. So if you remember the Power BI and we use the Slicer and Power BI, a disconnected slicer called a Wattif parameter. Here we're going to use the Slicer and Excel pivot table to create a simple dashboard. Then the last tip was going to be using slices directly in Excel. So not in private pivot table directly in Excel. Can we use a slicer directly in Excel? So we'll do all that but here is your Power BI desktop update for the month of August. So Power BI Microsoft just launched the August Power BI desktop update so you could download August Power BI desktop which came in last week I think or early this week. So if you come to go and download it, these are the new things you'll get. These are the new features that they brought in. I showed you a few things about the matrix visual but these are all the other new stuff they brought. Okay, you could see the Wattif parameter that I just showed you that is under analytics and modeling. New scatter chat analytics feature they've made the scatter chat faster. If you remember, yes last month I showed you scatter chat and how you could play the scatter chat. They've made it kind of quicker and easier to use. They've added a new quick measure, we talked about a quick measure called weighted average and they've done all sorts of stuff. So you could go to the YouTube channel and watch all these updates and download the latest for August. So challenge for the month who is ready for this challenge? This challenge if you do win this challenge you are entitled to register for any of our 2-day courses and that's worth 160,000 Naira. So this is a 160,000 Naira challenge. Who is ready? Okay, so I'm asking who is ready? I want to see anybody's hands that are up here ready for this challenge. Okay, so the challenge is this. I want you to build, I'm going to show you a template. A template that is what we call an Excel Function Translator. So I want you to build an Excel Function Translator. So but we're going to show you the Excel Function Translator. We'll just hide some things and you need to build it. If you're able to build what I'm going to show you now, you're going to win a training from us. It's worth almost 200,000 Naira. So Excel Function Translator. What do I mean? Now Excel is not just in English. I mean, there's Excel in French, in Italian, in German, in Dutch, in Croatian. I mean, there are different languages that Excel is in. So usually what happens is English is the most popular obviously. So when someone tells I tell a German guy, hey do a VLOOKUP. He's like V, what is VLOOKUP? He needs to understand VLOOKUP in German. So you can imagine if we had a Yoruba Excel for example, or a house Excel. Can someone tell me what would VLOOKUP be called in Yoruba? VLOOKUP means vertical lookup. Can you translate it to Yoruba? Anybody? My Yoruba is not very good. So type it and let me try and pronounce it. Somebody should type in the chat or a question. Or you can put up your mic and let me try and pronounce it VLOOKUP. Let me see. Or Lu or somebody wrote or Lu. Okay, that's interesting. If you look up in Hausa I don't know. Anyway, so that's basically what we're talking about. So now let me show you the challenge. So let me show you the Excel Function Calculator. So this is the challenge for you guys. Challenge is build this. So build this and win yourself a training. Now look at this. Function Calculator. This is all Excel. There's no VBA. No VBA. You shouldn't use macros. That's very key. You're using pure Excel. So if I select, for example English. I select English. Here it says function name in English. I have to change this. So let's say select everybody knows VLOOKUP. So you can see that I'm scrolling. VLOOKUP and VLOOKUP Okay, so if we look up. So English, a function called VLOOKUP is obviously going to be called VLOOKUP in English, but I don't want it in English. I want, what is it called in Danish? So I think Danish I see is Lopslag. Very strange. So this is the description in Danish. Danish Lopslag. What I really want you to do, you don't need to make it as beautiful as this, but you should be able to speak any language here. For example, I can take Brazilian. Let's say it's a Brazilian guy. A Brazilian guy is trying to understand what this function is. So these are all the functions in Brazilian. Very strange, right? I have no idea what these are. Let's take this, for example. Shama. So Shama in Brazilian in Danish is called Kald. Shama in Brazilian. But Shama in Brazilian in English it's called call. Okay. Does that make sense? So what I've done for you guys is you see the data. This is the data you used to build this. So you're going to use this data and go and try and build this whole solution. Try and build this tool. So we're giving you this tool free of charge. It's an excellent tool and this is the data for you to try and build this, right? Of course, we kind of locked it somehow. So you can't really break this, but try and build this yourself and then you win the price. Email it to trainingatdbrownconsulting.net trainingatdbrownconsulting.net So that's your challenge. Now all of you should look at the download. There's a download for you there already. If you look at your materials you see something called handouts. The handouts that is the, this spreadsheet is in the handouts. So go download it. Alright, so go download it. Now what I want to do now is I want someone to win the training. Someone's going to win an online training right now. So apart from this, you're going to win one of our online courses. So we have an online course on officetraininghub.com. So based on the people that have stayed up here for this webinar, we're going to just do a simple poll for someone to win. Alright, let's see. I have a Beardoon is in the house. Yes, a Beardoon in the house. You have Celestine. You have different people in the house. So I'm going to just do I'm also going to do a poll. We're going to do a random, let's just do something in Excel to find out who wins this. Okay, so let's see. And get everybody's names out. Okay. Copy email address clipboard. Let's see. So I have everyone's names just so that we know everybody should pick a number. So let's see. Can you guys any number at random? I just want you to pick one number and type that number into the chat or into the question box. And that number is going to represent you. So here I am here in Excel. I'm just going to have a blank Excel. And I want you to pick a number in the chat that represents you. Anyone that picks a duplicate number, then that's fine. So I can see someone with 9, someone pick 9, someone pick 8. Okay, you all know yourselves when you pick those numbers. Someone picked 8, someone picked 12. So if you look at the question box everybody is picking numbers. Two, three. Okay. Someone else picked 12. So Aliquay, is it Jesse or me? Can you pick another number? Someone has already picked 12. So pick a random number because someone had already picked 12 before you. So can you pick something else? Can you pick another number? And then 3, Bardi, someone already picked 3 so you can pick 3. I know someone picked 1. Bardi, someone else picked 3. Okay, so pick a different number. Any number at all, 50, 500 doesn't matter. And two people follow pick 7, but someone else pick 7. Follow pick 7 before Onye, Uchi. So Onye, Uchi pick 7. Pick another number. Don't pick 7. Too many people have picked 7. Everybody must have a unique number. I'm not going to win with 0. The random number generator I want to do is pick 0. So pick something else. So 14. Good. I can see it's 14. So everybody knows their number is 14. I'm going to close this now so I know I have the numbers that I'm going to use for the poll. Yes, 7. So the first person that had 7. Yeah, 7, 14. Good. 54. Good. 54. Alright. Let's stop here. So everyone has a number. Now if you had a duplicate number, you have the first person that picked that number that gets it. So let's see who is winning this. So we're going to do a quick poll now. Alright. If I don't have your number, you better shout before I start. I'm going to do a small lottery. So 5. I can see someone with 5. And I can see someone with lol. Anyway, alright. So let's do the lottery. So these are the numbers I have. I'm going to use this lottery and the lottery simply is this. I'm going to use a random number to do the lottery and I'm going to use the offset function. So let's assume I'm standing here. I'm just going to randomly go down here and pick somebody's number and then whoever has that number is the winner. So I'm going to tell offset to stand here. Let me make this a bit bigger. And the funny thing is I'm going to write a formula in here. And this formula I'm going to, let me make it bigger for you. So I'm going to insert a shape so you can see it clearly. I'm inserting a shape like this and this shape is going to show me what the answer is in here. I'm linking that shape to this. And this shape, I'm just going to make it very large. Yeah. It's going to be the number that will come out eventually. And centralize it. That's that. Fine. I'm touching this. The number that comes into this shape is the person that wins this. So if I come in here, I'm going to use offset. The offset I wanted to teach you guys. Offset is a very simple function. Offset just says, hey, stand here. I'm telling offset stand there. And then I want you to go a number of rows down. And then I want you to also go a number of columns left or right. I want something to stand here and then go down based on this. And then also go right. The next one is for you to go left or right. I don't want you to go left or right. So the only thing I want you to do is go down. But I want you to go down randomly. So let's see. This is 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12. So there are 12 numbers here. So I'm going to tell you to go randomly. Randomly go down between 1 and 12. So if you look at this formula. Offset stand here. And I want you to randomly come down any number that randomly comes between 1 and 12. So when I enter now, whatever number you see here is a winner. Which are these numbers you see here is the person that wins this. So are we ready? Drum rolls, we're ready. Let's go. Who is the winner? Oh, 0. Is there anybody with 0 here? No. This is not working. 0 is one of the random numbers that I can come out with. But I don't want it to be 0, obviously. 0 means don't go anywhere. Just stand there. Let's enter again. Ah, random. F9, F9. F9 is not working. What's happening? Run between. Let's check formula break. Let's look at this. So run between 1 and 12. And then number of columns you should move is 1. So this 1, sorry, this should be 0. It's made the error. It's very good that it made that error. So if you're standing here, we said come down whatever you come down and then 1 means go right one step or go left one step. That's what 1 means. Move a column right. We're now moving a column. So if I click OK, I'm now going to see the answer. If I click OK, it's now going to not move and then watch this. The answer is who wins is 9. Who had 9? Let me see. Who is the person with 9? Abhi-Ola Sani, the winner. So Abhi-Ola Sani, let's give it up for Abhi-Ola Sani. He's the winner. He's the winner of the online training. Now, if you have done our online training already, you can gift it to someone else. So you're winning this course. Thank you. Let's give it up for Abhi-Ola Sani. And let me show you what you've won. You've won this. So you've won one of our courses on officetraininghub.com which is report automation in modern Excel. So that's the course you've won. So it's coming up now. I'll show you www.officetraininghub.com. You're winning the reporting automation in modern Excel. So the course will teach you how to automate anything, any report in Excel. Any report at all you have in Excel to teach you how to automate it. You will never need to do copy and paste in your life again. So it's $70. So you basically want a course worth $70. Report automation in modern Excel. So that's what you want. Congratulations to Abhi-Ola. So Don Abhi-Ola Sani, we will send you an email to confirm it and give you your code so that you can get access to this on officetraininghub.com. So big congratulations to you. So the challenge still remains, guys. Try and build that tool, the translator tool, and then whoever builds it gets a free. You can pick whatever today course from our lineup of courses for absolutely free, right? So build that tool and you get that absolutely free. So the Excel function translator tool. That's what you're supposed to build and then you get training free. And of course you can all go to officetraininghub.com and get some training, trainings there. So for those that want to learn Power BI, we have a very detailed Power BI course. It's a very detailed three-day course on Power BI. And what you get taught is what you have on the screen. So this is the reporting and analytics with Power BI. So this is what you get taught in three days. You learn how to build data models in day one. You learn how to write DAX in day two. And then you come with your own data and automate your own reports in day three. Building your data from scratch with the Power BI. So thank you very much everybody. I'm going to give you another poll please. I would like you guys to answer some other second poll. So if you can quickly answer this second poll for me, that would be cool. I just want to know what Excel tools you currently use. So what are the Excel tools you use currently? So 80 something percent of us use VLOOKUP. That's wonderful. Let's quickly answer it. I will close the poll very shortly. I've taken most of my time. So that's what you use. So 69 percent of us have used INDEX and MATCH. This is a very advanced class. I kind of remember that a lot of you are using Excel for quite a while. It's 5 percent VLOOKUP. 92 percent of these people are tables. This is a very, very smart class. A very, very smart set of people for this webinar. Very interesting. So what about Power BI? Do you guys use Power BI? Do you really use Power BI? And if you have, then what are the modern Excel modern Power BI tools that you've used? Have you used any of these Power BI tools? So which of these modern Excel Power BI tools have you used? Yeah, I know you've used Excel, but this modern Power Pivot, Power Query, Power DAX, M, Power View, Power Maps, if you've really used them, not just played with them, for your work, can you take? If you really use them. From the statistics I'm looking at as people are entering, 27 percent of us have used Power Pivot. Nobody has used Power Query. To me, Power Query is like one of the most important tools in Excel. It helps you clean your data automatically, automates all your cleaning process, clean up your data. So maybe if you guys, can link you to register for this webinar. You can register for the next webinar and ask questions specific to Power Query so that we can focus on that next month. And some people want financial modeling. If you do, you can send us, put it there when you register for next month that maybe you would like a separate webinar for modeling, which I think could be good for financial modeling. Alright, so that's it. 27 percent of us have used Power Pivot. Power Query here, that's strange. You use it on a regular basis. 9 percent use Power View, 9 percent use Power Maps and about 73 percent of us say we've not used any of these tools at all. Okay. So thanks everybody. I think it was a really, really great time we spent together and I look forward to seeing you in the next webinar. So we are done for the day or for the month and some of this is going to be put online. I'll do the Excel. I'm going to record the those Excel shortcuts or tips I talked about. So put all of that online. So you should get an email from us on Tuesday and hopefully I get an email from you to win the challenge. So hopefully you'll be able to win that challenge which is the Excel this Excel function dialogue or Excel function I mean Excel function translator. And again, no VBA. No VBA, you're just using this and you're feeding from the data. Download the data right now from the handout. You have it in that tool you're looking at right now. Download this. You can download this tool. It's very cool. Send it to somebody you know from another country. One of these countries here, English, French, German, Danish, Dutch, Italian, Brazilian or Czech. It translates all the functions to that. In fact, you can even use it to learn some of those languages. So guys, thank you very much and we see you next month. Bye-bye.