[00:01] we have office supplies. Okay. And then the immediate question comes is um what is the total sales for furniture? What is the total sales for technology? What is the total sales for office supplies? Is it here? We say what is the different [00:15] categories? And each category wise, what is the total sales? Subcategory wise, furnitureures, we have a bookcases, chairs, tables and so on. So each the business is happening? Each region wise, how the business is happening, [00:30] country wise, city- wise, state- wise, uh segment wise, customer wise, or we have the order date here. Okay. Year-wise, each year how much business wise, daily wise, how much business [00:42] happening? So, we try to ask this kind of a questions. Is it okay? Fine. So, of a questions. Is it okay? Fine. So, now based on that, so what we say is whenever you look at the data, we always see the data from the two perspectives. [00:57] One is the data on which you can apply the calculation. One is the data on which you can apply the calculation uh calculations. Okay. So like example when you see sales quantity discount profit these are numerical values when you look [01:13] at the data this particular values. So we say hey what is the total sales? What is the total profit? What is the average sales? What is the highest sales? What is the highest sales in any region? what is higher sales for any particular [01:26] category or product. Okay. Uh what is the kind of a profit percentage? So that means when you look at this particular data we always try to apply the aggregations on that means the calculations on that. So based on that [01:39] we call this fields as measures we can call this fields. So or as measures these I call it as measures. Apart from that we have this particular non sorry [01:51] uh non-measurable values. Okay. Or we say it's a categorical datas like category, subcategory, region, segment, order date or we say um uh country, state. So these are data on which you cannot apply the calculations but yeah [02:06] is the total sales? How many different categories are there? Each categories region may how much sales have happened? Each um sales segment may how much sales have happened? How much profit has happened? We try to do that. So these [02:21] non-numerical datas or um the discrete values we call it as a dimensions okay dimensions are the discrete values whereas the measures are the continuous [02:34] whereas the measures are the continuous values. Got it? Hello. Okay. This is the first thing uh this is very important thing I can say. Okay. Whenever you look at the data we always see the data on which you can apply the [02:46] calculations and the data on which you can just do the groupings. Okay. So the data on which you apply the groupings we call as a dimensions or we call that as a discrete they are the discrete values and the data on which you can apply the [02:58] measures. They are the continuous values. Okay. So I cannot find out like you know hey what is the total sales of 261. No every has their own values is that so I can always say like you know hey I want to see those products where [03:12] hey I want to see those products where the sales value is more than $100. Okay. but here mainly we try to do a aggregations like what is the total sales average sales minimum sales what is the highest sales okay so those kind [03:25] those calculations only thing is we can have a count hey how many different categories are there how many different subcategories are there got it okay this is the first thing to understand so thanks hey now uh since like you know [03:38] before I get into the powerbi so I always say like you know in excel if you people have worked with the p tables I hope everybody have worked on Is it uh it's an amazing tool for summarizing the data, analyzing the data again uh very [03:53] uh amazing tools. Tick tick tick and the job is done. So that is just few clicks and we can analyze this complex data very easily. So almost 10,000 rows of each region wise how sales how much sales have happened or what is the total [04:07] sales or category wise sales. I can easily do it. So what I do I generally go to this particular pivot table and say hey I just try to keep it here itself is it all or know about pivot tables no um so [04:20] if I have to know what is the total sales I just click on the sales see as soon as I click on the sales you know sales is the numerical value it is has applied the aggregation as sum of sales okay and you can see we are [04:33] getting the sum of sales here this is the total sales 2.3 million okay 22 the total sales 2.3 million okay 22 lakhs 97,000 so which is 2.3 million Now the same sales I just want to find out for each region wise okay or you [04:46] is the region here so I just click on this as soon as I add a region you can see here the region doesn't have a numerical datas it is a character datas so automatically the region is being put picked into the rows and it is taking a [05:00] distinct value you can see hey out of the 10,000 rows of data hey this were the four regions were there okay and each the same 23 lakhs okay is been divided Ed something like this in the central region what is um five lakhs [05:14] okay uh thing so out of 23 lakhs you can see five lakhs of business happened in the central and the say 6 7 lakhs business happened in the east region south region is 3.9 lakhs and say west region has done 7 lakhs 25,000 okay fine [05:29] automatically able to do it so this is the kind of analysis what you're trying category wise what the sales are happening within that subcategory wise Okay. So it's an amazing tool like you know with AP tables. Now we'll try to do [05:44] something similar to this in PowerBI. Okay. So fine. So now yeah that was just to get uh no understanding about this particular data and things. Now what we [05:56] will do is we'll try to do a similar kind of a job with the PowerBI. Okay. So first thing is we need to extract this data. See PowerBI doesn't store the data. Excel beauty is what is it? Excel [06:08] can store a large amount. You can store the data transformation. If you work is even the power pwords in Excel. Yes, we can perform the data modeling in [06:21] Excel. There is also the power views in Excel. Okay. It has multiple things and it also has lot of features in Excel okay things. Okay. But in the PowerBI desktop, okay, or PowerBI, they don't store the data. So when it is needed we [06:35] can pull the data from the different data sources and with then we go ahead data sources and with then we go ahead creating the various uh visualizations operations we can do it okay what I said so it has a power query it has a power [06:48] data modeling and various things so now we will go with some basic u uh the summarization of the data okay so what I will do is okay um I'm just closing this don't do any changes on this okay just say don't save this I'm just closing [07:03] Okay. Now I just want to read this particular uh file into my what is say PowerBI. So what I do I just start my PowerBI. So I'll just go to the PowerBI desktop. I just go to the PowerBI desktop. So I'm [07:17] just going to this particular PowerBI desktop. Now we can read the data from the different data sources. Like say example this is one lake, Excel workbook, SQL server and so on. Okay. Now as I said we can read the data from [07:32] more than 100 different types of data sources. What did I say? We can read the data from more than 100 different types of data sources. Now [07:45] uh where is that? So we can see here most commonly we use Excel because people as I said jump from Excel to this but in the real time Excel is not preferred for storing the data uh for and this one analytics. The reason is [07:58] Excel occupies a lot of space. Okay. And the same data you save it in the CSV format, it occupies very very less space. Okay. Binary format, CSV formats. Okay. Or when the data is very large, the data will be stored in the database [08:11] servers. They have more security, multi-users and all those things. So now um if I have to get the data from any data sources. So we can see we have this option here called as get data from the other sources. Okay. Most commonly we [08:25] use Excel. So we can directly hit on this and one go. But as of now I'll show you them because I told that 100 numbers. No. So I have to prove that I'm right. Okay. As soon as you say this get data from the other sources. You can see [08:38] it shows me the list of data sources here. And if you count I'll not be wrong here. And if you count I'll not be wrong there. Okay. So I will be reading the just select it. See we have lot of it [08:50] types of database servers. uh Microsoft fabrics, power platforms, Azour, online services, okay, the file systems and so on. Okay, so I'll just go to this [09:02] particular Excel workbook because my data is present on the Excel and I have to read the data from the Excel. After selecting, okay, the type of data source that you wanted, we will just click on the connect. [09:17] the connect. Okay, I'll just click on this connect. connect it just says hey where is your data source present we can grow browse [09:29] and go to the location where your data set is present okay so I have shared the keep it in the download just have a separate folder might be with my name or something okay and have the data set and try to read from that particular [09:43] location okay so uh I'll just say I'm just using this which is present in this application fine so I'll just select this and just say open select the data source file here and click on open [10:00] now we are telling that okay I'm reading an excel data and where since excel is a where it is yes I gave the location I said uh loaded it is getting loaded now you can see here I showed that we had three sheets there orders people returns [10:17] from that in Excel we have The concept of dynamic tables there I have created referring to the orders those are the tables okay uh need not be like you can [10:29] itself okay so uh since I said I want to read the data from this particular file called as sorry sheet called as orders in that particular file so it is showing the preview of the data yes this is what [10:44] the data I showed this is the data I wanted to load it just a cross check have you selected the uh data set. Okay. And then we click on uh data set. Okay. And then we click on the load. Then again click on the load. [10:58] We'll be performing doing this data transformation and all this thing the power query in the coming sessions. Okay. So we'll go to the load l a d Okay. So we'll go to the load l a d load. I'll just click on load. [11:13] When you click on load, what happen? The data gets loaded to the PowerBI desktop. data gets loaded to the PowerBI desktop. or are doing parallelly with me. [11:28] Okay. If not you can do this the data gets loaded. Okay. So you can see once the data is loaded on your right hand side we have [11:42] this particular data pane. So which just shows that there is a table loaded called as orders and when you just expand this it shows me all the fields whatever the fields I showed you there. So all the fields are been kept here. [11:58] Oh here it is sorted in alphabetical order. So it is category city and so on. All the fields what is there is there. Yeah. [12:11] one, um, so now what you do is um, uh, as soon as you click on the sales, you can see automatically it creates a card for me telling that you know the total sales is 2.3 million. It's so easy, is it? [12:24] Okay. So there in Excel, we use a pivot tables and then I have to go for sales and you get a number which says 22 lakhs 97, uh, 287.973 which is almost 23 lakhs. So but here [12:38] you can see it is summing up all those particular sales value and the way it is being presented is in the best readable format 2.3 million. Yes. format 2.3 million. Yes. Okay. Uh all are able to do this much. [12:54] If you observe your interface also is slightly different. You don't have this as a build suggestion but you have two different tabs here. Is it okay? And I think you're missing out with this things also. there are some things [13:07] things also. there are some things missing is it okay he the reason and I do also have lot of many other um uh tools and all this particular stuff is it cool now the reason is um see what [13:20] happens is powerbi gets updated very frequently okay see generally when you talk about any softares to update the new software they take at least 2 to 3 years minimum 2 to 3 years okay because first the master software is released in [13:34] the market Okay. And then they take the reviews. Okay. And they feedbacks for one year and then next one year they work on that and they take another 6 months to one year to release that [13:46] market into the market promotion all those things. Fine. So it takes 2 to 3 years for the new software to come. But here is not like that. They are coming out with updates on every 30 days. Every month on month they're coming with [13:58] updates. Now you want to get updates for that. You know if you have to uninstall, that becomes a tedious work. Is it? Okay. So to get the features, the preview features, okay, the new features, updated features or the [14:13] preview features. So we can do something like this. Just click on the file. Just click on the file. Under the file, you can see there is an options and settings. In the very bottom, we have this options and settings. [14:31] file you have this options and settings. Just click on that. Under the options and settings again we have the options. Under the options and settings again we have the options. Just click on that. [14:49] Under the file options and settings and then options when you click there so you get this options. There you have something called the preview features. You can see this preview features. [15:03] preview features. Okay. In the preview features, so in my case uh you can see all the checkboxes are selected. In your case maybe most of the checkbox is unselected. Am I right? [15:17] Am I right here? So you can check all the check boxes. You can select all the check boxes. After selecting you have the okay button in the uh bottom. You just need to click on okay. [15:31] So click on okay. When you click on okay it'll ask you to restart it is you don't have to restart your machine. It is just the PowerBI. You need to close and open the PowerBI. That's it. Automatically you'll get the updates [15:46] and you can see your interface will be same like in my interface. the PowerBI and open the PowerBI again. That's it. [16:04] need to close the PowerBI and open. You don't have to restart your machine. Okay. Fine. So once you open it, you'll be able to see your interface is same like mine. And now you click on the sales, you [16:18] this particular sales you should be able to get a card like this 2.3 million. It's like a scorec card which gives you a complete summarized value. When you are watching the match cricket or any kind of a match you know uh it shows you [16:34] a summarized value. So our mere focus is on the card no keta. So what is the total score? Okay. How many wickets? What is the run rate? Current run rate. So those summarized values we try to show it as a cards. Okay. Then the cards [16:47] show it as a cards. Okay. Then the cards comes with a bigger form. Yes. Okay. So, comes with a bigger form. Yes. Okay. So, directly the sum. Okay. Fine. We'll look into what is card. Okay. But now uh we will go with [17:02] um what do you call it as the tables. Okay. Now another thing is okay uh before that you know uh you try to select uh just click outside of this. Don't select the card. Okay. Now you click the regions. [17:17] Click the region. What is happening? You are getting a What is happening? You are getting a table with the unique regions. So you're able to do this. So what we understand from this one is [17:31] when you select a measurable values it is performing the aggregation the total is performing the aggregation the total sales. But when you select a discrete sales. But when you select a discrete value, okay, it just gives me a discrete [17:43] uh what do you say a distinct um um the dimension that you have selected. Okay, so you have selected region it says oh you have four different regions there and have it something like this. Okay, now to get things going. Okay, so this [18:02] is something to do with the data. Okay, this kind of a symbol. No, database symbols we see. Okay, so it shows me all the tables and the fields and when I talk about this one, this is for building your visuals. Building your [18:16] different types of charts, tables, summaries and you talk about this symbol here. This is formatting. This is used for formatting. Okay. Uh if it is hidden just hit on this data build format. Okay. I think so [18:34] you'll be working on this particular canvas for creating the report. Okay. So now what happens you know if you keep all this open so you'll be compromising your canvas space. Okay. So based on your requirement keep only what is [18:48] required you can keep it open or it just pops out automatically. You can see in and out is very flexible here. Pin outs and in and things and so on. So we can pop out and pop in things so that we have a better uh space for us to work. [19:02] Okay. So I don't recommend you to always keep it open but yeah it is on the fly just a single click it just collapse and expands respectively got it now okay uh I'm just deleting all those particular cards and everything so now we will see [19:17] cards and everything so now we will see with the tables okay so when you see in this particular build we have the different types of visuals okay which we >> uh the different charts are there and so on you can either take it from the build [19:30] on you can either take it from the build or even from the insert uh option menu we have the same thing as well as in the home also we have the same thing either from anywhere you can use it the first thing is we will take this particular [19:42] table okay what is a table is nothing but is a collection of columns okay we'll try to work with the tables now so I'll just select this particular table as soon as I select this table we can [19:54] see a small what do you say the interface has been created here it says you're going to add the table Okay, you're going to add the table. Now, >> What? >> Table is a collection of column. Please [20:08] mute. No, who is there? So, uh it is a collection of columns. It is just a collection of columns. So, as soon as you add this, you can see in this particular build it says to add the [20:20] columns. What columns you want? You can add anything. You can add a dimensions. You can add a measurable values. You can even add the date fields. Now what I will do is again see if I just say [20:33] will do is again see if I just say category okay um when I say category you categories are there so we have furniture office supplies and technologies fine apart from that okay when you scroll here you can see we have [20:47] more ad items you want to add each category mean what is the total sales I just hit on this add and you want to add the sales we can search like this and you can do it or you can even uh type here and we can get [21:00] say sales. So it shows me each category wise what is the total sales. Okay. Or if you go to the build options here we have more better interface here. [21:12] So it says what fields is needed. So like say example you want to add the region. Select the region and I say sales. It shows me each region wise for sales. It shows me each region wise for the sales of got it. And now see what [21:25] happens is here see in Excel we call it as sheet. Okay. But again in a single sheet we can have multiple tables, multiple charts and everything. But in PowerBI we don't call it as a sheet, we call it as page. Okay, we have the [21:39] option of adding the new pages here. Okay, now when I say page, page is like a report. We can have multiple visuals. It can be a charts, it can be a tables, it can be the cards. Okay, also we have a slices and all these things. Okay, so [21:53] we'll be learning all those things. Now when you look at this one, it shows very small interface here. Okay. Now you want to enhance that we can go and format that the font and all the things. But at the same time we have something called [22:07] as a focus mode. So when you see when you select this visual we have this one. you select this visual we have this one. This is called as what? Focus mode. [22:23] want to bring the whole this particular visual to the whole screen so that I can look at it more clean okay more closely. So we can just hit on this focus mode it just brings in front of you and you get more better visibilities and when you [22:37] say back to report it comes back to the report because in a report you can have on a particular visuals you want to look at the individual like labels and all those things I can bring it to the focus mode okay and we say back to the report [22:52] it just comes back focus mode is it is showing only one single what do you say the visual okay is one single object is covering the whole screen. Okay. So that we can work on that and once it has been done say back to report it is a report [23:07] will have multiple visuals like this visuals is representing tables or charts visuals is representing tables or charts a slicer or any objects. So now see you makeup job I know this very uh small not [23:20] want the heading with a different color the four total I want in the different colors. You want to do those particular kind of a things. So what you do is just take this paint brush. Okay, you have a brush here also. Okay, you can go to the [23:32] more options or from the right hand side also you can see that is the formatter paint. Okay, so we hit on this one to do the makeup job here. I can just go here and see these are called as the values. No, the totals, values and so on. Just [23:46] click on this values and you can see it shows what is the font you want, what is the size of the font, bold, italics, color, everything can be done. So I want I don't by default it is given as a 10 points. Okay, see UI. So you can change [23:59] the font. Okay. So I don't know which is a good one. Whatever the default is want to have a bigger font. I'll increase the font size. Yes. [24:11] So black and black and white colors like colors you want to change for the text, you can just do that. Alternate text also can be done. [24:25] there. So you want to have a totals with a different color. So we can go even to the totals or you want your totals to be displayed. So you want the totals to be displayed in a different color uh with a different background. [24:41] Yes, we can do that. I'm very bad in color selections. But yeah, and we have a headers. So all names are like English names, you know, [24:53] column headers. how you want your headers. Uh headers may I just want to have a text color. Yeah. And I just want this to be as 20 [25:06] bigger. It's too big, isn't it? So 16. So one of So you want to have a different color. [25:20] possible as that professional looks kind of a things professional looks kind of a things default is this. Yeah. So we can give something. Yeah. It is in photos [25:38] So that you can go to the values and just work on the fonts to have a better. Now we just got the subcategories and the sales. Okay, when I took the sales automatically it has taken the total sales. Okay, it has been grouping the [25:53] totaling sales for each subcategory. Right, this is the total sales for each region. Now the default aggregation is sum. So which generally happens in the p dimension, it takes the unique values when you take a measurable value uh it [26:08] takes the sum. Okay, as a default aggregation but I say no I want to have one average sales I want to know okay things for each region may what is the average sales you want to go for that so I can just select the respective visual [26:23] okay and we have the sales no next to the sales we have a small arrow there just click on that instead of sum just change it to average this shows me the average sales or you want to change it to higher sales in that region lower [26:35] sales in the region we can just do all those things okay the default sum and most commonly we use a sum. Okay, this is how we can change the different aggregations. Okay, hey that is there. Now the next [26:48] thing is you want to add multiple measures. Say say example we take a region or subcategory. I think I'll take a subcategory. Okay. Uh now we have a sales with the sales I want to have even the profit. Okay. So I can just add the [27:02] profit here. Okay. Um so when I just hit on this say profit it just shows me the profit. So that means it tells me each subcategory wise what is the total sales and what is the total profit. So we can [27:17] see here I just added for each subcategory total sales as well as the total profit. So it says accessories we have a total sales of 167k and the have a total sales of 167k and the profit is 42k. Okay. Appliances 107K is [27:32] the sales and 18K is the profit. Arts 27K is the sales, 6.5K is the profit. Uh you can add multiple dimensions and multiple measures. How much quantity has been sold? Yes. So we can see accessories. Oh, we have a total sales [27:47] of 167K for which we have 42K is the profit. How much quantity is sold? 2,976. There is almost 3,000 quantity is been There is almost 3,000 quantity is been sold. Okay, we are able to add multiple [27:59] dimensions and multiple measures. We can add it. I can just have a slicer say which talks about the category and um I can just say furniture. It shows me one technology. But I'll come to the filter later. So not we'll not confuse with [28:15] this. Now how do we do that one is again just get into the format. All these are just select this particular visual. So you are applying the formatting on this and there is something called as a specific column because see when it [28:28] comes to column headers, totals, values, grid, style and all these things are a general format. That means you are formatting at the whole table level. But here I want to format based on the individual column. Okay. A specific [28:42] columns. Okay. So you can see we have this option here called as a specific columns. Yeah, you can expand this. Under the specific columns you say what want to format the sales. When you select the sales so we have a text color [28:57] want to change the display unit. There is a display units here. Just select this and say what is that? You want in thousands. Yes. 330K. You want a decimal places. No, I don't want the decimal places. Say zero. Cool. [29:12] places. Say zero. Cool. Got it. want, you can just format it. Hey, this was something about the table. Now percentage. How do we get a percentage? Say example. So I'll just take this [29:28] particular table here. We have a region and we have a sales here. Uh central region have a sales of 500k. East has sales of 678k respectively. Okay. Uh I don't want to see the numbers like this but instead of [29:42] this I want to see this in the form of percentage. How much percentage of the region central? How much percentage of the sales that is being contributed? East, how much percentage of the sales is contributed? I want to look for that [29:55] one. So in this case, what is that we do is just select the same thing again. Okay. Go to this particular build. In the build, okay, we have this option uh sum of sales. No, just right click on this. Right click on this. Okay. And [30:09] here we have something called as show values as. There we have a percentage of the grand you can see here it shows in the form of so from the east region we have 30% of the business coming from the east region 22% of the business coming [30:23] from the central region 31.6% business coming from the west region and only 17%. So highest business coming from the west [30:35] is 31.6% and the lowest business coming from the and the lowest business coming from the south region which is just 17%. what I saw in the chat. Hey, I don't have a habit of looking into the chat [30:48] I just went through this was the question. I hope I have answered all the question. I hope I have answered all the questions. See, just select the table. In the build we have a sum of sales. Under the build we have a sum of sales. [31:03] Just right click on that. We have this option here called as show values as. Show values as and there we can see we have this option here called as [31:15] have this option here called as percentage of grand total. Percentage of percentage of grand total. Percentage of grand total. Click on that. Yeah. Fine. We'll move to the next one here. Now we have this particular table. It shows me [31:27] each subcategory wise how the sales have happened. Now just imagine I want to add one more dimension to this. The phones. I just want to know the phones. how much they have been sold in the east region, the west region, central region. Um, [31:41] wise, how much they have contributed. So what I do is I just add a region to what I do is I just add a region to this. I just select the region. I just soon as I add the region, you can see here. So what is happening? It just says [31:55] like you know the phones. Okay, each product each product when you see here so that has been sold in each region. So that means it says accessories whatever the total sales was 1 lakh 67,000 no 167k you can see it is divided into [32:10] region okay so that means like you know it says the accessories the total sales of accessories in the central region south region east region west region then again it says hey appliances in the each region wise how the sales have [32:24] happened in the each region it is showing this okay now in this case what happens it becomes difficult to analy analyze this data which is running like a on this one is it the second thing is I don't know [32:38] region I don't have that I don't know what is the total sales of accessories under the each c subcategory each product says here region wise how the sales has been done okay so instead of seeing the data like this it'll be [32:53] better if I can put this in data in two dimensions is it rows and columns that is what we generally do on the p tables okay now in the tables we don't have the rows and columns. We just have only the columns. Now for that what we do is we [33:07] do also have an uh another thing called as a matrix. We'll go to the matrix now. Okay. What is a matrix? Matrix is nothing but a two dimensional data. Where is the matrix? Next to the table you can see there is a matrix here. Next [33:23] to the table you can see that there is a matrix here. We can see there is a matrix. Yes, there's a matrix. This was a table. This have the row headings, column headings and the data. I'll just select the [33:36] matrix. Okay. Now I'm selecting the matrix here. Now when you go to the matrix, since you have selected the matrix here, so we can see that we have three things here. Hey, what data you want in the rows? What data you want in [33:50] the columns? And what data you want as the values? Okay. Now I say in the rows, I just want to have a subcategory. And in the values I'll say measurable value I'll say sales. So we are able to [34:05] see the subcategory wise how the sales are happening. So you want to copy the same formatting features we have this format painter and paint it on this. Yeah it takes the formatting features also. Fine. So here you can see uh each [34:17] product okay what is the total sales accessories? Same like a table but in the case of what you say a table when you add a region. So this [34:29] was further divided with the regions and it was splitting the number of rows. But for that purpose what I do is when I go to this matrix we have rows and columns. I'll go to the columns and there I'll add the region [34:41] when I add a region here. So we can see we are able to get okay the informations like okay so how the sales have happened in the each subcategory okay accessories [34:54] in the each subcategory okay accessories the total sales is 167k okay chairs total sales is 328k we are able to see each product what is the total sales as well as we are able to see what is the total sales for the region central [35:08] region south region west region we are able to see the region wise total as well as we are able to see the grand well as we are able to see the grand total. Find all apart from that we can [35:20] also see okay this is nothing but a particular product in a particular region how much sales have happened. So that means we are able to see that accessories have done a sales of 167k but out of that 167k we see that in the [35:35] central region it has done 34k in the east region it has done 45k in the south region it has done 27k and the west region it has done 61k [35:50] sold in the west region 61k then is the east region 45k there's a big difference then we have 34k in the central and the very low sales coming from the south region. Got it? So we are a not only able to see the [36:05] information more easily. Okay. Since the data is in the two dimension, we are here. We are able to see the category wise total, the region wise total as well as the category wise total for the individual region and also the grand [36:19] total. Got it? So this is your matrix where I Got it? So this is your matrix where I can present the data in two dimensions. [36:31] features of formatting specific columns and all the things. Yes we do all have I don't want to spend more time on the cosmetic work. So makeups and all the things all good were you? Hey, apart from this one more thing what [36:46] I like in the matrix is we can have a drill down information like say example I'll just take the same the matrix okay now I'll take hierarchical data okay like say example I take the category and I also take the subcategory that means [37:00] under each category there are some subcategories okay and I'll take the values as a measurable value you can take either sales profit or anything I'll just take the sales as a measurable value now when Look into this. I've just [37:13] value now when Look into this. I've just taken two things here. Okay, subcategory and category and subcategory. Then the values I have taken as sales, sum of sales. Now when you go with this one, so what you see here is okay whatever it is [37:28] here. Now you see that uh it is showing me the each category wise what is the total sales happen. We see that furniture we have a sales of 742K, office supplies 720K, technology 836K respectively. Okay. Now uh there is a [37:44] plus sign here because under category we have a subcategory added there. So that means when you hit on this plus sign it expands. So under technology we have accessories, copers, machines coming under that. Under furnitureures we have [37:56] this chairs, bookcases and things. Instead of expanding the individual we can also go to drill with the hierarchies here. Okay. So we have two is the next level there? It's a subcategory. It is showing me the [38:09] subcategory or category. What do you want? You can toggle it. Okay. I say no, no, no, hold on. I want to see the category and within the category I want the category and I want to see the within the category I want to see the [38:22] subcategory. That means I want to see this information with the hierarchy. So we can drill with the hierarchy. Here we have this option here called as drill with the hierarchy. So the second option here drill go to the next level of the [38:36] hierarchy. So that means it shows me with the main level as well as suble. Under furniture this is subcategories. So we are able to see what is the total we have this four different subcategories and what is their total [38:50] and under office supplies we have these other subcategories. Technology we have a total sales of 836. Under technology subcategories here and their individual [39:02] sales. Got it? We can easily do this particular drill. This is something which I really like it. Uh when I just drill with the hierarchies here, it is just going Hey, now we'll go with the hierarchies here. [39:17] level. Next level will show me the quarterly wise, month- wise, what is the sales? Okay. Or day wise, individual day wise how the sales have happened. This is a cumulated value. So that means when I say this is the Q1 sale, Q2 [39:31] sales, we have four years data. It is the cumulative in every year there is a Q1. And it is summing up all the Q1s of all the years and saying telling this is the month wise says January the total sales of January irrespective of the [39:44] year but I say no I want to show as the running total. Okay. So you drill with hierarchy says and each year the quarterly wise and each quarter you want to see the month wise we are able to see. Okay. So that means here what [39:59] happens is when I drill with the next level it shows me the cumulated value. this is the January sales means irrespective of the year. Okay. And I want to drill with the hierarchy is like you know that is drilling 2018 within [40:14] that year quarterly wise within that year within that quarter month what is month wise. Okay. So now we can interchange this one. So what I wanted quarter. We can just remove the quarter each year within the year month wise or [40:29] I want the year in this particular column. So I can just take the year and place it here. Now is it I can remove the year from here keep only the month and in the columns go to the order date and just select only the year. So this [40:42] shows me in the matrix where I'm able to see the year- wise total month-wise total as well as within each year month wise how the contribution is happening. Now the next thing is hey just before I go with the visualizations like you know [40:58] um conditional formattings you have worked in Excel. the conditional formatting for a better understanding of the data. Okay. So I say uh we use the conditional formatting for the better understanding of the [41:12] with the help of the conditional formattings. Okay. So in what sense is see we have the accessories which is sold 1 lakh 67,000. Is it a good sales? If I have to know that I have to compare [41:27] that with the each and every value when I compare with accessories. Yeah 1 lakh 7,000 and 1 lakh 67,000 it's way ahead you know 30 40% higher sales okay and [41:39] when you see the compared to arts oh this is very very good but when it come to binder no no no no no no again books is very higher so there are many there are many products many many products which is lower than this so [41:52] to judge whether it's a good value or bad value or an average value or above easy when I use the conditional formatting So what I will do is uh I'll I'll just go to the condition formattings. I'll like to go with the [42:08] bars here. Okay. And by default it shows a positive bars blue color. I'll go with the default one. I'll just say okay. So you can see here it has been filled with those particular bars here. So by seeing that you can come to know that hey [42:21] accessories is just above 50%. Okay. It is just above average or average or just above average. Okay. And we see that chairs and phones have have done the highest sales. Okay, overall chairs and phones they have done the highest sales. [42:36] And we see that hey there are a lot of outlier values. So we see fasteners 3,000 we can see very invisible and compared to 3 lakh 30,000 this is very compared to 3 lakh 30,000 this is very very small. No not even 0.001% of the [42:49] phones is it? So we have too much of outlier values but that is what we are able to understand here. So we have some products with the small value products. Okay. The name only says labels, envelopes, papers, their value itself is [43:02] very small. So that means the sales is very low and we see phones we cannot things chairs for you and all they have a high value sales fine. Yeah. So we are able to understand in that way um like you know what are the products are above [43:16] average which the top leading products okay so which are the products okay which is doing a very very low sales we are able to understand the numbers. So numbers I can easily come to know okay where we have done a high sales where we [43:29] value of this paper it is below average low sales kind of a things that is what we are able to understand okay so I can say that we can understand the data say that we can understand the data better okay fine we'll move ahead here [43:43] now the next thing is I want to apply the conditional formatting for the profit where I'm getting a negative profit where I'm getting a positive profit so I can do the same thing I say take the sum of profit here and I can [43:55] just apply the conditional formattings and I say I'll take the same data bars you know the profit can be a negative value so if it is a negative value I say I want a red color bar positive value let it be blue or if you want a green [44:08] I'll go if you want a green color you can change the color like say example kind of a things um uh where we just understood what exactly is powerba is [44:23] one of the very powerful business intelligence tool. Uh when I talk about any business intelligence tools or nothing but a tools which works on a insights on that and based on that particular insights we are going to [44:37] create a reports with the help of which we can take a better business decisions. Now when I say it performs this so the first thing is the PowerBI has many components. So one is we have a power query the direct cleaning components we [44:51] have a power data modeling we have power views where we can create the visuals and we have a powerbi services where we can publish the report okay to the cloud from there we can share it so we haven't gone for all those particular things we [45:03] have to learn all those particular things today was just an start of knowing like you know okay what is data how do we read the data how do we apply particular data and then the tables and matrix which is very similar to the pure [45:17] pivoting concepts what you use it in Excel the same kind of a things so I can say the pivot table is an amazing tool in Excel for doing the summarization of the data analyzing of the data and so on so something similar to that we have [45:29] done in the powerbi okay hope it's interesting so tomorrow creating the different types of charts we can do the some visualization part we can do the some visualization part and then we can go to the power query [45:43] so where we can perform like we'll see how do we take some raw data and clean data data and how we can do the cleanings. Yeah. Hey, now you know heat map analysis like you know I can apply a conditional formatting on this one also. [45:56] Okay. So now here see always numbers are I always keep telling this you know numbers are only good when it comes to your account. Is it the bigger the number is more happier we are. Yeah. But uh if you have [46:10] to get some insights from your numbers okay small numbers are good there. big have more numbers you know it becomes really very um scary kind of a things [46:22] the insights like say I've just taken this year and the month wise sales okay now I just want to understand um during which period of time the sales are good during which period of time it's more challenging so I want to understand that [46:36] so you can just apply the conditional formatting for this this is sales you of sales I just go to the conditional formatting I'll just give a background default one. And you can see the way the colors have been applied, it just tells [46:50] you, hey, the sales are more good during the end of the year. So, we see that September, November, December, the sales are very good. Okay. The highest sales have happened in the November 2021 and the lowest sales have happened in what [47:02] the lowest sales have happened in what is saying um February 2018. Okay. And we challenging the light. So, the kind of color the way it has been colored here, the intensity of the color shows you. Okay. Uh high what is it say? Dark color [47:15] is high sales and light color is low sales value. So we see that okay the end of the year November, December and September. And we see the January, February is something like you know we have a very low sales. February uh sorry [47:30] March there is certain something good but um April to August are kind of towards the end of the year that is September, November and December. Okay. September, November and December. Okay. So yes, we can just do those particular [47:44] aspect. So if you want you can have more colors kind of a thing. So in the maximum value with the different colors you may say high value with the green you want to do those things I can just say hey low value with the red color mid [47:59] colors you want to have uh something like a lighter color and I want to say no. So we can easily come to know all the things this is similar kind of a formattings what we apply in Excel. In Excel also is always same thing we do [48:13] it. Okay. So we are able to I think this is called as generally heat map heat map analysis. So high values. Okay. We have the high sales towards the end of the year very low sales very challenging during the start of the year January [48:25] during the start of the year January February March spring season. No. So I think it's a very uh it clearly says that you know uh sales are based on the vacations and holidays. So we have September, November, December kind of a [48:37] there's a retail business you know we have a black Friday okay which promotes for the retail business okay and that is uh boosting up the sales during the end of the year and start of the year I feel people are busy with the projects or [48:50] something people who are from the US they should tell me if correct me if I'm they should tell me if correct me if I'm wrong here okay um they are less in spending in the retail segment and they are more busy with their official work [49:02] looking for a new projects and things yeah okay so that is what we are able to understand and see based on this we can look at like you know how promotional very challenging for the retail business doing a lot of promotional activities [49:16] there having lot of stock there becomes a dead investment okay we know that onwards there's a big sale coming so we can have a better planning month or two promotional activities we do how much stock is required okay what is the kind [49:30] that so have a better plan and have a better execution and so that we can have a better um what do you say business a better planning by looking at this kind of historical datas. Okay. So with [49:45] data the same numbers it becomes more easy to understand and we can take a easy to understand and we can take a better decisions on that. [49:57] Okay. Now we all have to look at the data from the business point of view. Okay. Hey this was just like you know the very intro. So we haven't seen any kind of a charts. So next session we'll start off with the charts. Okay maybe [50:09] the next two sessions we can see with the charts and then we can go with um sessions we can look into the charts itself. We can we have lot of things to the power query. So one or two sessions we can discuss about the power query is [50:23] transformation data cleaning jobs. We can see two sessions we can talk on the power query itself and then we can go to the data modeling and DAX calculations. And then we'll see about how to use AI in PowerBI. Okay. So there are some kind [50:38] of a AIdriven visuals are there. We'll look into those kind of a things and how kind of a DAX calculations with the help of AI tools or even with the chipity how we can generate this. Yes, I incorporate a chipity in everything now even in [50:52] Excel. I do use lot of chipities to write the Excel formulas and all the Excel. I really like Excel. I love Excel a lot. But with the help of a chat we can do really amazing things. It speeds up the job like anything. See [51:06] um if you have to read the data from the excel. So the first thing is to start off with powerbi. Okay. Uh we just click on the start. We say power [51:18] powerbi. Okay. There's a software. We need to Okay. There's a software. We need to open it something like this. [51:46] data from the Excel, I've already shared the Excel file. Is it yesterday? I'm using the same Excel file. What has been shared yesterday? Okay. So, it's a Excel workbook. >> Okay. Because in the case of a Tableau [52:00] in public, you cannot save it. So only the desktop you can save it. In the case cloud. Okay. Only if in the case of table tab table uh desktop you have to say you can you have the option of saving it to the hard disk. Yeah. Fine. [52:13] Hey, we'll move ahead here. Hey see yesterday we just saw with the tables matrix and so on. In today's session we'll see about creating the charts. Okay. See very commonly used chart is what when you talk about a chart the [52:30] very commonly used chart. So what is it is a column chart. Okay. So where the columns are been arranged side by side. So we will just go with a simple column chart. So if I have to go to the charts in the build option under the build okay [52:45] we can see that we have this particular what you say the charts option here. Okay. Um so I'll just select okay. All these are charts. Okay. All these are different different types of charts. We'll see some of these charts. Okay. [52:57] Not all at least some of this most of the charts. Okay. U we'll just see the very commonly used is the column chart. I'll just select this. Okay. Here when I take this particular column chart. Okay. See in the charts we generally have what [53:11] do you say? This axis is called as x-axis and um this one we call it as x-axis and um this one we call it as y-axis. Is it x-axis and y axis? Now what is a column chart? What is a column chart? Okay, the column [53:26] chart is nothing but a chart where each column represents a dimensions. Each column column represents its dimension and the length of this particular column represents the measurable value. So that means okay here um suppose if I say [53:43] region wise sales regions are my dimension each column is going to represent individual region okay east central west and south region respectively the length of that column will represent what is the measurable [53:56] value they have. So if I say sales how much sales has been done in the south west region how much sales is done in the east region. So we can easily do a comparison based on the length of the bar. The taller is the bar is the higher [54:09] value. See if I say sales higher is the sales is higher is a uh longer is a bar so on. Okay. So based on that so when you select this particular column chart [54:21] you can see in the build we get this option here called as x-axis and what do you call it as y-axis. We have the x-axis and the y-axis. So the this two [54:33] basic things what is needed for creating the chart. Now your y-axis is a measurable value. So I'll just say y-axis I have to use a measurable value. What is measurable values? Yesterday we discussed about measurable values and [54:47] numerical values. Okay. Sales, profit, quantity, they are going to be the measurable values. So I will take the sales. When you take the sales here into the y-axis, you can see automatically a chart has been created which just tells [55:02] chart has been created which just tells me the total sales is 2.3 million. Okay. So one single bar has been created. So that means if I have to create a column chart the minimum requirement is we need to have at least one measurable value. [55:17] to have at least one measurable value. Okay. Now we have the total sales but I want to see the sales for each region wise. I want to know okay how the west region has been performing, east region is performing, central region how the [55:30] sales has been performing. South region I want to compare. Okay. So that means I want to divide this particular column into the region wise. So for that I'll just go to the x-axis. Here I said you have to use any dimension. So dimension [55:43] can be region, segment, category, subcategory, states, it can be any discrete value. I'll take the region. So as soon as I say region here you can see we have the four regions. Accordingly four columns are being created and each [55:58] column is representing the individual region. I'll get into the focus mode. You can see hey this is the west region and automatically it has been arranged okay uh in the descending order where from left to right is it so the region [56:11] which has done the highest sales is on the left most and which is the tallest bar so we understand that west region has done the highest sales then we have a east almost very close competition but central region is far away okay and [56:24] have the lowest sales coming from the south region and we have the highest sales coming in the west region hey fine So here um I've just created this particular chart. So and the chart has been [56:38] created. Now you want to make this chart as informative means um I say this x-axis. Okay. It says the regions I want to make it more visible. Okay. Uh things [56:50] and you have the y-axis which is showing me the what is the kind of a value. Okay. 0 million.2 million.4 million. 6 million.8 million. This is something million.8 million. This is something between 6 and 8 million. just above 7 [57:03] million 0.7 million okay more than seven lakhs okay that is what we understand but instead of that you know I just want to show the values so I want to make this little bold or we have a titles here okay access title we call it as is [57:16] it or we say what is this hey this is sum of sales these are regions okay you want it or you don't want it so you have all those options in excel also is it so all those options in excel also is it so now you want to uh customize this okay [57:29] first thing is like you know I want to display the labels. That is what people ask for. Uh how do we display the labels here? Okay. So just select this particular format. Okay. So you see here. So we have the format option [57:43] there. The paint brush. Okay. There's a format option. Just go to the format can see there is something called as a data labels. By default you can see it labels. You can just say on. As soon as you say on you can see it shows me the [57:58] values. And you can see here in Excel what happens is by default the values will be displayed in this format 7 lakhs 25,458.90 Excel and we have to explicitly go and change the format but here it is been [58:14] automatically formatted to a best display unit okay uh that has been converted to million 73 million with two digits of decimal places. Okay. So it is digits of decimal places. Okay. So it is 73 million. 68 million.5 million.39 [58:28] million or we can say 7 lakhs 30,000 6 lakhs 80,000 uh 5 lakhs 3 lakhs 90,000 lakhs 80,000 uh 5 lakhs 3 lakhs 90,000 respectively isn't whatever you say. [58:43] what you do just expand this data labels. When you expand this okay uh you the positioning of this. You want horizontal or vertical. Okay. You want horizontal or vertical. Okay. You want to position inside outside. Okay. Inside [58:58] in the end, outside in the end, inside in the center, you want inside in the base. Yes, you have all those particular things. So I always say like you know the best thing is the default is always a really good one. So prefer to go with [59:12] the default one. But still you have the options of changing. So the same thing happens in Excel also. By default when you add a labels it comes in a default know big brains have given those particular settings and given that as a [59:25] default most commonly used but yeah so they have also given the options hey horizontally when you have too many columns I think vertical will be a better way to display okay so in this case I think horizontal will be better [59:38] to display. So if you ask me uh a good chart is one where we can we should be able to understand without looking at the labels I say that that is going to be a better charts. Okay. So if you're reading the numbers and able to [59:52] understand tables also is far better than the charts. No. Okay. So that means chart. You're focusing only on the number in spite you have a chart. So I always say that you know that's sadik says okay. Um so according to me I feel [01:00:08] u a good chart is one without even looking at the numbers we should be able to judge it. Okay. Hey, we don't go with the microscopic numbers, you know, 7 the microscopic numbers, you know, 7 lakhs, 25,489.75. [01:00:23] region. And when you see here the margin is 7 million. It is slightly above 7 million. Okay. So that is what we can see here. It is oh just below 7 million. Okay. You can see you have a dotted grid line there. Okay. So we can you should [01:00:38] if you are looking at a specific values, customize this particular labels. I've just expanded more options here in the data labels. And here we have something called as a values. Okay. So I can just [01:00:51] expand the values and you can say hey I want to make it bold. Yes. You want to increase the font. You want to have a different color. Yes. You want to have a background. Okay. Display unit. You say none. It shows something in Excel that [01:01:03] explicitly you don't have the built-in option of changing. No, you have to have it whether you want to display in thousands, millions, billions, whatever. thousands, millions, billions, whatever. And how the decimal places it says auto [01:01:18] is taken as two. But I say no, I want a one-digit decimal. Say one digit decimal. It converts to one digit decimal. So all those options are there. Okay, auto is the better one. Okay, I'll go with that. And you want to have a [01:01:31] background color. Yes, you want to have a background color. What color you want to have it for the background? I prefer something like yellowish color. U say the transparency. Okay. Uh when you say the background [01:01:44] overlapping too much. But at the same a things options you wanted. See, it's okay. So like this, you know, layout in the center, left, right. Okay. So [01:01:56] adjustments of this particular text can be done. So like, okay. Like this kind good. Okay. The same way like say X-axis. So this is your X-axis. Okay. In the X-axis, I want to make the text as bold. You want to make it as a bigger [01:02:10] font. You want to have a different font color. I just say no, no, no. We can give that contrast color. contrast color. Yes. We want bold. Yes. Accordingly, you [01:02:22] can just go for it. Okay. So, y-axis the same way you can just set it y-axis. Okay. So, minimum, maximum. Okay. How you want to display the labels. Okay. [01:02:34] You want to be informed with what kind of a color, text and things. [01:02:48] Grid lines if you want grid lines how you want the grid lines. Okay. So those kind of a things dotted grid lines dash grid lines. So things you can have columns. Each column you want to have a different color. So I'll see that will [01:03:02] the options. So you can go to the columns and you say apply all all columns having the same color but I say no I want the west with the different color and um you want the east with a different color. So you can select the [01:03:15] individual and you can assign whatever the colors you wanted but I'll say okay don't do too much of coloring at all necessarily. So be professional uh uh [01:03:28] all are professionals here but you know let the visuals also be professional uh things I think [01:03:44] is really very good one but still we have this options of changing borders layouts and all the things it's very English-l like names you want me to be further slow okay here you can tell me where I'm rushing. So if [01:04:01] again. No problem. I'll repeat it now. Hey, if you have to create a column chart, if you have to create a column chart, you need to click on the build b. [01:04:13] chart, you need to click on the build b. Okay, where is build? This is build. Okay, and there, okay, we have the different charts. We have a different charts. Since I wanted a column chart, I'll just select this one. [01:04:27] Okay, got it. Okay, then you just need to click just click on that. Just give the cursor on that and just click able to do. Okay, once you click this, you get a [01:04:44] template. You get a template which shows a collection of columns which is a collection of columns which is arranged side by side. Can see that. So you're getting a template where we can see the columns which is arranged side [01:04:59] by side. Got it? Okay. Now in this if you have to create a chart we have x axis and the yaxis. [01:05:15] Xaxis is going to be your dimension your discrete values. Y-axis is going to be the measure. See you know length length is length is defined by the measure. No, what is the total sales? What is the [01:05:28] total profit? How much quantity has been sold? Okay, based on that length will be decided. So each column will be representing a region or segment or representing a region or segment or category or something. [01:05:40] You want me to go much slower than this? Okay, you're able to get to the screen. Okay, now in the x-axis. Okay, I will take the dimension or you want to see the region wise sales [01:05:55] is it? So I select the region. When you select the region, region is charts because if you have to create a chart I said minimum requirement is we [01:06:07] should have at least one measurable value. Okay, since you don't have a chart is not created even though you have added the region. So go to the have added the region. So go to the y-axis and add a measure sales profit or [01:06:20] y-axis and add a measure sales profit or anything sales. Okay. So it shows me the all the region wise what is the sales happening [01:06:34] region and the y-axis I have added the sales. you'll not be able to cover the content as to those if you go with the speed. [01:06:51] have to um adjust to your speed and face. Okay, content voice covering will be difficult. Okay. So, if you go with this face, I'll adjust I'll adjust myself. [01:07:16] Okay. Okay. Now if you want to display the labels. Okay. if you want to display the labels. Okay. What is that we do is go to the format. [01:07:28] What is that we do is go to the format. Format. Okay. Go to format. So format. Why is I said format? Because you want to do some formatting. See by default a chart is being created which gives you the information okay lot of information [01:07:41] I'm able to get west region is the top selling south is the lot lower most east and west has a good competition but central and south are way behind and south have done a very low sales and we see that x-axis may we have already like [01:07:55] labels on your axis y-axis is8 million that is 8 lakhs and six lakhs and it is more than between 6 and half no more than half that means more than seven lakhs okay and this This is just below like you know seven lakhs okay and this [01:08:09] this is just below you can see there is a dotted line here okay when you see and representing 4.4 4 million that is four lakhs just below four lakhs yes okay [01:08:21] something but I don't want like three lakhs 91,721 or something okay if you want to display the labels if you want to display the labels in that case we [01:08:33] have the data labels you can just enable it and you want to do the formattings you can further expand this under that there are many swap options you want to change the orientations you want to change the position middle center top [01:08:46] end whatever is how you want the labels values. So color, the back color, the full color formattings. Yes, you have all those particular formatting all those particular formatting features. [01:09:01] now we will see the same chart by adding a second dimension to that. Okay. Now I want to create with two dimensions. I want to create a same U with two [01:09:13] dimensions. Okay. So now we'll see with two dimensions how we can do this. Okay. Uh let me just again I'll take the same uh Clustered is nothing but the columns are arranged side by side. Okay. So I'll [01:09:29] take the same thing here. Uh the x-axis I will take a measurable value. So sorry I will take a measurable value. So sorry dimension uh um x-axis should be a dimension. I'll take a region. Yaxis I'll take a measurable value say sales [01:09:42] or region wise how the sales are happening. So we are able to display this. Okay. We see the west region has done the highest sales and so on. Now what I want is within each region I want to see the segment wise. We have [01:09:55] different uh what to say segment of customer consumer segment the corporate segment or home office segment. So I want to divide this in the segment. Okay. So how the consumer how much um sales is done by the consumer segment in [01:10:08] the west region corporate segment in the west region home office segment and the same thing in the east region also. I want to divide it. So what I will do here is if you want to add a second dimension. Here you can see we have [01:10:20] something called as legend. Okay. Just select the legend and in the legend you can add the second dimension. What is that you wanted to add? Segment. So the segment becomes a legend here. And you can see within the west region we can [01:10:33] see each side bar is there. So this is your legends and legends are nothing but your segment. Okay. So we see that this is a consumer. Consumers have done the highest sales. Okay. And that we see that in every region we have seen the [01:10:47] consumers have done the highest sales. Okay. And the corporate has done the small amount of sales is coming from the home office segment. Okay. So we see that um the kind way the segment wise sales is not depends on the region. So [01:11:01] irrespective of the region we see that the maximum sales is happening. It's not have more of a consumer segment of customers and the south region we have more of a corporate segment of customers. No no no. So it says like you [01:11:14] that okay maximum business coming from the consumer and a kind of a average business coming from the west and a small below average business coming from the home office segment of customers. [01:11:26] Okay. He see um if I see that you know if you worked in Excel okay uh that was the reason yesterday I asked you guys like you know see and I say um Excel like you know see and I say um Excel should be the [01:11:40] basic requirement for uh I don't say for PowerBI for data analytics okay even if I go to the Python I always hey if you work with Excel your Python with data analytics becomes easy or anything okay because Excel is a place where you can [01:11:54] freely move around and do maximum things. Okay, see I just recreate this things. Okay, see I just recreate this table. No, I say inside a pivot table [01:12:08] I can have it in the new sheet itself. No, just put this. Okay, generally if you have to do this but just say as sales by region and I just have this data and I just want to insert a chart. I just say insert a chart. That's it. [01:12:22] Okay, this is what we do it. Now if you want to add the labels x-axis y-axis we have a plus sign here and I say hey I just want to add the data labels so we say data data because see by default the formatting doesn't happen here but you [01:12:34] this is it so I generally like do like this you know select this and say home and say hey I just want to convert this to millions I just go to the more options and there in the more options I just say hash um [01:12:49] just say hash um comma 0 and I just want to say this million I just say this format and say hey so it is showing us 0.5 million 68 million and so on and here by default arranging also doesn't happen we need to [01:13:01] arranging also doesn't happen we need to explicitly do this also okay uh things yes yes okay this is what it is and now again within the each region you want to add a segment so what is that we do we take a segment and then say where is a [01:13:13] segment here and act as a legends so when you add it automatically adds a segment here okay so the legends way has been displayed same thing okay So this you want to create a stack like how we [01:13:28] saw the uh snigh hills uh snigh hills machine. No. Okay. So there if I have to something called as a stack. Okay. So we have all things like you know whatever have all things like you know whatever see Excel is something like you know [01:13:41] see Excel is something like you know trust me um I do lot of data analytics with Python. I do lot of coding but I'll tell you the same things everything can be done in Excel. very easily also. Okay. So, Excel is [01:13:55] very powerful tool. Okay. So, you want to have a percentage in the stack. There is all the types of a chart. Okay. So, that is what it is going to say. That's that is what it is going to say. That's this. So, fine. Any uh [01:14:12] adding a second dimension. Okay. The same data is being divided by the legends. That is what we have added. Okay. uh all are able to get this. Okay. Hey, this is like you know when you want to have two dimensions [01:14:27] you want to have two dimensions represented. Now here what happens is like see again when you're creating a report you'll when you're creating a report you'll have multiple charts and you have to uh [01:14:40] display this particular charts in the smaller space whatever has been given. Okay. And when you see when you generally add this kind of a labels on creating a report, you'll have multiple charts, multiple cards, u filters, [01:14:53] slicers and various things. So you'll have a very small space is it? Okay. I even create we easily create the dashboards also in uh Excel you know and dashboards also in uh Excel you know and things. Okay. So we do so now when you [01:15:05] add the labels you can see there the overlapping is happening. Okay. So that is the reason I don't prefer those particular labels. Okay. Good chart is understand the data without looking at the numbers. Okay. Otherwise the tables [01:15:19] are far better than this. Okay. See table tables when you select this able to get a lot of information from this itself. Add a colors to that it becomes more informative. Okay. So that is there. Okay. Um fine. Anyhow [01:15:40] this even when you have a second dimension. So in that case I can go with a stack. What is stack? Stack is something which is arranging one above the other or side by side. Okay. So for that you know we have something called [01:15:53] as a stacked column chart. You can see the second option here stacked column staged column chart. I'll just select the staged column chart. When you select a stacked column chart I'll take the same thing here. X-axis I'll take the [01:16:07] region. Okay. The y-axis should be a measurable value. I'll take the sales. Okay. And okay fine. That's it. And um okay uh I want to stack it by the segment. I'll just say the segment. [01:16:21] You can see the same data. So is been stacked and then it has been displayed. Yeah. So you want to add the labels. Yes. The data labels. [01:16:35] have a two label uh total labels also. So that means here compared to the clustered. So this looks more neat and clean. That is one thing. we don't have too much of cluster of columns. Okay. And the second thing is we are able to [01:16:49] get both the information. We are able to see which region is doing better. Okay. So when thea in the case of this one we were not very clear whether west region is the top leader or the east region because if you have to compare this [01:17:01] should be accumulated value of all the three segments is it? So each segment has its own performance. So we cannot easily say like you know hey west is the highest or east is the highest. But here we very easily I can say that you know [01:17:15] west is the top leader then east. Okay. And you can see when I go to the labels also I'm able to show the totals as well as the individuals. Uh this one. So this is the region wise total like we see that how much each region has been [01:17:27] contributed and within the region the segment contribution and mainly like we see that the data has been staged one above the other. The d legends okay has been stacked one above the other. So this looks more neat and clean. This [01:17:40] occupies a lesser space in your plot. A lesser chart area also it uses a lesser lesser chart area also it uses a lesser chart area compared to this. Okay. Fine. region wise as well as the individual [01:17:54] region wise as well as the individual also. compared to Excel is in Excel we don't have this option. When I say corporate, [01:18:09] When I say office supplies, we don't have this feature in Excel. I like this kind of things in PowerBI. Power is much easier than Excel. So if you know Excel, PowerBI should be just a cakewalk. [01:18:29] do you call it as 100% stacked column chart. Now when I go with this one, so I'll just take the same thing here again. Um so I'll just say x-axis as again. Um so I'll just say x-axis as region yaxis I'll take the sales legends [01:18:42] taking the same thing so that we can do a comparisons okay so now we understand that almost when you look at this that almost when you look at this numbers itself okay anything okay so the [01:18:55] total sales is 73 million out of 73 million the west has contributed 36 million the west has contributed 36 million see 7 half is 35 okay so it is million see 7 half is 35 okay so it is almost very close to half 49% or 49.9% [01:19:10] or something is the business we see that no 50 see we see that 50% of the business is coming from the consumer okay but you know it is very difficult to know whether it's 50 or 45 or what it is no so you want to see in the form of [01:19:22] percentage yeah this is the one I can see okay um so when you look at this one here it shows me in the form of percentage [01:19:35] me as a percentage. So otherwise also though data labels also you can see y-axis is converted to a percentage and we see that so almost all the regions the consumers have contributed 50% business [01:19:50] okay slightly in the case of the east region or we have a slightly more than region or we have a slightly more than 50% 51.7% that is almost close to 52% but otherwise 50.3% 49.93 which is also close to 50 no [01:20:04] 49.93 which is also close to 50 no almost 50.02% 02% 50% 50%, the west region has done slightly above 50% business from the consumer segment. Okay. And we see that again uh the corporate segment 50 to 80 that is [01:20:16] around 30% business coming from this. So we see that slightly higher as in the we see that slightly higher as in the central region or yeah 31.5% coming from the central region of the corporate segment of customers. So if you want to [01:20:29] present it in the form of percentage contributions, we can take the 100% stacked column chart or stacked column chart. If you want to uh arrange one above the other, but you want to show the actual values, okay, uh clustered is [01:20:43] you know you are very specific at the legends. Okay, how much they have been contributed. Okay, say in that case you can go with a clustered column chart So if I'm not using a legend, it is just going to be a single dimension. [01:20:59] Here now the same thing we can see this is a vertical column. We do also have the bar chart. The bar chart is a horizontal one. Okay. The same feature what we do we can do it here. Uh say example we have this what is stacked bar [01:21:13] chart. Okay. So where the legends will be stacked. We have a clustered bar chart where the legends will be clustered arranged side by side. We also have a 100% stacked bar chart. bar is like you know the instead of vertical it [01:21:26] so it will be horizontal like if I just say clustered bar chart so if you just select this so it automatically changes okay or if you take a new this one [01:21:38] okay so here what happens you know what is your y-axis so this is your y-axis now your y-axis is going to be a discrete value so y-axis will be your region okay and x-axis is going to be a [01:21:51] okay and x-axis is going to be a measurable value that's legends are going to be our legends only. Okay. So say segment. Cool. Okay. [01:22:03] And the same thing like if I say tagged, it just arranges tagged. If I say 100%, it says 100%. So based on the requirement, you can select what kind of a chart you wanted. Okay. Fine. [01:22:17] Uh see um sometimes you know what happens is uh when you have this particular bar chart okay text will be more readable okay um irrespectively like kind of a things um this becomes more readable uh you can even display [01:22:32] the values here um we can see that numbers also be more readable here numbers also be more readable here totals data labels yes so we have a better visibility better readability even like labels in x-axis y [01:22:46] axis and everything and the comparison like you know top orders okay like rankings this will be your preferred one okay so one thing is I feel that it occupies a lesser plot area okay the second thing is when you have too many [01:22:59] values like subcategories states and all the things so instead of showing side by be able to written like this so if it is you see when you're writing a text like this this occupies a lesser space uh compared to writing a text like this [01:23:14] okay so based on the kind of a visib ility and a kind of a similarly when you say report is going to be a collection of multiple visuals. So how best it can be fixed based on that you can take a call but otherwise you know both does [01:23:27] almost the same job okay the purpose remains the same. So we move to the next remains the same. So we move to the next chart we can okay here what is the next chart we can okay here what is the next chart the commonly view [01:23:40] the next very commonly charts what we create in Excel also is the pie chart is it pie chart okay so what is a pie chart pie chart okay so what is a pie chart pie chart is a circle chart okay a chart [01:23:54] circle now in the circle we have a slices Okay. Uh each slice is going to be your dimension and the size of the slice, okay, will represent the measurable value. And we mainly use a pie chart. We [01:24:11] mainly use a pie chart when you want to find the individual dimension value. What is its contribution to the grand total? Generally in the form of total? Generally in the form of percentage. Okay, we use this. [01:24:26] Fine. That is where exactly we use a pie chart. So now for creating this particular pie chart. So what is that we do? I just select this pie. You can see in this particular build we have this pie pie chart. Just click the shape [01:24:41] itself. No circle and this one. So we can see we have a circle which is divided into slices of different sizes. So each slice is going to represent the individual dimension value and the size of that will represent how much [01:24:55] contribution they have done to the grand total. Okay. So now I can just take the value legends are going to be a dimension region segment or anything. So just imagine if I take a segment. So we can see here hey we have the consumer [01:25:11] segment which has contributed 50% of the business and the corporate segment business and the corporate segment around uh uh 30% 31% of the business and the home office around 18% of the business has been contribution. Okay, we [01:25:25] total sales? How much percentage it is contributed by seeing the slice itself? No, 50% more than half. Okay, 50% is half. No, it is slightly more than half. So, you can see 50.56. Yes. Okay. And this is like you know quarter [01:25:40] is 25%, you can see more than that. So around 30.74 that is almost 31% business coming from the corporate segment and the home office segment is 18% business the home office segment is 18% business coming. The values are there. [01:25:54] Got it. So I just taken the legend and the value. That's it. Legends are going to be a dimension and the values are going to be a measurable value. [01:26:07] And now the thing is in this chart again. So when you see this one uh since you know uh the legends I feel is occupying a lot of space instead of legends over here. So I want to display this legends as a value. Okay. You want [01:26:20] to customize this. So I'll just go to the format here and see uh we have this what do you say details table okay in the details label okay if you just uncheck it is going that uh and okay data labels you know things so here it [01:26:35] is called as the detail labels because it shows me the value as well as it is shows what is the percentage I don't want the value but uh sorry percentage that you know it is 50% it is more than 25% it is less than 20% and so [01:26:51] Now instead of this you know in the detail section can I show this segment name the category name so that I can remove this legend I can just close the legend but we don't know what is first you know so I can just go here into more [01:27:05] options and here we can say what is it you want to display here I want to you want to display here I want to display the category and the data value okay uh I just want to increase the font of [01:27:23] So we see that consumers have done sales of 1.16 million corporate 8.7 million or either of it category with the percentage of the totals or a data value value or only the percentage of the total I say category and the percentage [01:27:37] of the total. Yes. Um so here uh we have this. So what is Um so here uh we have this. So what is that I wanted is u say example um I just percentage of the total. Yes you can [01:27:52] change those kind of options. Okay why do I do that is um so I feel that legends are occupying lot of space because the circle charts no so you can because the circle charts no so you can see it is see if I remove the legends uh [01:28:04] I can just use this chart only in this much area. Okay so this area I can save it. So that is the main purpose like I generally prefer legend removing the legend and trying to display this and this display also what happens know we [01:28:18] this display also what happens know we have a inside outside and preferred so working with the um what you say the report so the plot area and cha proper utilization also matters okay that basis I said but otherwise as I said the [01:28:34] formatting options are all optional that's it about your Okay. Uh pie chart. What is the next chart? Okay. Here. What is the next one? Uh next thing is we'll [01:28:48] see about donut chart. What is a donut chart? Is very similar to your pie chart. You can see it is a donut shape. Okay. I [01:29:00] just click outside. So I'll try to bring the donut here. Okay. So we have a donut. This is also a circle chart. a circular chart just like your pie chart. The only the difference is the pie chart is completely opaque. [01:29:17] Whereas in the case of a donut, we have a space in the center. So that means since I have a space in the center, I can use this space I can use this space can use this space I can use this space for adding some measurable value. I can [01:29:32] use the space for adding some measurable use the space for adding some measurable values. Okay. Um this is fast. You want me to redo is mah. Okay. See Mahesh it is like this. [01:29:45] Go to the build next to the pi uh what do you say pie chart we have this what do you call it as a donut chart. Just click on the donut chart. Just click on the donut chart. You get this particular chart. [01:30:04] pie chart and the donut chart is so pie chart is completely opaque whereas the donut chart is you have a space in the center okay now when you working on this [01:30:16] center okay now when you working on this particular donut chart in the legends in the legends I will add a um same say segment I'll just do the same thing and um I say values I can take a measurable value say [01:30:30] fine the Same thing the same formatting features you want to copy like say like say I don't want to have the legends but I want to end the detail section u I just wanted to say hey category and the percentage of the total [01:30:44] you want to present it inside all those options we can just place it okay fine so we are able to do this this is your donut chart okay that's it okay the next [01:30:57] thing is uh what is the major the look and feel. So now we have a space in the center. I can use the space for adding some measurable values. What I'll do is I'll just um add a slicer [01:31:11] here. Uh sorry, I'll add a card. So what I will do is I'll not select any chart. I just click on the sales. When you click on the sales, you can see um you get a card here. Okay. Which says 2.3 million. So we can just take this [01:31:26] particular card and we can just place it inside this Uh in the card, card is mainly used for displaying the summarized value. Card is [01:31:39] mainly used for displaying the summarized value. Okay. So now uh here in this particular card, okay, we have this particular numbers Generally cards comes in the bigger font. You want to change the font of [01:31:55] this format option. So we go to the format. We have this thing called as call out value. So here I can just specify what should be the value. Say 40. Okay. Or maybe say 35 or something. Cool. [01:32:09] 35 or something. Cool. We have set this. Can you come once again? Uh lost us. Hey guys. Uh I'm adjusting to your space [01:32:22] but some kind of adjustment from your side I request kind of a thing. So yeah, I know teacher also have has to adjust. I'll adjust myself. Okay. Um I we have a [01:32:34] space in the center. We have a space in the center. Is it? the center. Is it? Okay. Uh some space in the center. So I can use the space for adding some card. For adding some card. What does that we [01:32:46] do? I can just take the sales. When you hit on the sales, you get this particular sales. By default, when you select a single measure, you get a card. Okay. Now what you do is just resize this. Uh see by default the card comes [01:33:00] with a bigger font. Okay. To display the KPIs and things but you want to format this. Just select this. Go to the callout value and just change the font. Say 35. Reduce the size of it. Then just try to [01:33:14] adjust it in the center. Drag and drop in the center of this. We are able to see okay um the consumers and things how much they have been contributed. And we values. Okay. So we are able to see both the [01:33:28] things together. So when I say consumer segment say consumer segment has contributed 1.16 million. Okay. Which is 50% of the business. Corporate segment 50% of the business. Corporate segment has contributed 31% which is nothing but [01:33:41] you know 7 lakhs business. And the home office segment say has contributed how office segment say has contributed how much? 18% which is just around 429K. [01:33:54] chart here. Cool. We can also slice it with the help of that. So we can also use a slices. Okay. So let me just take a column chart. I say hey I just want to go with the region wise sales. So what we have done before [01:34:14] contribution. Okay. So from the west region he has contributed. Okay. Okay, we see like 50% and all this particular thing and we see the west region contribution has been dark and that's all the contribution from the other than [01:34:27] the west region and we see that west region has contributed 725k right uh east region has contributed 678k south has contributed 391k central is this has contributed 391k central is this central main yes we are able to see this [01:34:46] got it okay in this card You click on the card and go to the build. In the build we have fields f i e l ds fields. You can have fields f i e l ds fields. You can see there are fields. We can see fields [01:35:03] f i e l ds. We can see that fields. There it is written as add data. There There it is written as add data. There it is written as add data. You just it is written as add data. You just click on this add data. [01:35:15] When you click on this add data, you can see there is S A L E S sales. see there is S A L E S sales. S A L E S that is called as sales, you S A L E S that is called as sales, you know. S A L E S. Just take this. [01:35:31] This is also fast. Okay. S A L E S stands for sales. Just check this. You get this. [01:35:46] Okay. Now, now, okay. See this sales is card is going to be a summarized value since we have selected the sales which is a have selected the sales which is a measurable value. So, it gives me a sum [01:36:01] sum of sales, the total sales. And mainly cards are used for displaying the KPIs. Okay. So, it's in a bigger font, but I want to fit here. Okay. You can slightly adjust the font. How do we do that? Format option. Just go to F O R M [01:36:17] A T format. The paintbrush. The icon with the f paintbrush is for formatting. This particular icon with the paintbrush is for formatting. is for formatting. Just go to the format. Okay. There in [01:36:32] Just go to the format. Okay. There in the format we have this call out value. Call out value. call out value and there you can see we [01:36:45] have this particular font size. This is the font size. Now how you set the font size in word, excel, powerpoint, any this one the same thing. So where I can reduce it I'll reduce it to 35. So according to your size of the [01:37:00] whatever the size you want just and I say reduce it to 35. You can see it is um okay. It's a good phone but at the same time not very small, not very big is it. [01:37:12] is it. Just place it in the center. That's it. there is some value displayed inside this. Is it? [01:37:28] Hey. The next thing is we will see about line chart. When do you use a line chart? We mainly use a line chart when you want [01:37:41] to present the information over a time period. Okay. Periodically how things are changing month on month, quarter on quarter, year on year sales. That is where exactly we use a line chart. Is it? Yes. So that means if you are [01:37:59] uh using uh a line chart uh it's a mandatory that you should have at least a date field. Okay. So let us go with the date fields here. So how do we go to the line chart? This is one. This is [01:38:13] your line chart. Okay. When you focus on that it says a this is a line chart and you can see the way it has been presented. Okay. This is a line chart. I just click on this. We have a line chart. Now in the line [01:38:27] We have a line chart. Now in the line chart, x-axis and y-axis. X-axis I'll take the date field. I have a order date. I have order date. I'm just taking date. I have order date. I'm just taking the date field. And the y-axis I will [01:38:39] take a measurable value. So which can be either sales, profit, quantity or anything. I'll just go with the sales. So you can see here we need where I have taken one date field and one measurable value sales. Okay. X-axis I have taken [01:38:53] the date field and the Y-axis I have taken the sales. Okay. So X-axis may have taken the date and Y-axis I have taken the measurable value. Yeah. And we [01:39:05] see that a line chart has been created. We have the data for four years. In 2018 we had a sales of 484K. Whereas in 2019 there was a slight slip in the sales which reduced from 484 to 470K. There was a decline of 14,000 [01:39:20] but from there it never looked back and there is a progress. You can see 20 we there is a progress. You can see 20 we made it to 610k and then 733k every year we can see we have a great per 30 35% of progress. Yes. But from 18 to 19 there [01:39:35] was a decline in the sales. Yeah. I can try with this one hierarchical datas category and the subcategory. The same way when you look subcategory. The same way when you look at this date field we have the year we [01:39:50] have the quarter we have the month and we do also have the days. So top of the hierarchy is the year. Okay. Even though we have the day wise sales but it is showing you the year- wise. So it is showing the top level of the hierarchy. [01:40:05] wise, monthly wise, day wise all the sales are happening. I can drill through. Okay, we saw this drill through yesterday when we did the matrix. Okay, yesterday when we did the matrix. Okay, the same way here also we can see that [01:40:19] we have this particular drill options. We saw the same options in matrix yesterday. No. Yes. So now when I just say drill this, we can see here it is showing the yearly. Okay. But when I drill to the next level, uh we can drill [01:40:33] in two ways. one is level and with the hierarchy when I say next level it will happening so this is the quarter one sales we have the sale data for four years all the four years together okay in Q1 okay every year has Q1 is it okay [01:40:47] the first quarter so it says 359K we see that the quarter 1 has the lowest sales then quarter two but three and four has done a very good sales okay this is the accumulated value when I further go to the next level it is showing the month [01:41:00] wise January we had done 94,000 This is for all the four years. All the four Januaries of all the four years. Okay. All the February of all the four years. Okay. Total sales is this much. So we [01:41:12] see that February has recorded the lowest sales and November has recorded the highest sales. Okay. Then we have a December, then we have a September, then we have a March something like that. Okay. [01:41:26] So now this is one and even you can further drill to see the day wise. We see that hey end of the month the sales have been declining but we see that it the sales are high so there are a lot of ups and downs on the day wise it is not [01:41:40] depends on the day something like you know you go to the demart the first 10 days of the over there you see it's a very difficult to get even the billing the customer crowd is huge okay so here it is not that kind of a scenario it is [01:41:53] bad it's not like end of the month or start of the month but we see that after 15 the sales have been declining kind of a Okay. Hey, now I can also drill with the hierarchy. Okay. So I can drill with the [01:42:08] hierarchy. So when I say drill with the hierarchy, it shows me each year within the year quarterly wise. Okay. 2018 Q1 sale, 2018 Q2 sales, Q3, Q4. Then again sale, 2018 Q2 sales, Q3, Q4. Then again 2019 Q1, Q2, Q3, Q4, 2020 Q1 2 3 4. [01:42:24] Okay. So in this what we understand is it is a continuous line chart. We call this as a continuous line chart. It is showing all the quarters. So even wise. So it is I have drilled with the hierarchy to get this one what did I do? [01:42:37] I drilled with the hierarchy. When I drill with the hierarchy it just goes okay from the year to year and within each year quarterly wise the data has been drilled. So we see in 2018 quarter 1 2 3 4 2019 quarter 1 quarter 2 3 and [01:42:51] four. 2019 again quarter 1 2 3 4 respectively. respectively. So here what is that we understand is we understand that every year every year the quarter 1 is recorded the lowest [01:43:05] sales every year that is a story and the quarter two is just okay slightly up better than the quarter 1 okay but not great but we see the three two to three there is a big jump every year there is we see that yeah it's from quarter 2 to [01:43:19] quarter 3 there is a jump but quarter four is recorded the highest in every year so that means the sales are more towards the end of the year quarter three and four and out of which we see that the quarter four we have a [01:43:31] tremendous high sale okay that is what we are able to understand when you see the quarterly wise now I want to see monthly wise so I further drill this so under quarter it is a month now when I drill this it goes to the month wise now [01:43:45] we can see that what is happening here January February March so we see that January February is very struggling in what about in 2019 same January February is the bottom. Okay. Every year we see that January and February is something [01:43:59] which is very struggling time. Okay. And peak time is what? September, November, December. Peak time is what? September, November and also the December. Okay. [01:44:13] Now what we understand from here is we understand the sales are very seasonal. We see that every year January, February is very struggling and September, every year October there is a ding uh like you know September is high but [01:44:27] again uh suddenly there is a fall in October and that is story in every year September and this is October okay every year. So that means we see that September might be there can be some festivals, some vacations and things. [01:44:39] Okay. And we see that November, December is lot of vacations. This is a US data. In US we see that you know lot of vacations holiday mode in the month of called as a black Friday which promotes the retail business and things. Okay. So [01:44:53] that is where we see that November December is a big month. So with the help of this one so we can have a better planning. Okay. So we need we know that January February is a tough time. So doing a marketing activities, [01:45:05] promotional will not fetch you any kind of a uh more uh results there. But we see that September onwards the sales are good. We can have a better planning. Okay. Um and better managing things and ensuring like you know we do a good [01:45:19] business during the season time when people come out and so on. We can have a better planning promotional activities and so on. Okay. This is what we are able to understand from this. So understood [01:45:35] where we can use this particular line chart is you want to see how the information is changing over a time periods. Okay. Periodically month on month how the sales are changing quarter on quarter year on year. Okay. Something [01:45:48] like this. Okay. Uh and one thing here is if you drill with the hierarchy it is going to be a continuous line chart. And if you drill with the levels it is going Accumulated Lamb chart is hey overall [01:46:00] irrespective of the all the years the quarter sales one of all the years okay so this showing the January of all the years but I say no I want to see the continuous like you know I want to see the trend okay we drill with the [01:46:12] hierarchy to see the continuous so how was in 2018 how it was in 2019 2020 and 21 we see that the spikes are more rising in 20 and 21 that means the [01:46:24] 18 and 19 was more of a lot of ups and down was a lot of struggle but we see that 20 and 21 okay uh the things the spikes are little higher here you see November December is doing good year so every months like you know so you see [01:46:38] April to August we had a very average is here it is slightly above average kind of a thing so we see the kind of a progress okay and you want to see what progress okay and you want to see what is the kind of a for um um trend here so [01:46:50] we can just go to the format and you can just see this particular what you see uh Let me go a little bit more into line chart. Okay. Let me go a bit into more [01:47:03] chart. Okay. Let me go a bit into more into the line chart. Okay. Um chart. I'll just take the same thing. Okay. X-axis I will take the order date. [01:47:18] Okay. X-axis I will take the order date. Yaxis I will take the sales value. Okay. Yaxis I will take the sales value. Okay. Now we understand that from 18 to 19 Now we understand that from 18 to 19 there was a decline but from 19 to 20 [01:47:31] there was a progress from 20 to 21 again there is a good progress. Okay. Now the question comes is I submit this report to the manager says hey sadiki you have done a good job but my boss says I want to know the reason [01:47:47] why there was a decline in the sales in 18 to 19. In 19 we had a decline in the sales. know there was a a decline in the sales. I want to know the reason. So how do you find out the reason? Okay fine. Hey now I want to know the [01:48:03] reason why there was a decline. So if I have to find out the reason for the decline. So how will you find out? So it can be something like this. In 2018 we [01:48:15] can be something like this. In 2018 we had a very good sales um uh by a particular customer. He did a very big business but unfortunately what happened business but unfortunately what happened is hey 2019 he did not turn up and he [01:48:28] did so much of business that since he did not turn up in 2019 my graph is showing decline possibilities are there okay in 2018 had a mishani who had come to my store and he did a big business in 2019 I was expecting he did not come and [01:48:43] I see that you know huge downfall in the sales I'm telling a reason okay just imagining okay or might some category of products they were doing good in 18 but 19 they did not perform good what compared to 18 or that some segment of [01:48:58] product did not perform good uh some region okay they did good in 18 but 19 did not do it so there can be various reason okay so that means we try to analyze this based on the dimension okay so what I will do here is okay I will go [01:49:16] second dimension we use a legend is it So in the legend I'll just say you can add any dimension here. So let me add a region. Okay. So let me add a region. So happens when you add a region automatically whatever the total was [01:49:31] there that gets distributed. Is it that is what exactly is happening in the case single column without the region it was showing 2.13 million. But as soon as I add a region it got distributed based on the region. Okay. And the same thing has [01:49:45] happened over here. Okay. Um before we had 484K know almost close to five lakhs. So now you can see here okay this is 1.5 lakhs this is just 1.3 lakhs and this is just around one lakh. Okay. So that means that five lakhs you know it [01:49:59] has been distributed with this. So now we see that the west region was the top leader in 2018. Okay. Then we have a east, then we have a south and then we have a central. And we see from 18 to 19 I understand that the west region has [01:50:14] shown a small decline. But the east region has shown a progress. From 18 to 19 they have done a progress. You can see in 2018 the east region had done a see in 2018 the east region had done a sales of 128k 1 lakh 28,000. From 128 it [01:50:28] has increased to 156. They have shown a progress of 28,000. But we see that this one what do you call it as the west region um they have declined from region um they have declined from 147 to 139 that is 8,000 declined by the [01:50:43] uh west region but east region has shown 28,000 increase okay so they're able to composite kind of a things that it should not be the major factor for me has shown a small decline but east has done far better then okay and we see in [01:50:59] the west region was in the top in 2018 but since it has declined but the east has done better and got got the first place. Okay, East has been performing the top in 2018. Central there is no growth in progress [01:51:12] but south we see that hey in 2018 south region did a sales of 1 lakh 4,000 1 lakh 3,850 so that is almost close to 1 lakh 4,000 so that is almost close to 1 lakh 4,000 okay 104k from 104k it has been reduced [01:51:26] okay 104k from 104k it has been reduced to 71k so that means 33,000 there is a decline in the sales by the south region okay so when you see the the west region from 147 it got declined to 139 line [01:51:40] 8,000 decline but you see the south region the way the decline has happened from 104k it has been reduced to 71k 33,000 a decline so I can say 28 we are able to see the decline because south performed very bad okay so [01:51:57] 18 may did did more than one lakh but in 2019 they could do only 71,000 so that is the main reason for a decline of the sales in 2018 agree with me. But [01:52:11] one thing I see that after that decline, hey, it is not going completely decline kind of a thing. It is trying to come up but is not able to achieve. So it is the south region. But we can see the east and west they are having a [01:52:24] competition. But you can see the west region uh it got um a small decline in region uh it got um a small decline in 18 to 19 but from 19 to 20 it has again got back the first place. You can see the way it has been progressed. Okay. So [01:52:37] west region is performing very good year on year but still it has overtaken with a small margin and 2019 we see that it has been okay big uh um it has been [01:52:49] retained in the position one with a big margin. Okay so that means east and west they are in the competition and they are really doing good. Central region yeah one year it has done good but you know two years it has been a flat sale but we [01:53:01] see that you know um the south region once it was been declined it has tried to move up. Okay. But um the way the other regions have been performing so we see that it is in the very bottom of the table. See um you know when you talk [01:53:15] about a data analytics so the first thing is we always ask what what what then we go to why y why y why why okay what do you mean by this? So first thing is when you look at the data so your first question should be hey what is the [01:53:29] total sales what is the total profit? How many customers are there? Uh how many different states are there? So this is like more of what. Okay. Then we say is like more of what. Okay. Then we say why. Okay. Why sales went down? Okay. Um [01:53:43] which year where it went down. Okay. So we ask those kind of a questions. Okay. So this is mainly of your why. Why why we are trying to go for it. Okay. Why looking at why so we can analyze this [01:53:56] based on any dimension. Region wise, customer wise, product wise, statewise, city wise, any this one. Okay. But I'll tell you um there are also some kind of states we have 50 states. Okay. So when I go with this this will be very jumbled [01:54:09] we'll not be able to understand. So in this kind of a situation to handle okay charts. So I generally prefer for this kind of a thing. So scatter plot will be more better to analyze. Okay which we'll be seeing later. Okay. So see again um [01:54:24] here but at the same time they really good um in understanding the data. So dimension with too many values. I'll not recommend you to go for a line chart. [01:54:36] The same thing holds good in the case of a pie chart. Also don't use a pie chart when you have a dimension with many values. The recommend is 7 to 8 max. If it is more than eight values. So I'll say don't go with this particular pie [01:54:50] chart means okay. So instead of segment if I just take a state what will happen? See this software is not telling that you know I'll not create a chart. It is creating it's accurate but we are not able to understand anything from this. [01:55:05] Okay it looks like you know what is that called a wheel called as okay uh thing. So we are not able to understand things that doesn't get you the um the actual objective of this. So there is some kind of a limitation but that I can discuss [01:55:18] it once we learn all types of charts when to use what kind of a chart. Now if I talk about all those particular charts which we have not even covered so that bit patience and we'll learn things first and then we'll try to have as as [01:55:33] first and then we'll try to have as as analytics point of view. Okay. So but at clear understanding of what is dimension and measures. I always talk about dimension and measure the legend should be always a dimension. Okay. And the [01:55:46] y-axis is always a measurable value here. Okay. Now okay. So this is fine. Now I want to further drill on this and try to find out what is going wrong with [01:55:58] the south region. Okay. Now I say take an example um SNHA I'll make her as a manager for south region. I have lot of hope on her that you know she'll bring back the south region sales. So south region is struggling a lot. Okay. I have [01:56:14] trust in Nha. Okay. uh Sneha is it sorry not Na Sneha but uh that you know she know she has to figure out why or it's not only her is it everyone can figure it out here uh why things are going wrong or how we can correct this south [01:56:30] region okay let me just drill further here so I'm just going one step further I'm seeing the quarterly wise now the next thing is we'll see about area chart [01:56:42] what is area chart area chart is nothing not a line chart but is they call it as Google you know what is area chart it says it's a line chart with the fill color okay uh that is what I get the answer from the Google I think uh yeah [01:56:55] let me just take the area chart we have something called as a stacked area chart okay so now the same thing I'll just take x-axis I'll take the order date take x-axis I'll take the order date yaxis I will take it as the sales [01:57:08] yaxis I will take it as the sales okay and we see that oh from 18 to 19 there was a decline but 19 to 20 they have done and 21 they have really very done very good. So when you see the line chart and this one no difference both [01:57:21] the same it is just the field one. Okay. And when you drill also, we can see the Drill to the next levels, it shows. Yes. Okay. So, if it is a line chart, it is like this. Okay. Area chart is like this. Area chart is like this. Okay. The [01:57:36] this. Area chart is like this. Okay. The major different comes is when you add a dimension, when you add a legend. Okay. Um, let me add a legend. Let me add the itself. Now, when you add a region, what is [01:57:50] happening here? It is stacking. it is stacking. Okay. Whereas in the case of the uh line chart what was happening is when you added this particular the what [01:58:02] is say the legend it was showing as a competition running race. Okay. Who is standing top where it is and so on. But here so what is happening it has been stacked. Okay that is a major difference. [01:58:15] So when I say it has been staged so that means okay uh here we are able to see the overall sales as well as the individual contribution. So overall we see that yeah there is a small decline. Okay overall we see that there is a [01:58:31] small decline. Okay small decline is there here. Yes there is a small decline here. Yes I agree. But we see that the region the way it started and the way it has been here in this. Okay. So it has [01:58:47] there was a decline even when the west region there was a decline in the west region but the very small but we see the major decline is from the south region. But we can see this east region has performed very good. The central is [01:59:01] again the same. No change, no up and no down. But we see that yeah we have very down. But we see that yeah we have very good uh sales from 18 to 19 by the west region. But other three regions they have not shown any progress but majorly [01:59:13] we see that you know the south region has a kind of compared to the previous year they have done much lower sales. Okay things. And when I further drill this to the quarterly we are able to understand more clear here. We can see [01:59:27] it started with a very big note but we can see the sales are same irrespective of whatever the quarter it is. Okay. But we see that during the quarter 1 all are doing doing very low but quarter two and quarter three they have a big mountains [01:59:41] of business which we don't see in the south region. Okay. So that means south region is not having that kind of a planning. Okay. So when there is a offse when there is a festival season when there is they are not able to understand [01:59:55] have a proper planning and doing. So that is what I feel that is missing in the south region. The same information we are able to extract here but in a um we are able to extract here but in a um what do you say um staged manner. Yeah [02:00:09] what do you say um staged manner. Yeah this is what is your area chart and the this is what is your area chart and the um what do you say the line chart. Fine. Hey um we have something to learn with the slicers. We have a lot more [02:00:21] of which we can try to understand you know why things can went wrong. What had given the lower contribution. We have something also waterfall charts. Amazing. Apart from that we do also have lot of AI tools also with the help of [02:00:36] which it becomes easy for us to analyze it. Okay. You don't have to really know like you know all this x-axis yaxis this and all these things. we can do it but I suggest as a learning stage we will not depend on AI but AI tools are there so [02:00:50] sound guys you know we can do a lot of this kind of a charts even you know dashboards can be created within 5 minutes okay we when I take the AI tools but as of now so I don't want to touch on the AI as of now we'll do it in the [02:01:05] on the AI as of now we'll do it in the later sessions okay but um so uh let us concentrate on the learning part okay yeah any questions with whatever we have learned tonight. So we just started with the charts is a visual [02:01:18] representation of the data. The same data represents in the visual way. Okay. We have a column chart is mainly used for comparing. Okay. Region wise comparing or sometimes okay and then we do also have u cluster is when you have [02:01:32] a second dimension okay within the each region segment wise what they have been performing the same instead of having a cluster is too many columns and all the things. So you want to have this the legends to be stacked one above the [02:01:44] other. We can have a stacked or column chart. And if you want the legends to be arranged side by side, you can have a uh what is say stacked bar chart. We can the form of percentage, we can use a percentage bar chart. You don't have to [02:01:58] write any kind of a DAX calculations or anything to say how to calculate the does a calculation of the percentage and the chart gets created. That's the beauty of this using this built-in charts. Okay. Okay. And then so now we [02:02:14] understood about the line area chart and the donut chart. They both are same. So that means when you have any kind of a um what do you say dimensions I want to grand total. So how much west has been contributed east region how much they [02:02:28] see that. And when you say the donut chart is again the same. Okay. Only thing is so pie chart is completely opaque. In the case of donut chart we have a space in the center. that space I can use it for adding any kind of a [02:02:41] measurable values okay thanks that's it and so automatically it shows you that particular values interactions I don't have to do any kind of a calculations or DAX calculations or any this one no we we can easily do all those stuffs okay [02:02:55] so that is the beauty of this okay thanks then we saw what the line chart is mainly used when you want to present the information over a time period so we can drill this particular for to see the cumul calculated value. So when I go [02:03:09] shows me the accumulated value. Cumulated is what is the total sales in each quarter or what is the total sales in each month irrespective of the year. Okay. Things. Okay. But I say no, I want to see the trend analysis kind of a [02:03:23] this year to the next year I want to do it. So we can go with the drill with the hierarchy. We can see hey last year each month wise and this year and next year and next year we are able to see how things were happening. So that is what [02:03:38] is the mainly things what we get here. Fine all clear. Uh in the last class we started with the charts. Uh where we looked into first thing initially we saw with how do we work with the tables. [02:03:53] Then we saw with the matrix. Okay. Uh what is the difference between the tables and the matrix. Uh in the case of matrix we can present the data in two dimension rows and columns. And not only that we can also have a hierarchical [02:04:07] datas category within the category subcategories where we can get the information in multiple dimensions row wise column wise subcategory wise hierarchical datas we can easily drill through to the next level of the [02:04:19] hierarchy or we can drill with the hierarchies all those things are there. Apart from that um we do also have studied with those particular concept studied with those particular concept of uh what do you call it as [02:04:37] have seen how do we apply the conditional formatting. Uh to please would you please zoom the link over mail? mail? Better to join from there [02:05:03] okay. Um fine apart from that you know so we have studied with this particular concept of creating the charts. Okay. Um the charts are nothing but you know pictorial representation of the data. We [02:05:16] cluster. So when you add a second dimension uh the dimension will be presented side by side and also we saw the stack. Stack is nothing but where the other. Okay. at the second dimension the legends and we also saw with the [02:05:31] 100% stack to column chart and also the bar charts and we also studied with the concept of the pie chart and the donut chart. Uh we did study about u the line [02:05:43] chart is it? Uh pie chart is mainly used when you want to present the information the individual value. Okay. What is a contribution to the grand total? So form of a percentage. We use a pie charts is a circle charts where um the [02:05:59] data will be divided into slices. The dimensions will be values will be divided in the slices based on the measurable values it has. And the donut chart is very similar to your pie chart. Only thing is in the case of a pie [02:06:11] chart, it's completely opaque uh donut chart, we have a space in the center. adding some additional measurable values. And then we studied with the line chart and the area chart. Correct? [02:06:25] Uh this is what we have done in the last class. Am I right? We did with the line chart and the area chart. Is it? Yes. Okay. A line chart and the area chart. That is what we studied. Hey, fine. So, we'll continue with the charts today. [02:06:39] Okay. Um so fine we'll move ahead. Uh what is this ribbon? Uh see it is something like again um like a column chart. Okay. We have a collection of columns. Okay. And obviously there is a length length is [02:06:54] going to have a measurable value and here it says the joining point. So here like you know how the things have been changing from one period of time to another. Okay. So what I will do is in the ribbon chart um so one thing is it's [02:07:07] called as a ribbon because the chart looks like a ribbon. Okay. So what we we'll take a x-axis. So it is going to be a discrete I'll take um what do you say the order date. Okay. So I want to see periodically how things are changing [02:07:22] and the y-axis I'll take a measurable value. I'll take a sales. So we see that okay how things have been changing from year to year. Okay year to year how things have been changing. So now um [02:07:43] decline in the sales but 20 and 21 they have done a very good sales. Okay that is what we are able to understand from this one. Now uh again uh in the line chart we saw like you know from one period of time to other period of time [02:07:58] when things were going bad. So we found out like you know why things went wrong. uh we understood like south region has performed a very underperformed. Okay. So that means what we did we added a legend to find out like you know why [02:08:10] things went wrong from one period of time to other period of time. Okay. The same being uh here also you want to find why things went wrong one period to the other period of time. So in that case uh we'll just go with this particular thing [02:08:23] called as a legend. Okay. I just go to the legend and in the legend I'll just take the region. when you select this particular region here. So you can see um we have this each column that is periodically 18 19 20 and 21 how the [02:08:38] sales are happening and this particular overall it just says like you know how overall it just says like you know how much sales was done in 18 okay how much it did in 19 okay uh 20 how much it did totally and 21 may how much what was the [02:08:51] sales so we see that 21 it has done more than eight lakhs okay in a single year so now um okay whatever is there now at At the same time we have a legend here. So we see that 2018 May we have the west region which is in the top the east [02:09:07] we have a south region and then we have a central region. Uh this is the sales what has happened in the each year. We see that in 2018 we had the west region was in the leading then east then south and then the central but from 18 to 19 [02:09:22] we see that you know the sales was been declined and we see that west region what has happened is whatever the sales was being done in the west region and we can see from 18 to 19 there was a decline a small decline so we see the [02:09:35] thickness here of the ribbon is almost the same but um generally like you know slightly small when you see the actual values here it is 147K and here it is done as 140K that is 7 to8,000 there was [02:09:50] a decline okay but we see that uh the west region or sorry east region so they had done a sales of 128 in 2018 from 2018 to 19 they have increased the sales [02:10:03] from 128 they've gone to 156 they have shown a progress okay and we see that shown a progress okay and we see that the overall in 201819 the west region stand Sorry, East region is standing in the top. Okay, the east region is in the [02:10:17] top of the table, the first rank. Okay, so in the 2018 uh west region was in the top, east was in the second and in 2019 it is reversed. Now here east is in the top. They have done the higher sales and [02:10:31] compared to last year they have done a very good sales and west region slightly they have shown a decline and overall the result is um east region is standing in the top of the table. Okay. And when you see the south region, okay, it was [02:10:46] slipped down to the fourth place. And you can see the thickness of the ribbon has been declined here. The thickness has reduced. So that means I can say that okay um this was something like you know by visible thing is we see that the [02:11:00] thickness has been reduced here. Okay. But you know you want to know the actual intersecting point here joining okay when it is slipping. So when you see this particular okay when you just keep the coin pointers in the intersection so [02:11:14] it tells you last year how much it was the sales and this year how much it was the sales in 2018 it has done 1 lakh 4,000 and here it has done only 71,000 so that means there was a decline of 33,000 okay 32,485 [02:11:27] this was a total decline from the previous 1 year to the next year from 18 to 19 so which is nothing but 31.3% decline minus okay and it was in the rank which is nothing but decrease in rank one rank. So that is what is the [02:11:44] kind of understanding that we have. What is the name of the chart is called as a ribbon chart. Okay. So here what exactly happens is it is more like a line chart. See like you know if I go with the line chart if I [02:11:56] chart. Yeah it is creating the same thing as a line chart. But you know we there was a declined. What is the actual of a rank which has been declined. See we have three four values it's fine but [02:12:08] when you have many values so uh one you know product would have been gone from the third rank to the 10th rank so there was a se 7 eight ranks it has been declined so we will not know about that but here we can easily come to know with [02:12:21] the help of the ribbon chart okay so here you are getting both the information one is like you know what were the total sales done okay what was the total sales done we are able to see so when you see the total year so we see [02:12:33] that you know there is completely there is a small decline Okay. And each one how much how the ranks has been changed. We are able to see that. And not only that, apart from that we are able to get this kind of a descriptive information. [02:12:46] you see this also like last year and this year how the rank has been changing. So it is one rank. So uh things we are able to see all those particular descriptive information like you know [02:12:59] last year what it was this year what it is. So what is the kind of a growth? How What is the change in the rank? Okay. Where it has gone down or same or above or what it is we are able to get this kind of a descriptive information. It [02:13:13] just gives you uh so we are getting multiple information. One is overall how much they have been done for each period of time. Okay. The second thing is like you know um what the legend what you have okay the region. So which region is [02:13:26] standing in the top second and third we are able to see the ranking. The third other period of time how the rank has see that you know there is 18 to 19 where there is a decline. So we see that [02:13:39] decline. From 19 to 20 they have shown a growth but what has contributed the max to the growth we are able to understand that also from you can see so the the west region which was in the second place it got to the first place but how [02:13:53] much it has contributed when you see the statistical information it gives you the descriptive thing. So telling like you know okay in 19 we had 139K from there it has gone to 189K which is almost 48,000 increase in the sales which is [02:14:07] like around 34% increase in the sales happened from the previous year. So which has gained the position by increasing of one rank from the second rank it has gone to the first rank and we see that maximum contribution is [02:14:19] coming from this. Okay that is what we are able to understand. Um we saw this ribbon chart. is one which looks in the form of a ribbon. So in this what did I do here is x-axis may have taken the date field or [02:14:34] y-axis may have taken the sales and the legend I have taken the region since I have taken the x-axis as a date it shows me each periodically okay so it has been divided periodically okay year wise okay and y-axis I have taken the sales it [02:14:48] tells me like you know what is the total sales which is done in each period of time okay and the legend says like you know okay it is showing in the form of a ribbons the legends which says each period being which region is standing in [02:15:00] the top. Okay. uh how the region has been changing from one period to another period and also gives you a statist descriptive information like you know okay what was in the previous um uh period and the next period what it is [02:15:13] okay how much of change has happened okay whether is a positive or a negative those particular descriptive information has been shown so we use a column chart when you're comparing we use a pie chart is when you want to see the individual [02:15:27] contribution to the grand total we use the same chart pie Donut chart again to grand total. But apart from that you want to add an additional measurable present it. Yes, this is a donut chart. We use a line chart is when you want to [02:15:42] present the information over a time periods like you know trend analysis. try to find out like you know why things went wrong. Okay. Um and the ribbon chart also is the same thing. So we are able to see like you know from one [02:15:56] period of time to another period of times how they have been performing and periodically how things are happening and the measurable value tells you like contributed and the legend tells you like you know okay uh which was in the [02:16:10] which has been change um which is the leading one okay how the rank has been changing okay what is the value has been descriptive information compared to your line chart so I think this I've in [02:16:23] repeatedly telling it. Okay. Um so I said like you know line chart also does kind of a descriptive information. So we the top then went to the second place and then kind of a thing. But in the [02:16:36] case of a line chart you don't get a total summary value. Okay we don't get that descriptive values. Okay. So but in the case of a area chart we are able to see the what individual period of time how much they have done. That is one [02:16:49] information. Second thing is we are able to see the ranking and we are also able changing and then the complete descriptives which you don't see it in the case of a line chart. Okay. I think last class also I told this [02:17:02] whatever we have covered till then. Okay. Because we have lot more different Okay. Because we have lot more different types of charts. Okay. Thanks. Okay. This is what is the main thing we the next thing is we'll see with the [02:17:15] waterfall chart. Okay. Now what is an waterfall chart? So now when I talk it looks like a waterfall. So I'll just get into the focus mode. [02:17:27] Now again in this we have a category y-axis and the breakdown category I'll take the date I'll just take the order date okay and um so y-axis I'll take a measurable value which is going to be the sales [02:17:42] so in this case in the case of waterfall you can see it looks like a waterfall okay where we see that we have this blue color bar which is telling the total color bar which is telling the total sales where we have a totals of 22 lakhs [02:17:55] or 23 lakhs total sales has been that is 2.3 million and we see that okay totally like you know 2.3 million sales has been done and here green color bars what green color bars which is been like you know uh it is starting from the previous [02:18:10] showing what is the total what they have done so which is nothing but the uh total sales done at the individual period in 2018 we had a total sales of 4 lakhs 84,0004 48 million kind of a things in 2019 it [02:18:26] slipped down to 47 million there was a decline of 14,000 sales. Okay. And 20 we see like you know there was okay growth from 19 to 20 we see that from 4 lakh 70,000 it has gone to 6 lakhs 10,000. Okay. There has been progress. You can [02:18:42] also shows me like you know they have shown a progress. Okay. So we are getting informations like what is the total is a blue color but this is the individual. So we see that less than 0.5 [02:18:55] million. So at the 2019 we could not even achieve 1 million. So it is less than 1 million. Okay.9 million something. But you can see from 2020 that is in third year we exceeded more than 1.5 million. And when you see in [02:19:10] 2011 we exceeded almost 2 3 million kind of thing more than 2 million. Okay much ahead from the 2 million. Okay that is what is the total what we have done. Okay now we what is that we are able to see? We are able to see one thing is [02:19:24] what is the overall total and the individual um periodically how much they have been contributed and it is showing us from where it stopped from there okay the next contribution what is that there so with the help of which we can see the [02:19:36] contributed as well as at any period of time how much we achieved so that means time how much we achieved so that means by the end of 2020 by the end of 2020 we see that we have achieved almost okay [02:19:50] how much uh 1.6 6 million something. Okay. So you want to show the values. I but you want to show the values. You can see the values here. Okay. So which just have been contributed from each period of time. In this particular access when [02:20:06] you compare it tells me like you know so each period of time how much they have been contributed. Okay fine we are able to see this. Hey Okay fine we are able to see this. Hey now where do you use this one else? [02:20:18] Now when you see from 18 we had 4.48 48 million and in 19 it has been declined to 47 million. I want to know the reason for the decline. Okay. Again not only that from 19 to 20 we can see in 19 we just did only 47 million. In 20 we have [02:20:33] done 6 million. So which has done almost um.13 million higher sales. Okay. 19 to 20. So I want to again know like you know what led to that kind of a contribute which has contributed the highest to achieve that particular good [02:20:48] margins. Okay. So I want to understand that. So for that you know what we have is we have something called as breakdown. We have something called as breakdown. In the breakdown we say hey I just want to understand this might be [02:21:01] I'll just go with the region. Any dimension we can use it. When you take the region yeah we can see in 2018 we did a 484k 19 it reduced to 471k. And when you see okay um the reason for the decline when you see here the reason for [02:21:17] the decline uh when you see the region reason for the decline is we see that 18 to 19 um we have taken the breakdown. So here you can see a breakdown what I've done I've taken the region I've taken the region as a breakdown. Since I've [02:21:31] taken the region as a breakdown it says from 18 to 19 there was a decline and we see that east region has contributed very good. Okay. So when you see here but the lowest contribution is coming from the south region and what is the [02:21:46] negative big negative. So it has contributed minus 32,000 compared to the previous year what it has done. So there was a decline of 32%. There was a decline of 32%. So last year it did 1 lakh 4,000 this time it is only 71,000. [02:22:02] Got it? Okay. So here the breakdown is telling decline so only the east region has contributed um better than the last year but apart from that all the other three [02:22:16] regions have shown a negative okay central has shown a negative 1,000 lesser than the previous year west has done - 8,000 lesser than the previous year but south has done minus 32,000 so I can say from 18 to 19 the decline was [02:22:31] what has happened so major thing was from the south region So South has contributed the lowest okay from this period of time to this period of time and because of which we see there is a big downfall okay and even during this [02:22:45] particular downfall time you can see east has good support here. So in 2018 east has good support here. So in 2018 it did 128 but in 2019 it has done 156k which is 27,600 that is almost 28,000 higher sales which [02:22:58] is almost 22% growth progressive sales compared to the previous year. Got it? Okay. And we can see from 19 to 20, okay, there was a big growth. And we can see in this growth all the region has [02:23:12] contributed positively. All the see green color shows positive, red color shows negative. All the regions have posit contributed positively. But again, which has contributed the maximum rest. So one thing is the way it has been [02:23:24] arranged the the what do you say the breakdown whichever the dimension value which has contributed the max is on your left hand side and which has contributed the lowest is on the right hand side okay so we see that from 19 to 20 we [02:23:38] have a very good pro growth you know from 470 it has gone to 610k that means 1 lakh 40,000 growth is there so now in that 1 lakh 40,000 growth west has shown a growth of 50,000 okay 45,000 growth from the central region they both West [02:23:52] and Central they have shown a very good progress. Uh East has done again 25,000 progress. South also has done a cont contribution 22 better progress than the previous year. So all three have four have shown a positive uh thing a very [02:24:05] good growth progress but maximum is coming from the west region 48,000 alone in a single period of time. Okay. From 19 from 20 to 21 again it has shown a big progress. You can see from 610K it has gone to 733K that is one [02:24:21] lakh 24,000 extra business they have done but during this period of time the west has contributed 63,000 extra you are getting the descriptive information also we are getting the [02:24:34] breakdown also we are able to find out where exactly things went wrong in the different colors makes it more easy to understand and when you see if all are we can come to know which has contributed the max for it growth from [02:24:49] 20 to 21 so I can see the almost you know 1 lakh 24,000 growth is there so all have shown a positive but you can see the west has shown one west alone see the west has shown one west alone has shown 63,000 extra growth [02:25:02] okay this is what is we get it from the waterfall chart understood breakdown breakdown is the reason when there is a decline why why it went wrong [02:25:17] to see which has contributed the most which has contributed even during the shown the negative so that it'll show in the negative value so the maximum contributor on the left hand side the lowest contributor on the left right [02:25:31] hand side so I don't have to see the width of this particular bar by okay by easily judge me so as I said no good chart is not the chart which has the able to judge so on that basis I say [02:25:44] left has contributed to the tax and the right has contributed the lowest and color is the negative contribution compared to the previous period and the compared to the previous period and the green is the positive contribution [02:26:01] hey you don't have to do anything see you have a chart like this you want I can show it from the very beginning okay I have taken the waterfall chart if that is also not there so it is like this no from this particular um this So we can [02:26:16] take the waterfall chart. The very first thing I'll get into the focus mode the I can take the category I'll just take the order date in the category and the measurable value sales profit or anything. So here first thing it shows [02:26:33] me what is the blue color bar tells me what is the total and the green color bar it shows me what is the um contribution from the individual period contribution from the individual period of time. Okay. And here you can see um [02:26:45] um the next period it is starting from where it has been stopped. So with the the contribution happening from the each period of time. We are able to see that overall how much we achieved at the end [02:26:58] of that period of time. Okay. Now uh I want to understand what went wrong or what has contributed the max for the growth or for the decline. So for that we have the breakdown. So in the breakdown I'll just take any dimension. [02:27:14] You can take it. I'll just take a region. In the breakdown in the breakdown I can take any dimension. I'm just taking the region here. So I'm just taking the region here. So I'm trying to see the region wise. [02:27:27] Yeah. Understood about this u waterfall chart. like I just say region or instead of region you say category we see that you [02:27:42] know 18 to 19 furniture has shown the positive but we see that technology and negative. See the all this particular three this one looks the same size. So red color says is a negative green color shows the positive and second thing is [02:27:56] like you know if it is on the left hand side it is what you say big value on the right hand side is a small value. So when you see it's a negative means what see maximum loss is happening from the office supplies. Okay. So when you focus [02:28:08] there it says so we have 10% decline that is - 15,000. So this is - 12,000. Okay. But furniture have shown a progress of 13,000 extra. Okay. [02:28:21] So 19 to 20 we have shown a progress but we see the technology sales have increased. From 19 to 20 we see the technology sales have been increased by technology sales have been increased by 40%. Okay. Whereas the other sales like [02:28:33] you know 34%, and this is so there is a complete growth from 19 to 20 but technology and from 20 to 21 again there's a big growth and all the categories have been shown a progress but we can see office supplies but I say [02:28:47] subcategory yes you can do that way also. also. Yes. So we see that 19 to 20 in the decline time. So we see that you know uh book cases have contributed the maximum. [02:29:01] Okay, there are many subcategories but we can see the the the maximum uh damage is because of the machines as the product buys. So machines have shown a decline of 55%. Okay. So in 2018 machines had a sales of [02:29:17] Okay. So in 2018 machines had a sales of 62,000. Okay. But in 2019 they have done only 27,000 not even half of it. Half of 62,000 is 31,000. Not even half of it. Okay. That's the reason it says minus 55,000. 55% minus 35,000 decline is it [02:29:35] what went wrong? No, that is what we are trying to find out. So you can use any this one but I can tell you um if you have a dimension with [02:29:47] too many values I don't think so this is a recommended one. So this is good like you know when you have something like you know six seven um uh values so in that case this will be good. It's the same thing as your pie chart also. When [02:29:59] you have a dimension with many values, pie chart will not be useful to sales. If you take a pie chart, we have 50 states, you get a small small slices. have a pizza and one pizza is there and we have 50 people and 50 people are [02:30:15] there and you are ordering a pizza for the party. a single one single pizza and for 50 people if you divide the slices you know some people will get onion some people will get cheese some people will okay yeah so that means they'll not get [02:30:28] the actual taste is it okay you will not know which is big small and things it becomes difficult to judge so don't use the pie charts again when you have a dimension with too many values 6 7 8 max is good so more than that you [02:30:42] know it'll not see it'll create but it is not going to be information the same thing this this one also stay again in this particular chart. No. stay again in this particular chart. No. Um you can so I just copy. [02:31:19] take even the subcategory here the ribbon so you see this particular product the way it has been going. Okay. So how much they have done in 2019 and how much they have done in 2018. Okay. So the it was standing in the [02:31:35] third place but it shift to almost 78th place. Okay. Four five lines down. So we things went wrong. If you see product wise, okay, this product is something which is underperformed from 18 to 19. Okay, we are able to see it has slipped [02:31:51] by five runs down. Okay, so we can see um this is again nothing but missions. Okay, so we see that from the line chart we are able to clearly see that the missions the way it has been gone. So in 2018 it has done the third highest sales [02:32:04] but in 2019 it is the eighth highest sales. So in 2018 it has done a sales of sales. So in 2018 it has done a sales of 62,000 but 919 it just did only 27,000. So which is minus 35,000 decline which is 55% decline in the sales. Okay. See [02:32:19] sometimes what happens you know when you have too many things. So this particular analyze it. So in that case the other chart will be a better one. Okay. Based on the number of values also you'll be taking a call which can be easy for you [02:32:33] to explain. So again at the end of the day like you know you as a data analyst you're going to explain to your managers in such a way that they should be easily able to take a decisions. Okay. So which one you feel is better to explain that [02:32:47] thing with the help of the rimman chart and the same thing with the help of the um what do you call it as um the waterfall chart. But in the waterfall chart I feel hey it is clearly telling that you know the maximum okay so I I [02:33:02] can say the one of the worst contribution is coming from 18 to 19 is the mission by seeing this you know the the the contribution any what do you say have given highest contribution on the left and the lowest contribution on the [02:33:16] means is the negative contribution. So we understand that so the maximum negative contribution is coming from the missions. Okay. So even in the uh what do you say ribbon chart we are able to understand the same thing but you feel [02:33:32] have slipped by eight ranks we are getting lot of descriptive information here okay you feel this one is good to explain you can go ahead with this or I it depends like you know depends what kind of a manager you have so what he [02:33:47] understands it better that also can be again a call so we can take a call like you know what you want to use it which chart you want to use it Okay, I too many values like you you can go to don't use the both of this. So we can [02:34:00] use something called as the scatter plot. Okay. Now what I mean to say is see instead of categories if I just go with the state wise um state we have almost 50 states. So in this [02:34:14] is getting confused. We're not able to make any sense like you know okay what it is. Okay. uh rather in this also like say example if I go with the states instead of subcategory because we have [02:34:27] too many states no 50 states kind of a thing so you can see it'll see the charts will be created okay power will not say like I know I'll not create a chart but we see that you know what is this one this is what California so [02:34:41] California is in every year it is in the top of the table okay and then we see in the table okay which is very closely competing to that but you can see there So we see that you know it has been changing but you know we have almost 50 [02:34:57] states okay so I don't think so this makes it um this is very rang um jumbled up no it is completely messed up okay I don't think so this is a better way to explain okay so that is where we take a call okay when to use what kind of a [02:35:12] charts so when you have too many values also okay so some charts will not see powerbi will create the charts but it will not be that very easy to explain or that very easy to extract the insights of it. Uh understood with the ribbon [02:35:27] of it. Uh understood with the ribbon charts and the waterfall charts. See every chart is good. Okay. At the end of the day, you as a data analyst, you like you know when to use what kind of a charts. Okay. I'll also tell you some [02:35:40] you something. At the end of the day, you are a human being. So you also have to take a call like you know uh better understanding say and things. Okay. As I said like you know it's just like you know how you are going to explain to the [02:35:53] decision makers or if you are your own decision makers but still so how will you understand like you know where if I invest it is going to get be a better okay which category is doing better which category is not doing good where [02:36:06] need to do a corrective measures so we need to all do all those particular things okay how your better way you can explain it or how you can understand it better you can take the respective charts okay now I I think one more very [02:36:19] um if you feel that waterfall chart is better so we have something much better than that but uh it is a little tricky like you know more things are there so we need more contrition concentration so we'll go with the next chart hey before [02:36:32] I think I go to the next chart see this is the charts mainly try to find out like you know why things went wrong okay first thing is we saw what what what total sales what is the total sales for each region wise when you talk about [02:36:46] What happened? What happened? What happened? Now we are telling is why why why things went wrong? Okay, that is know we are doing trying to do like you know why the what is the corrective [02:36:58] measures that can be done. Okay so when you go to a doctor you know first thing he'll ask you what happened then second he'll ask you why it happened. Okay. diagnostics like you know testings and x-rays or whatever it is like you know [02:37:12] from there he'll try to understand more detailed information and then he takes a uh what is a kind of a um prescriptions kind of things. So which says like you this is what it is going to be there. This is how it has to be cured and [02:37:25] So here also you're doing the same thing. So till last two sessions we're doing just like you know what happened what are the total sales what are the wise category wise subcategory wise we try to analyze it and then we saw like [02:37:39] chart the donut charts or the line charts and also the you talk about a mainly talking about what happened why how much it happened and things here mainly we talking about hey why things went wrong okay what was the reason we [02:37:54] are trying to find out this figure out those kind of things Okay, I said like you know before I go to this waterfall chart which is going to be more detailed particular thing. So I just wanted to tell you about this combo chart. Okay, [02:38:08] combo because it's a combination of column and the um line chart. Okay. So this is mainly used to get the information about the sales two measures okay for the same dimension. So we have a column and the line okay I'll just [02:38:24] select this. This is called as clustered column line and the clustered column chart. So it is a combination of line chart and the column chart. I'll just take this. Okay. So now in this again so x-axis you can take any dimension. Okay. [02:38:38] So I if it comes to a dimension I'll take the subcategory because it has not too many values not too very less values. And when it comes to y-axis so x-axis is the dimension. But when it comes to y-axis, when it comes to [02:38:52] comes to y-axis, when it comes to y-axis, we have two things here. We have the column y-axis and the line y-axis. Okay. So, when it comes to the column is what is the data you want to present as a columns. When it comes to [02:39:06] what you say the line, it says what data you want to follow as a line. Okay, that is what it is. Hey, columns pay I just want to have the sales. Okay. And the line I want to have the profit. Okay. Both are y-axis is both a [02:39:22] measurable value. One I have taken a sales and other one I'm taking as the profit. So what we understand from this we see that highest sales is coming from the phones and then chase the both have almost very close competition but with a [02:39:36] we can easily say that phones have done the highest. Okay. So the bigger values the right hand side. So it says phones have done the highest sales then the chairs uh so when you see the length of the bars they are almost very close but [02:39:51] when you see the values there it says 1330k and 328.5k okay with a small margin the chair stands in the second place um then we can see the third place you know there is a big mar difference okay so that is [02:40:05] almost 3.3 lakhs to 2.3 lakhs okay 2.23 1 2 three lakhs more than almost like there between the second highest and the third highest okay there is a big difference that is what is this bar is telling and then you can see there is a [02:40:19] decline tables is very close to the this one and so on but when it comes to this line chart is showing the profit so it says for a sales of three lakhs you know 30,000 it has given me a profit of um okay it has given a profit of close to [02:40:31] okay it has given a profit of close to less than 50,000 but we see um this is less than 50,000 but we see um this is the profit of um what do you uh phones and this is the profit of chairs. [02:40:44] Okay. So actually you know there is 0 to 50,000 50,000 and when you see chairs have given me only 25,000 and the phones have given me almost close to 50,000 less than 50,000 but close to so that means there is a difference of 25,000 [02:41:01] which is a big difference but we are not able to find out the difference. The reason is uh we are using this particular same measure which has a bigger denominations. Okay. Because we have the highest sales of 3 lakh 30,000 [02:41:14] but highest profit is just around 50,000. Just above 50,000. Okay. So now this the profit always comes in the smaller denomination. And if you're becomes very difficult to know where things went wrong and so on. Okay. [02:41:30] the tables have shown negative values and so on. But you know it'll be better profit because the profit always comes in the denom smaller denomination. So you can see that maximum profit is around 50 55,000. Okay. So let us have [02:41:45] it as a separate scale. So for this what I will do is I'll just go to the format. I just go to the format. In the format in the format we have this option here [02:41:57] called as um secondary y-axis. We have something called as we have y-axis. We have secondary y-axis. Just expand the secondary y-axis. Just expand the secondary y-axis. And under the secondary y-axis, you can see [02:42:13] we have this option here called as align zero. Align zero. Align zero. Just enable this. Automatically you get a secondary y-axis. And here we can see that. Okay. So that means now we are comparing to this particular margin. [02:42:29] comparing to this particular margin. So we can see it has done almost 45,000 profit and when it comes to the profit of the chairs we see it has just around sales they have a neck to neck competition but when it comes to profit [02:42:41] they have 50% decline in the contribution of profit. Okay. And we see that maximum profit is coming from the what do you say the copers and for that to get the maximum profit. Okay. Highest profit we see the [02:42:56] sales are just not even average lesser than average. Okay. So we see that highest is coming from the top. This is a line is a profit. No. And the lowest say phones which is a big negative profit. Even though it is when it comes [02:43:11] out of 17 which is a very very good sales but the profit wise one of the worst one of the worst. Okay. Minus 18,000 a things. Yeah, we need to do an investigation on that. You know why [02:43:27] Sales are happening but the profits are negative. Okay, that means overhead cost is too much. So how we have to control those particular things. Okay, that investigation can be done. So only we know what why things went wrong when we [02:43:40] can do the investigation. Is it okay and we see we have three products with a negative value. So we see the bookcases more than one lakh of sales but still more than one lakh of sales but still minus 3,000 3,500 minus okay profit and [02:43:52] we see even like uh supplies uh we have a sales of how much you know just a small value like 46,000 but still the negative profit of 1200 minus 1200 okay so this has to be immediately taken care profit is like you know whatever we are [02:44:07] gaining the profit from here and we are giving to this guys okay uh thing so that means that has to be immediately controlled but Otherwise we see that missions is a big concern. Sales are good but the profit is very minimal. [02:44:19] Okay. Uh we see the same thing binders sales are good and the profit also is sales are good and the profit also is not bad. Okay. But we see that um if the line is going above this particular head of this. So that is something which is [02:44:32] very profitable one. So we see that phones has done a very low sales of just phones has done a very low sales of just around 78,000 and a profit of 34,000 45% profit. We see the kind of this one the length of the bar and the line okay it [02:44:47] is really amazing so that is what I say this has to be our favorite product copers and the phones the phones with just a 78,000 we are getting 35,000 profit that is almost 45% profit here also we see almost 37 38% profit we are [02:45:01] getting but the concerns are immediate concerns is the negative values okay and then immediate concern I can say is the machine even though it has done a sales of almost close to two lakhs but profit is just 3,000 not even 2% profit we are [02:45:16] getting. So we are able to understand this. Got it? Understood? Sorry. Hey guys. Hey, we'll see the next a very interesting chart which is nothing but a scatter plot chart. Okay. [02:45:34] We were trying to understand why things went wrong in the last three charts. Either it is a ribbon chart or line charts or even it is a uh what do you charts or even it is a uh what do you call it as waterfall charts okay we had [02:45:48] some kind of a breakdowns okay where we trying to find out okay why things went wrong how it can be corrected that is what we mainly looked at is it what we mainly looked at is it okay so now uh here uh we'll see this [02:46:03] particular scatter plot chart as it is called a scattered we can see The bubbles are being scattered here and there. Okay, the bubbles are just being there. Okay, the bubbles are just being scattered here and there. Okay, so now [02:46:17] in this we have a x and yaxis. Generally what we do x-axis we take a dimension values and the y-axis we take a measurable values that is what we have seen. Okay, till now the commonly used. But here both x and yaxis both x and [02:46:35] yaxis are going to be the measurable values. Okay, they're going to be the measurable values. Both x-axis and y-axis is going to be measurable values. [02:46:48] Generally, when I say values, values we add a measure. But here it is reverse. In the values, we'll be adding the dimensions. Okay, I'll show you. So on the x-axis I'll take a sales on the x-axis I'll take the sales uh fine I was [02:47:04] x-axis I'll take the sales uh fine I was just telling like uh we have taken both x-axis and the y-axis both have taken the measurable value okay sales and the profit based on which we see x-axis me [02:47:19] we have the sum of sales here and the y-axis we have a sum of profit so now we see that 0 0 so that means if it is on this side. So this is your sales and this is your profit. If the bubble is in the top is the high profit. If the [02:47:35] bubble is on your right, it is the high sales. Got it? So since we have not added any dimension, so we just have a single bubble which tells me what is the total sales and what is the total profit. What is the total sales and what [02:47:51] profit. What is the total sales and what is the total profit. Cool. Great. So now the next thing is we have done this. Now in the place of values generally in the place of values we add a dimension but here we'll add sorry values we place the [02:48:05] measurable values but here I'll add a dimension. Maybe I'll just add the category. When you add a category so you can see what has happened. We have the three bubbles here again. So one is okay you [02:48:17] can show the label labels. So you can say the labels is show the labels category labels. Okay. I'm just going to the format showing the category label here. Okay. To know what is that each this one is. Okay. Now when you look at [02:48:30] this one technology is on the right hand side. technology is on the right hand side. The technology is on your rightmost. Okay. And it is in the topmost. So that means what is the meaning of this? The [02:48:44] technology has done the highest sales and also it has given me the highest profit. technology has the highest sales and also has the highest profit. Clear? [02:48:56] Now the next thing is when it comes to office supplies okay the sales wise okay we can see that x-axis it is taking from the 700k okay uh that is 7 million so I can make that 0 0 so I can just go to the format and say this is the x-axis no [02:49:12] the format and say this is the x-axis no I can make this minimum as zero I can make this minimum as zero yeah so 0 0 so or if it is auto also is can see that it is starting from this one so now when you see Office supplies [02:49:26] they have a lesser sales. That means um when it comes to office supplies when you see the sales are this side means the sales are less and the profit also slightly less. Okay, we have a little lesser sales more lesser sales but [02:49:40] slightly lesser profit. So for lesser sales lesser profit is fine. sales lesser profit is fine. Okay, so office supplies has um lesser sales but second highest profit but the major concern here is furniture. Okay. [02:49:56] It has a slightly it is high is higher sales than the office supplies. It has the higher sales than the office supplies. It has much higher sales than the office supplies. Almost 13 lakhs 42,000 plus sales. But when it comes to [02:50:10] profit, it is very very low. We can see the profit wise where exactly technology is there where we have the office supplies and where we have the phone uh what do you say furniture. Okay. So when you see that you know uh technology has [02:50:24] you see that you know uh technology has done a profit of 1 1.5 lakhs 1 lakh 50,000 and this is 1 lakh no so this also is around 1 lakh 30,000 but when it comes to furniture it is just around 20,000 [02:50:38] is the total profit so but we know when it comes to sales wise 7 lakhs 40,000 plus okay so it has a very good sales it has the higher sales even than the office supplies okay but when it comes to profit it it is very low. So what we [02:50:53] understand from this what is that we understand from this furniture even though it has a very very very good sales it has the higher sales than the office supplies but the profit wise it has a very very very very bad [02:51:09] wise it has a very very very very bad profit very very low profit okay so we can see if you just look into this 8 lakh 36,000 145 uh 1 lakh 45,000 means lakh 36,000 145 uh 1 lakh 45,000 means around 17 18% % profit I'm getting the [02:51:24] same thing here also okay for 7 lakhs 19,000 we are getting a profit of 1 lakh 19,000 we are getting a profit of 1 lakh 22,000 again 17 18% profit but here 7 22,000 again 17 18% profit but here 7 lakhs 42,000 sales my profit is only [02:51:37] lakhs 42,000 sales my profit is only 18,000 so if it is 10% I should get 75,000 is it yeah not even 10 not even half of it so forget about 75,000 so [02:51:49] even if it is 5% you should get 35,000 no not and five. So it is just around 2 2.5% less than 2.5% profit we are getting. That is what we understand from this. Yes. So that means here is something like you know as a data [02:52:02] analyst you are the people who are going to explain to the decision makers or business managers who are going to actually take the business forward. So you can explain that even though it is belongs to this particular technology we [02:52:15] are something like you know having no profit here a very low profit. Okay, almost like no kind of a so here you're getting around 45% profit here we just getting not even 2% profit it's a big concern so we need to try to find out [02:52:28] how things can be corrected might be so the business manager will think like you happening tier one may how it is happening or some cities may it is doing good profit so how we are able to generate that profit there so might be [02:52:42] semi-commercial area or a non-commercial area so you know the golden prices are cheaper and the Commercial prices are very expensive. Okay. Where has to be kept? What kind of a products if that kind of a cost cutting can be taken care [02:52:55] also? Might be the profit is sorry overhead cost is too much that is the reason that it is low profit. Okay. So that has to be done a correction. So that kind of ideas can be given. Now again when you see the office supplies [02:53:08] we see the office supplies most of the products are in near the zero. So that means when you look into this it is envelope past papers okay all the stationary items you know uh so I understand this is standing in the very [02:53:21] understand this is standing in the very zero line uh low sales low profit means the product of value itself is very small the value of the product itself is very small when I say the product of value itself is very small the sales are [02:53:33] very low and the profit will be always a small portion of that they are standing in that place it is fine so only concern is when it is a negative profit is a it out like you know why this negative things are there otherwise you know this [02:53:47] uh having a low low sales because the when you see the name the fasteners envelope art binders so they are the small value product so how muchever you do the sales the value of that product is low so profit also will be low profit [02:54:00] is always a a portion of the sales but out of this we see that papers have been outperformed even though with a low sales even though with a sales of something around 75,000 And we are getting a profit almost close to nearing [02:54:15] to 40,000 or you know 35,000 or something. That's really a good one. Okay. So um it is outstanding kind of a things. Even with the low sales we are getting a very good profit. 45% profit we are getting. That's really good. But [02:54:29] again you see that binders and storage they have very good sales and a very average profit. Lesser the profit than the papers. Storage is much lesser than the papers. So but the sales are high the profit is low. Okay, that is what we [02:54:42] are able to understand from here. So we can just see like you know okay how we can increase this profit. So sometimes you know we need to learn when I say profit papers almost we are getting 45% profit so how is it able to generate [02:54:55] that 45% profit there okay try to learn things okay how it is doing okay and try to implement those particular things so that can so even though they are doing a very good sales if I can implement some learnings from here into this this [02:55:09] profits can be improved okay something becoming small is not bad okay okay people learn from the kind of a thing and try to implement those things. Okay, [02:55:21] that is what we again we are able to understand. Okay, now let me go to the understand. Okay, now let me go to the third category as furniture. So when I here, okay, hey this is your zero line, is it? This is your zero line. This is [02:55:35] your zero line. What we understand from here is we have four products belonging to furniture. Okay, so we have four products belonging to furniture. We have a chairs, we have a furnishing, we have a bookcases and we do also have [02:55:50] the tables. So one thing when you observe under furniture we have four products. Out of four products, two products are having a negative profit. Out of four products, two products are having a negative [02:56:05] products. 50% of the products are giving us a negative profit. They are not profit we are generating from the other products we have to sell it out here. Okay, which is negative profit is a big concern. No, we don't want to run a [02:56:19] business with a negative profit. Okay, fine. That is the thing. So 50% of the products are giving a negative profit and we see that chairs even though it has done the highest sales. Okay, phones and chairs you know you see that they [02:56:34] have done the highest sales but the profit is very very average or I can say below average. So that means what you understand from here for 3 lakh 30,000 we are getting less than uh just about 20,000 is a profit. So hardly 7% 8% we [02:56:47] 20,000 is a profit. So hardly 7% 8% we are getting the profit and we see the furniture. So it has done a sales of close to 1 lakhs you know 80,000 something okay but still my profit is just around how much okay around 10,000 [02:57:01] is a profit we are getting so again you know 78% profit you're getting but you know tables when you see here more than two lakhs more than two lakhs 2 lakhs 7,000 and the profit is one of the worst minus 20,000 close to minus 20,000 okay [02:57:15] bookcases more than one lakh sales okay but still my profit is 3 and a half,000 negative Okay. So what we understand from this one is the furniture is a big concern. We have four products under furniture. Out of four two products are [02:57:31] giving a negative profit. To give the negative profit is the sales are bad. No sales are very good but the profit is very very bad. Okay. As I just give an example you know opening in a opening a gym or having a gym in a commercial area [02:57:45] in the ground floor something like you know you have a you are a very rich guy and you don't respect the money. Okay. Uh or like you know uh if the same thing can be just put in the third floor or fourth floor people don't mind in going [02:57:59] fourth floor people don't mind in going to the this one is it uh things okay but promoting the kind of a business marketing kind of things will be different but you know we always we always see that gyms in the top and most [02:58:11] of the times we see the gyms are kept in the residential areas and things. Okay. Uh fine. So so that you know they can do the cost cutting okay and that particular cost can be done in a more productive way okay might be I can say [02:58:26] like you know tables okay can be one reason it uses lot of space to store the data and things and if you're using that in a highly very expensive commercial whatever the sales are happening more than that you are spending on the what [02:58:42] than that you are spending on the what is say um commercial rent okay and I see in the commercial areas know even if the vehicle has to go and pick up from there the big vehicles okay uh it becomes difficult for them to go there okay they [02:58:54] might also charge more so that kind of overhead cost can be do I'm just I'm thinking because of furniture things that can be things so that is what kind those particular things so that okay we can improve like you know otherwise the [02:59:09] sales are good okay two lakhs of sales is done I cannot directly stop the business that is going to overall affect my business so but you know I need to where things are going wrong. I understood that you know sales are good [02:59:23] but profit is negative. Okay, which is a big concern. So I have to do a corrective. So telling that guiding them okay oh the store should not be kept here. This is too much of overhead cost. So when we um overhead cost is something [02:59:35] like you know see generally around nobody does a business with less than 20% of profit. Okay. So that means we and you're getting minus 8% negative this one that means we are not able to manage your cost with the help of the [02:59:48] profit with but you know you're getting more than that. So this corrections can more than that. So this corrections can be done. Got some ideas here guys. I hope I had not take too much of time of yours. [03:00:03] just categorize this into different categories to know like you know um like I'm getting a big concern is the furniture one thing I understood with the help of the uh only by showing the category furniture was a big concern and [03:00:19] I'm went into one step further there and try to find out what exactly is going wrong with the furniture. So you I got some more clarity that we have four two products are giving me negative profit which is a big concern and other [03:00:33] two products what is there even though the sales are good but the profit is very average or below average. Okay. So that is the reason I can see that furniture is going in a very bad this one. So when you look into this when you [03:00:46] out like you know how this can be corrected. So go and check sales um okay see again further you can see like you know which city may um sales are the positive in the tables in that city when it is positive profit there might be [03:01:00] some cities so where the tables are giving me a profit positive profit in that positive profit how we are able to get a positive profit where he has kept the commercial how much he is spending for the manpower okay how okay so see [03:01:15] one thing can be a negative profit is overhead cost okay your overhead cost is too much so only then it might go to the loss okay we'll look into those things so that we can do a corrective measures okay got it now let's move here as um so [03:01:33] is first I'll try to add the reference line okay first let me add a reference line for keeping it for zero okay so we studied this reference line in the line chart no I'll just go this is your y-axis I want to add one reference line [03:01:48] on your Y-axis uh for zero I just keep it as red color. So this is where I can see that zero and which is going below zero. So these are come to the mission is very close to zero. Okay. I don't like numbers. Okay. [03:02:04] See if you add the numbers too much of labels. It just becomes clumsy. Okay. Just fills with lot of text there. Okay. uh things but we based on the distance we can come to know the kind of distance what has been covered on the profit from [03:02:17] zero to this and zero to this okay now the next thing is uh I'll try to add more lines here and when it comes to add more lines and when it comes to add more lines choose the type I just say y-axis okay [03:02:32] choose the type I just say y-axis okay um I'll take the average line. Okay. I'm taking this what you say average line. [03:02:55] like you know this taking in the average or you can also do one thing is um I'll just remove this average line. So now what I want here is I want to divide [03:03:07] this into four quadrants. Okay. Four quadrants is what? Quadrant is four. No, quadrant will be obviously four. I should not say four quadrants. I want to English is it? Yeah, I'm very bad in English. Yeah, I understand. I want to [03:03:20] divide into four parts. Okay. So, we call it the quadrants. I can just draw lines here. So, I'll just draw a line. So, this is your y-axis. Okay. On your So, this is your y-axis. Okay. On your y-axis, I'll just say okay, on your [03:03:33] y-axis, I'll just say okay, on your yaxis, I just want to have value. Okay. 0 60,000 is the highest. So I will just keep it around something around 25,000 or around yeah 20 30,000 I'll just keep it okay 30,000 [03:03:52] and the same way I'll just add one more line here okay and this line I'll just want to keep it for um so what you Okay. [03:04:14] Okay. On the x-axis, I'll just say the value is [03:04:29] value is 0 to three lakhs. two lakhs sorry 3 lakhs 20,000 half of that is 1 sorry 3 lakhs 20,000 half of that is 1 lakh 60,000 is it so I say 1 lakh 60,000 okay you can just give any I'm just giving roughly kind of thing so for [03:04:45] three lakhs 30,000 is the high this one and so zero to that 1 lakh 60,000 I'm just keeping it as roughly around I'm just want to have 50% distance cover just want to have 50% distance cover okay now we can see I've just drawn this [03:04:58] okay now we can see I've just drawn this particular four quadrants okay Now this is your sales and this is your profit. Is it? This is your sales and this is your profit. Okay. Now I can say this quadrant is I can call this as what you [03:05:14] say I can call this one as okay low sales low profit low sales and the low profit. This products like okay envelopes binders appliances the sales are low the profit is low. Okay. So [03:05:29] those products which is coming in over here their sales value is low and the profit also is low. Okay. And when you look into that product there are the small small products and the envelopes, labels and all those things. But at the [03:05:42] same time I look into this quadrant. This is low sales but still high profit copers papers even though they have done the low sales. Even though they have [03:05:54] done the low sales but the profit is really very high. Okay. So the copers, papers, accessories also very close to this line. Is it? Okay. I can consider [03:06:06] that also. Okay. There are the products where I say even though we have a sales low but the profit is high. Okay. [03:06:19] Now those things which comes in this quadrant they are high sales and high profit. We see the phones. Okay. Especially the phones we are seeing that high profit just because the sales is high for three lakhs 30,000 you have [03:06:31] done the sales and the profit is around 45,000 this is just around 11% 12% profit okay and we are seeing that you know high profit just because the sales are good but here we are getting you know 37% profit here we are getting 45% [03:06:48] profit compared to that we cannot compare is it okay but yeah with the Okay. Now the quadrant which comes in this is Now the quadrant which comes in this is what? High sales. The sales are high but [03:07:03] it is low profit. Okay. Like chairs, storage, machines, they are like you know the sales are very high in spite the sales are high very high in spite the sales are high but my profit is very low. [03:07:18] but my profit is very low. Okay fine. But if you ask me, so this I will keep it as my favorite product. Which one? Low sales, high profit. See, even with a low sales, they are giving me a very high profit. If I increase the [03:07:33] business of this one, my profit is increasing. If I increase the sales of 100 rupees, I'm getting a 50 rupees extra profit. Here, if I increase a sales of 100 rupees, I'm just getting only 20 rupees or extra 20 30 rupees. [03:07:46] Here if I'm increasing a sales of say 100 rupees I'm just getting only 10 rupees extra. Here if I increase a sales of 100 rupees I'm just hardly getting one or two rupees extra. Okay. So here if I increase a sales of 100 rupees [03:07:59] directly 50 rupees is my profit here. 45 rupees is my profit. So if you ask me here even with the low sales low investment low sales I'm getting a high [03:08:11] profit. So I will keep this as my favorite product because the sales are low. When I say sales are low, my investment is less. Okay. Um when my investment is less but still I'm getting a higher profit. That has to be my [03:08:25] favorite product. So I will say hey in all the regions try to increase the business cop copers and papers. Try to do the maximum promotion on those particular uh products. Okay. And try to get the maximum business out of this [03:08:38] giving me a very good profit. Okay. Now low sales low profit we try to find out like you know okay um see um again um the product of value is very small. Okay. And the profit is uh the sales [03:08:54] value profit always comes from the this one. So you try to increase the business of this year we try to see in the it is again like you know uh I go to the Reliance Mart okay the departmental store. Okay. Sometimes when the store is [03:09:08] in my residential area so people come there just to buy the milk packet and they don't go only with the milk packet with the milk packet they buy the kind of uh even the chocolates and all the things see the way they keep that [03:09:22] product you know milk may hardly be get a profit you know 30 rupees packet and they get a hardly one rupee profit in that okay when you see it's hardly 2 and 3% profit but still okay they keep that product because people come to buy that [03:09:36] product with that you know they do the other buildings okay so some products even with the low margins we try to they come there but they don't buy only that also moving okay we look into those things okay is that kind of a things [03:09:50] things we can try to look for there are because you know you go to a departmental store it is not one product there are many products because of that you know people go there because in one [03:10:03] do you say um shop we get different types of multiple products So I just go there. Okay. Then going for a specialized kind of a things. Yeah. So the products which is pulling the customers here and might be because of [03:10:16] that you know we are trying to do. So all those analysis we try to do it. So at the end of the day like you know this has to be my favorite product that is profit we try to find out like you know might be there is a lot of overhead cost [03:10:29] that can be reduced to that okay we can increase the profit yet because we are not very happy with the profit because just the profit is good because the sales are good but um if this comes with um even with low sales high profit if I [03:10:43] to be a better one or if they have the same high sales but the profit is something like you know we are getting 45% % profit here you are getting some 12% profit so if I have to ensure like you know here if I can in how can we [03:10:57] increase this 12% to 45%. So that my profit will be something like this. Okay, that is but those which is standing in this one is a big concern. Here we have the high sales but the profit is very low. Okay, binders, [03:11:13] machines, okay, all those things. So this is leading to a loss. So in this case I cannot stop the business. We try to learn from this kind of experts. Okay, how papers and copers are able to make such a good profit. Okay, try to [03:11:27] learn those things and try to implement those things from here. Okay, as I said learned. No, as I said the different tires of cities, different um kind of rentals you have. So overhead cost, how we can have a cost cutting, maintenance [03:11:40] and all the things. So like those kind of a things we can learn from there and try to implement here. Okay. Because um I say this are something like is the major concern where the immediate action has to be taken and much more the [03:11:53] immediate is like you know um high sales and the negative profit is a very very big concern. This is a very important um um uh like you know we have to take a decisions. Hey apart from this one okay so I'm asking time in this particular [03:12:08] chart here. I can also go with something like you know um I see this is the one chart where I can even add the animations to this. Okay. No other charts can have animations. Okay. Uh the default the built-in charts. Apart from [03:12:23] charts from the app source that charts I can do the animations but the powerb's native charts out of this I can say this is the one chart where I can have the animations. Okay let us do the animation on this. So maybe I can say uh that [03:12:39] understanding. So I can remove those lines is not needed I think. Okay just to understand that quadrants okay that zero line was needed. Okay I think okay fine anyway it is now what I will do here is [03:12:55] I will try to do the animation on this. Okay. Um what is animation? Animation is like you know giving some effects. To do the animations I'll just go to the build. In the build we have seen values x-axis, y-axis, legends. But here we [03:13:11] have something called as a play axis. We don't see the play axis in every charts. Okay. But this chart has a play access. Okay. Play is for animation play button. No, I'll go to the play axis. In the play axis, I'll just go to the order [03:13:25] date. I'll just go to the order date. I've just selected the order date. Oh, it is taken a day. Is it fine? No. What you do is just expand the order date. from the order date I'll just take the year see even you can go for the quarter [03:13:38] month and this one but we'll go with the year so you'll know what it is fine so if you want me to repeat I'm just repeating so in the build of the scatter plot I'm just going to the play axis play axis in the play axis I'll go to [03:13:54] the order date from the order date I'll just extract only the year that's it as soon as you select the year from the order date into the play access. We can see here you got a access button. Play button here. We can see [03:14:10] there is one play button here. We have a play button and it says we have four play button and it says we have four years 2018, 19, 20 and 21. It says which years 2018, 19, 20 and 21. It says which year. Now I can play this from 18 to 19 [03:14:24] to 20 how things have been changing. You observe here it is playing it is playing on its own speed. It is playing on its own speed. But one thing you observe [03:14:36] this one the way it has been playing. It was nowhere from nowhere it has come to was nowhere from nowhere it has come to somewhere. I'll do one thing. So play it goes with its own speed. I but I want I don't want like this go at this speed. I [03:14:50] want to see at each year when I select 2018 it says in 2018. when I select 2018 it says in 2018. Okay. In 2018 um we have a phones and chairs. We have the highest sales and not only the highest sales they have [03:15:05] given me the highest profit. Both the phones and the chairs they have the highest profit and the highest sales. Okay. They both have the highest profit as well as the chairs also have given the second highest profit. Okay. In 2018 [03:15:19] itself they are the top leaders. Okay. Apart from that you know copers is completely invisible. Okay. Now when you go to 2019 we can see what is happening. One thing I observed is missions. The missions which was standing over here it [03:15:34] is moving to this. So that means it is declining the sales. So you can see sorry this is 2018 from 18 to 19 you can see the emissions the way it is going completely inside. Okay, they but we can [03:15:49] see this one when you see the phones and chairs the way it is going it is 18 18 to 19 it is going instead of all other things are going forward but this is going backward is it so it was something over here okay and it shifted okay that [03:16:03] means it was a decline in the sales and it was something over here that means slightly decline in the profit and the decline in the sales too okay it has reduced even the sales too okay now when you say 2020. Oh, [03:16:19] they have gone something like this. They have gone something like they have increased in the sales but the profit still there is a negative. Okay. But now they were the top leader in the profit as well as the sales. But you can see [03:16:32] sales but not in the profit. Who has come top? Copers. And copers was completely invisible. Okay. And when you see 21 the way it is standing is very highly visible. Okay. So what we understand from this is on 2018 we see [03:16:48] the copier was completely invisible. They hardly have done a sales of 10,000 They hardly have done a sales of 10,000 with only 2,900 profit. In 2019 you can see what it has done. It has doubled. No it has tripled. From 10,000 sales it has [03:17:04] it has tripled. From 10,000 sales it has gone to 26,000. 260% increase. And the profit from 2,900 300% means it is just around 8,700. 300% means it is just around 8,700. So 350% growth in the profit 260% growth [03:17:19] in the sales. You can see the way it has been grown in one single year. In 20 you can see further it has doubled both the sales it has doubled and the profit. In 21 you can see again. So we can see when you just when you click there we can see [03:17:33] it's showing the complete progress. In 18 it was having a very low sales and the very low profit. In 19 you can see it increased the sales but not only the sales it has increased even the profit and you can see the further it has not [03:17:47] kept there. Okay. It has been moving a long distance every year. It is covering a huge amount of sales as well as the profit it is increasing. [03:18:06] just click on the phones we can see the travel. Okay. It has declined in the in the sales slightly increase in the profit but overall we see that oh they have shown a progress in the sales but the profit where it is the same. Okay. [03:18:19] Same thing is your chairs. But when you see the papers uh papers are growing positively but amount of covering 18 to 19 there was hardly any kind of a increase but 19 to 20 there was a small increase. is 22 it is increasing both in [03:18:33] compare we hold on the control key and click you can see both so way you can see there from nowhere and every year the amount of sales it is growing okay see if it is growing only in the sales okay so it'll be growing like this if it [03:18:46] is only growing the sales it is growing the sales as well as the profit doubling the sales and doubling or tripling the profit okay every year it is covering a huge amount of sales and the profit is increasing so that means there are lot [03:18:59] of things to learn from this particular topers I say hey so they say that no the parents keep telling like you know see that guy how is he growing okay they call that xyz neighbor's name and say hey why he's doing and why you are not [03:19:13] able to do so always that you know you see the copers you see even when you see accessories uh it has shown the progress yes but you can see um 8 19 to 20 there was a decline there was not shown progress but [03:19:27] you can see it is not um the angle here you know it's slightly tilted lower. It is increasing the sales. The profit not that very much. Okay. But copies is increasing both in the sales as well as in the profit. Okay. Fine. Understood [03:19:42] with this any products we can just look into. It tells you the complete story what has happened in the four years. And we see the machines. Okay. So you can Okay. In one year we can see it has shown the complete decline in the sales. [03:19:55] Okay. Uh and then again it is come back the sales. that we see the profit wise it is not going like this. Okay. So it is just moving like this and we see in 2021 it has a negative profit of missions. [03:20:10] Okay. Overall so it is just playing on here and 2021 to it has shown a big negative profit. You can see in 21 it has a negative profit here. So minus0 is it - 3,000 in 2021 this is there. So we are able to [03:20:26] understand this. Okay. Okay. Junet uh see talk about the bubble size and all the things. Okay. So this is the size of the bubble um that can be done. I'll the bubble um that can be done. I'll tell you yeah when it comes to size you [03:20:41] on the size or something. You can just do you can take a measurable value. So the bubble size tells you how much the sales has been done. But I'll tell you if it is in the right it is the sales and the top is the profit. But still [03:20:54] bigger is the bubble. Smaller is the sales is the smaller bubbles that is what it is the indicator but I see I feel like you know uh we it has to be it though the options have been given use it wisely uh this is just like to say uh [03:21:11] representing the higher sales with the bigger see like no tables the sales of the bubble is good but the position where it's standing is down so I feel like you know this itself is better when you have a labels and things okay so on [03:21:24] top is the profit and tool tip to I think we discussed like you know what all the things that you have used it is showing the tool tip want to get the additional information you can use the tool tip so now we see [03:21:40] the sales and the profit 60,000 is a sales minus 8,000 is a profit what is the profit percentage I want to display that so I can just go to the tool tip and include the profit percentage so when you see the copers it has given [03:21:53] so when you see the copers it has given me a profit of um say sales of 62,000 profit of 25,000. So the kind of percentage of profit I'm getting is 39.8 that is almost 40% profit. When it comes to the phones I'm getting 12%. So that I [03:22:08] but still you want to display on the tool tip just include those particular measures or dimensions or anything in the tool tip that can be an additional information that you want to add on the tool tips. So by default whatever you [03:22:23] have used to create the tool tip that has been there. Okay fine. I think this was something about your uh what do you say uh scatter plot chart. It is just the scatter as the name says it is things have been just scattered here and [03:22:37] there. Okay. Geography uh we have this particular fields here. Okay. When you look at this particular fields when you look at this particular fields we have a geographic fields you know we have a country city state postal code region. [03:22:52] country city state postal code region. Okay. things. Okay. Uh I hope you have done this geographic charts in Excel also. There is this geographic charts what we do. So we try to find out like you know what [03:23:04] is the total sales in the each state and the E state wise we do the sales. I think this is what we generally do in Excel. Okay. So I just say is equal to let I just want to have a state which is [03:23:17] going to be the unique um I just say table one and the table one I take the state is it you write this function to get the unique state and then I just want to get the total sales I say total sales so which is [03:23:31] sales I say total sales so which is going to be like you know what sum ifs [03:23:43] we say total sales uh which is going to be nothing but uh sum ifs. You write it like this is it. And then I just say summing range is uh on the table one is [03:23:55] summing range is uh on the table one is the sales and then I just want to have the sales and then I just want to have um table one of what is the criteria range is the state and the actual criteria has to be the state what you [03:24:08] criteria has to be the state what you have selected. Fine. And um yeah. So then I need to have a H tag. So I just say H stack. I just want to get the state and the total sales. That is what I just want to display. We are able to [03:24:23] I just want to display. We are able to get this total sales for each state. [03:24:37] wise what the sales are happening and then we just create this kind of a map. Oh, same map what we saw there uh in the graph. So we are able to see how the sales are happening each statewise. We understand one thing here is see the [03:24:51] this is called as a legend. No. Okay. which is telling that highest sales was uh 457K and the lowest sale was less than 1K 919. Okay. And that is the light color is the low sales. Dark color is the high sales. So I can clearly say [03:25:05] that California has done the highest sales compared to any other regions. Okay. Very high sales. And then we see we have a New York. Okay. Which is again slightly lighter lighter color than the this one. But the darkest one is the [03:25:17] California has done the highest sales. Then we have this particular New York. Then we have a Texas. Then we have a Washington. Okay. And then we have the other states like say Florida and so on. Okay. So this is what you would have [03:25:30] done this in Excel. Okay. Don't see you have done this. This is a basic things in Excel. We do it. Okay. So I just wrote this particular function to just get like you know what is the state wise how the sales have happened. Okay. I've [03:25:42] Yeah. Anyway, I'm not going to teach you. It's not an Excel session. I know. Sorry for that. This also again. Okay. But this just to revise like you know you would have done this in Excel. Okay. Something similar to this we will do it [03:25:55] in the PowerBI. Okay. So we will try to do this in the PowerBI. Now if I have to do this PowerBI mean so I don't have to write that kind of a complex functions guys. I don't have to write that complex functions. Very very easily I can do it. [03:26:08] So what did I do here? The first thing is I'll again start from this scratch. So we have the same map. Same thing what I showed in Excel in the this one. I I showed in Excel in the this one. I took the map. I took this map for [03:26:21] creating the map. Okay. And you can see the same map is there. The same map is you have studied in Excel, the same thing. I'm just using a map. When you select a map, automatically you get a 10 template. So, which is telling that [03:26:35] here and there which is based on the this is kind of a road maps. No. Okay. Kind of things. So it is not pertaining to any city state or anything because you have not selected any um city or a state or a [03:26:49] country or anything is it? Now when you're working with geographic data we have the fields like locations. We have a fields like locations locations has to be a geographic field. Locations locations has to be a [03:27:07] geographic field. What do you mean by geographic field? What PowerBI does here is geographic even Excel was doing the same job. I did not say that um California, New York, Washington is geography. It by default it understands [03:27:21] latitude and the longitude and it is creating a chart. That is what is happening even in Excel. Okay. Um Excel is amazing thing. Okay. U see Excel as a data analyst Excel has to be something like the basic things. Uh we can do a [03:27:36] think you people have already done that. telling about Excel but Excel is so we set just a single click the chart was been created. Okay. Now here when I say [03:27:51] geographic field what do you mean by geographic field? Geographic field is nothing but the field which has the name called as country, called as country, state, uh city, postal code, airport [03:28:05] code. when you have the field with this names. So what PowerBI considers this as it automatically consider this as a geographic field. So inside that whatever the values are there like California is a value. Okay, [03:28:18] Washington is a value. So that value it is going to take the help of the Google okay try to get the latitude and the longitude based on that particular Google dictionary. So that means if you have to create a location we need to [03:28:32] have a net internet should be there. Okay fine. So here what I do is I just select the location and when you go to the location you can see here we have a field called as state. Okay we have a field as country region here. So I can [03:28:47] something called as a city. So it is not showing any symbol but the name says like you know this is a geographic field. I'll select the state as soon as field. I'll select the state as soon as you select the state. So oh whatever the [03:29:00] states what you have in the data set they are the states belonging to USA. So it is showing this map of USA and what are the state names are there that has been given as a bubbles. Okay. So that means the sales have happened. That [03:29:13] means in my orders table in my orders table when you go to this particular states these are the states are there. You can see here. So we have what do you call it as Alabama like this you know around 45 50 states are there. Okay 50 [03:29:28] different states are there that 50 bubbles you are getting each states values what is there it has been shown now when I see this is a chart and I say this is the chart is a graph when I say it's a graph so that means it has to [03:29:41] represent something is it okay who which state has done the high sales which state has done the low sales I want to represent that so that means um I can since it has a bubbles it has a bigger size I can say high [03:29:54] sales low smaller bubbles I say low sales so for that what I do is here in the build itself we have something called as bubble size we have something called as bubble size so we can call this as bubbles can I call this as [03:30:09] bubbles the circles or bubbles it is called as bubbles okay so that is the don't say why he's calling as circle okay just go to the bubble size go to the bubble size and the measurable value [03:30:23] what is the measurable value say six we can see Automatically the bubbles have been resized based on the sales value. What it is taking the total sales of California and says hey California has done the sales of 4 lakh 57,687. [03:30:40] Okay. Washington has done a sales of 3 lakhs 10,000. Okay. Okay. So it is something like you know in Excel what I did is I wrote a function. If you look into this Excel, first I got the unique states. Then I [03:30:56] wrote another function to do the sum of this is a sum ifs and no. So it has to say summing range is my sales and it has to do the sales for the state wise. So ranges for the individual state. It has to tell me what is this one. So first I [03:31:10] did the summary year. So each state wise what is the total sales and then I created the chart. So in PowerBI I don't have to do this. PowerB I don't have to write this particular different functions. Okay. And I don't have to do [03:31:23] all this particular stuff. It's much easier. Okay. So PowerBI is automatically summarizing the value based on the statewise sales and automatically the chart has been created. It says hey California has a [03:31:35] sales than New York and all those things. easier than Excel. If you work with Excel actually this is much much easier [03:31:50] than Excel. Um and if you can just compare this in Excel. So if I have to do this in Excel so I have to do so many things is it? So I just say these are nothing but the unique states is it? Uh these are the states and uh [03:32:14] say this is like you know the total sales so in Excel like you know you have to write this kind of functions is it so then I'm doing a stacking of that and then trying to get this data you don't [03:32:27] functions in Excel in PowerBI it this particular task has been done automatically. So I think you do this kind of functions in Excel. First get the data, summarize data and then based on that you create a chart. Okay. But in [03:32:40] PowerBI that has been done automatically. So if I go to the table here. So if I go to the table view, you can see the same thing is going to be shown here. So first it is getting the data something like this. It is taking [03:32:52] okay see what I did in Excel. I wrote this particular formula to get like this see so automatically it is taking the creating a chart on that. Okay. Power is much much easier than Excel. [03:33:12] know this is much easier. Yes. Since you said you have not worked on Excel. Okay. I'll tell you this is much easier if you have something like you know we say vehicle so drive the toughest vehicle say like before you used to have that [03:33:27] chak you know one side engine and it is a tough to uh you know start the vehicle and so on if the person drives that vehicle the other vehicles becomes very easy there there you scroll down in the build [03:33:42] there there you scroll down in the build when you scroll down on the build we have things called as bubble size. The circles are called as bubbles. The circles are called as bubbles. [03:33:56] The circles are called as bubbles. Since I'm presenting this as a graph, I want to present some measurable values with the help of which I should be able to know which state has done high sales, which state has done low sales that I [03:34:11] can resize based on the bubbles. For that I need to add a measurable value. that I need to add a measurable value. What is measurable value? Sales, profit, [03:34:25] quantity, the numbers where we can use the aggregation. So what I do? I just the aggregation. So what I do? I just click on the bubble. I add sales. I add click on the bubble. I add sales. I add sales which is a measurable value. And [03:34:39] the chart is been resized. The bubbles are being resized. Okay. So you want to resize the bubbles. We have a bubbles here which says minus 10. You can just um increase the size or decrease the size kind of a thing. So it [03:34:51] uh gives a size accordingly percentile wise. So I generally give multiple examples. Uh I don't know it's a good or bad I don't know for myself but this is generally I enjoy teaching with other batches guys. I generally enjoy [03:35:06] teachings. Okay. I say teaching is more than my profession. I say this is my to say this is my profession. Now I say it's a passion more than a profession is a passion. Yeah. teaching even though it's a profession of mine but I take [03:35:20] this as a passion and I always try to have no new creative things and that is have no new creative things and that is what I try to add it but uh yeah but I that is the reason I always keep you guys unmuted and things okay so [03:35:32] that you have anything please in there you can uh ask clarified okay instead of none of my colleagues are understanding anything and things so that was little kind of this one great job sik Okay. [03:35:47] Fine. Hey, now so let me go with this one. So I just want to show um so what we understand from this one is the California has done the highest sales and then we have a New York that is what is we understand from this one [03:36:00] based on the size of the bubble. Okay. And then we see we have a Texas the third highest and the Washington is the fourth highest and so on we have and at the same time we see that some states we have a very very low sales. So generally [03:36:12] I understand the coastal regions have a higher sales and when you see this um uh central regions we have a lower sales. Fine. Okay. Now what I want to know is now in this particular regions I want to see the category wise or the [03:36:27] happening. Let me go with the segment. Um we have three segment of customers. We have a consumer segment, we have a corporate segment and the home office. So now in each state I want to see the segment wise how they are performing. [03:36:40] Okay. So I can create another map or I can even copy and paste this. Okay, copy the visual and paste it. So I'm just uh taking the same things that means like you know I'm just taking the same x-axis uh that is your state as a location and [03:36:54] the bubble size. You can create a fresh chart also. Okay, I'm a lazy guy you know where I just have done the copy paste. You can uh do is create a chart and then click on the uh location where you can take a state and in the bubble [03:37:08] size you can take the sales. Now what I wanted is I want to know in each region segment wise okay um what kind of a segment we have more okay in the corporate uh sorry in the consumer segment what kind of a customers are [03:37:22] more we have more of a business coming from the consumer or corporate or the in the New York how things are happening in Texas how things are happening segment wise I want to see so if you have to see the segment wise so as [03:37:34] have to see the segment wise so as favorite we have a legend okay legend is what okay We have the sales but I want to divide the sales by the different segment wise. So whenever I talk about legends I'll remember Snehana. Okay. Uh [03:37:48] I'll just go to the legend and I'll just say oh segment. Okay. So as soon as I add a segment you can see this has become as a pie chart. This has become as a pie chart. Okay. Where in this particular chart we see that okay um you [03:38:03] can see the segment here. Okay. consumer 50% light blue is um what I say the uh light blue is your consumer the dark blue is corporate and the this is orange [03:38:15] color is office supplies so we can see that almost all the state 50% of the business is coming from the consumer we can see that in California to 50% is can see that in California to 50% is coming from the consumer and 25 30 plus% [03:38:28] is coming from the corporate and the remaining 20% is coming from the home office fine and when you see in the New New York more than 50% almost 60% of the business is coming from uh what do you say consumer segment around 25% business [03:38:41] coming from the corporate and around 20% business coming from the what is the home office segment when you see um this particular state again almost 60% business so when you see Texas almost 60% of the business is coming from the [03:38:54] consumer segment then we say corporate and the Texas we say like you know how much business is coming from the each segment but in case of Florida uh I can say it is equally divided 35 35 35 or something. Okay. And we see that um here [03:39:08] almost 75% of business is coming from the consumer segment. 75% of the segment. Here it is like you know almost 60% business coming from the corporate 60% business coming from the corporate segment. Dark blue is here. Okay. So but [03:39:22] particular thing over here and you can see in Montana now. So we have maximum segment. Here it is fully 100% business coming from the home office money. We don't have a consumer and corporate segment. We have just a small portion of [03:39:36] consumer segment. Corporate is absolutely null there. Okay. We see that 90% of the business coming from the consumer segment here. So here 90 almost 95% 99% of business coming from the corporate segment. So here you can see [03:39:50] 60% more than 60 70% business coming from the consumer segment. So here we can see it is just around 40% business coming. So we are dividing this in the segment wise. Since this is a bubble instead of creating this clustered bar [03:40:04] instead of creating this clustered bar chart, it is creating a pie chart. Okay. So it becomes easy. This is one thing what I like in uh PowerBI is when is highlighted. Corporate is highlighted. Home office is highlighted. [03:40:18] See we are able to get two informations here. One is um how the state has been performing. how the individual state has been performing and the second thing is within the each state um how the contribution is coming from the each [03:40:31] segment wise um got it. So we are able to get two info here. One is how each state has been performing. The second thing is within [03:40:43] the each state within the each state how the segment wise contribution is coming. So now this legends can be anything. Okay, I took a segment is fine. So or category wise how they have been performing. Yes. Okay. See when it is a [03:41:00] pie chart don't give something which is like you know when I say customers okay performing so we have hundreds of customer in each state okay so you this will be of uninformative so that is what I think the principles of um the pie [03:41:14] you have a dimension with too many values take a dimensions with has a lesser values so that you can understand see even if you add a subcategory you can see you get so many pies here but you know it is creating a pie chart so [03:41:27] power will not regret uh Say no for that. Okay. So whatever you give, how But you know for us it becomes it becomes meaningless is that okay. So we [03:41:39] as a human beings we are more intelligent. Okay. Uh we can just decide technology how the sales are happening where we have more sales of technology. We see Florida we have more sales of technology coming. Um in India now we [03:41:52] have more sales of technology. Orange color is technology. Okay. Sales are coming. Otherwise we see it's the equal contribution of all the categories distribution is there. So we see this in this state Michigan technology is much [03:42:04] lesser sales okay and so on. So we see the office supplies we have almost 50% business coming from the office supplies in this particular state uh year office supplies is less year around 35% here equal contribution all the categories [03:42:17] kind of a things hey how the profit is happening in the states. So we are able to see the sales but I want to see even the profit. Okay. So let me create another chart here. Let me create another map. Okay. Um I'll say location. [03:42:31] I'll go with the state. Location. We have a state. Okay. And bubble size the profit. The same thing what I did. Sorry. Sales. I'll take the sales. This is just like the first chart what we [03:42:45] is just like the first chart what we created. Okay. Uh we are just seeing each state wise how the sales are happening. E state wise how the sales happening. E state wise how the sales are being happening. Now what I want is [03:42:57] I also want to know the profit in this. How will you show the profit here? How will you show the profit here? How can we show the profit here? Okay. See now um so we have something called as um conditional formattings. [03:43:12] called as um conditional formattings. Okay. See one thing is um I'll tell you uh in this particular chart okay uh we took the legend as a segment fine I'll take it a segment it is showing this three colors here [03:43:27] fine um what is orange color is representing the home office this dark blue and the light blue now instead of telling the consumer as a light blue I want to have instead of having a similar color dark blue and light blue I want to [03:43:40] so in case what you do you want to change the color. So these are nothing change the color. So these are nothing but um uh what do you say um the colors no? So you want to change the color. So these are nothing but your legends. No. [03:43:53] So just go to your format we have a legends. Just expand this legends and on legends. Just expand this legends and on this we have those ah we these are the bubbles. Uh these are bubbles. No in the bubbles. So [03:44:08] since we have added a pi just expand this bubbles. Okay, we have the bubbles the bubbles, you can see we have something called as the colors. By now you want to change the color. See, uh this one instead of this or what I [03:44:24] wanted, we have a orange. This we can have a different color. Might be a red have a different color. Might be a red color. Red is to this. Let me have a green color or something. [03:44:41] default is good. So let us respect to the big brains. Okay, I do that. I respect the big brains. Okay, Microsoft has uh by default given some kind of a studies and then only they have come out with the colors. Okay, but sometimes I [03:44:57] want to have my own colors. We can change it. Okay. So I know this is a very contrasting color but still um it's very highlighting thing way how the corporate is performing how the office supplies are performing you want to [03:45:11] change the color you have the full this one to change the colors that you wanted got it this is setting the individual color yes in any of the charts you can the legends what you have you can add your own colors okay okay now what I [03:45:30] want here because I want to show the sales as well as I want to show the profit. Now the profit is not a discrete value so that I can add it as a legend. Okay, that is also a continuous [03:45:45] legend. Okay, that is also a continuous values. Now for that um continuous value is nothing but a measure. So we can have only one measure. We can have only one measure. We can see only one after you click on add field there is if there is [03:45:57] one more plus add that means it allows you to have one more field. No, it doesn't allow. Okay. But still I want to know how the profit is happening in this happening in the states. I want to know that. If I have to find that what I will [03:46:11] do is we have the color under this particular bubbles. You know we have this color. We have the color. Okay. Under the bubbles itself the same colors. Since we don't have any legends it is only one color. See if I want to [03:46:24] change you can see it is showing the color for every this one. Okay. I I I always respect to the big brains. I take the default color but at the same time I want to change the color based on the measurable value based on the profit. So [03:46:37] what I will do is next to the color we have a FX. Where do you see this FX in Excel? Do you see this FX [03:46:49] in Microsoft Excel? It is FX. So that means it is a function. You're going to write a function. So that means I want to fill the color based on some function. Okay. So what I do is I just click on this FX and I say I want to [03:47:04] applying the color based on the measurable value, I'll say I want to apply a gradient color based on what? Okay. Um I'm just selecting this as format style is a gradient color. Okay, what field should be based on what [03:47:20] field? Hey, I want to apply the color based on the profit. So uh here to get the profit you have all the data here and expand the table. From the table you and expand the table. From the table you can just select the profit. P R O F I T [03:47:34] profit where is we have a profit here. So I want to apply the color based on So I want to apply the color based on the profit based on the sum of profit. negative value is it? So I say high value is this color but low value I will [03:47:48] try to give it in the red color. And now if it is an average mid color if you want you can add a mid color. Mid color what do you want? I say I want to say light color or something. I'll just add it and see what happens. You know [03:48:03] highest profit is around 76,000. The lowest profit is around - 25,000. Average of that is going to be around plus 25,000 plus 30,000. I say no no no I want to say mid value is zero. Okay. I want to have my custom mid value. So we [03:48:19] have a custom and enter a mid value. Okay. So this will say mid value is zero. If it is zero okay it will be like this and if it is slightly above zero it is according to the value it is. So it'll have a bluish color if it is a [03:48:34] positive profit reddish color if it is a negative profit. Okay. And I just say as negative profit. Okay. And I just say as now you can see here uh this one we have a bubble but full of okay light color. Maybe I can just say um the mid [03:48:51] itself is good is it? I just go with the white yellow itself. Okay. So we see that very light color yellowish color that means the very very minimal profit is coming from there. But we see that some scary things are here is Texas. [03:49:07] is the size of the bubble and otherwise the color. The blue color says high sales. Sorry. Um the big bubble says high sales. The size of the bubble represent the sales and the color represent the profit and the profit is [03:49:22] if the bubbles is in dark blue high profit light blue low profit red color is a negative profit. So one thing we understand here is Texas Texas even though when you see the size of the bubble after California and the [03:49:38] of the bubble after California and the New York next biggest one is your Texas it's the third highest sales but when it comes to the color it is a dark red color it is a dark red color so that means we don't have a profit here we [03:49:51] have a loss big loss and it's not one place we have many places where we can see they we are running in a loss okay so Now how do we know that there is a profit or loss? Okay, got it guys? See here I'm trying to show that okay even [03:50:05] though we have a sales in many states and some states have done a very good sales but I'm trying to show even the profit there. Okay, so California has New York again the same thing. It has done a very good sales after California [03:50:18] the second highest sales but the profit wise is almost the same color. Okay. Uh things um now how do we know that this is a high sales and high profit? people don't believe me that so I need to show even the profit there is it so for that [03:50:33] in this um build we have something called as a tool tip we have something called as a tool tip I think yesterday I covered about tool tips also is it so this tool tip so here it shows you the tip is when you focus on this any object [03:50:45] it tells you uh based on what you have created the chart for creating a chart I've taken two things one is a state and other one is the sales those values are being displayed as a tool tip okay now I want to show even the profit it there. [03:50:58] So, but I have not used the profit to build the chart. Okay, profit I'm showing as an additional thing. Not I I'm not using this in the build for creating the chart. So, I want to show the profit also. What I do? I'll just go [03:51:10] to the tool tip. I'll go to the tool tip. Here we have this thing called as add data. In the add data, I'll just select the profit. Now, we can see here it shows me the profit also. California has done a sales [03:51:24] of 457K for which we have a profit of 76,000 76,000 and New York has done a sales of 310K that is 3 lakhs 10,000 oh almost the same there we have 76,000 year 74,000 [03:51:38] okay it has done 30% lesser sales but still okay almost the same profit very small difference you know 2,000 per 75,000 is just 2% difference is it yes but when you see Texas what exactly is the story here I said this scary is it? [03:51:53] So 170k 1 lakh 70,000 business has been done the sales has been done but the done the sales has been done but the profit is - 26,000 you can see right okay so and we see wherever we [03:52:08] have a reddish color so all these are negative profit - 12,600 Florida again - negative profit - 12,600 Florida again - 3,400 we have this is -6 17,000 okay minus so we can see the reddish kind of a color they are negative So there are [03:52:22] many states with even the negative profit. profit. So as a data analyst okay my this one is in each state. So what is the kind of a profit I'm generating? So I see that New [03:52:38] York is generating a better profit than California even though slightly there is a lower sales. Okay. See for three lakhs I'm getting 75,000 that is 25% profit I'm getting here. So here for 4 and a half lakhs I'm just getting only uh [03:52:51] 76,000 that means here I'm hardly getting 15 16% profit is that if you see the percentage wise okay so that I'll say I said like you know we'll go to the DAX and we'll try to learn more on the calculation the DAX calculations okay [03:53:07] uh when you see the this one reddish color is a concern here so we have a where are we standing in the case of profit wise minus 255,000 800 26,000 I'm [03:53:20] is in a loss. There are many states which is in a loss. So as a data analyst my job is not to see the numbers and be happy. Okay. I need to look into how we things. Okay. Where things has to be corrected. So I cannot continue business [03:53:35] with those negative profits. So we need to find out in this particular Texas what are the products are there? Which product is having a biggest loss? Is a positive profit? Continue with that. the products which is having a negative [03:53:47] product is it giving any kind of a positive profit in the other state or the business of that product here and that product which is giving me a good here and then do the business. So maybe that can be a corrective measures can be [03:54:02] better plannings and things once you know where things are going wrong. So that is what we are able to understand from this. This is generally this is not given as a part of the syllabus. I just cover more into whatever I take it. I [03:54:16] know that is my good or bad. But um that is what I generally do with other batches also instead of showing this gradient color. So I just want to know where all we have a positive profit. See like we have a dark color, light color. [03:54:30] I'm really very bad at color selection. Guys are very bad at color selections. Is it I am one of that. So I have very bad in color selections. Okay. So now what I wanted is instead of showing this gradient color I want to [03:54:45] define a rule like hey wherever I'm getting a loss it might be even one negative if I'm getting a profit I want to share that is a blue or green color okay in that case what will I do okay let me just create another um map I same [03:55:01] thing say location I will take it as uh street hey if I'm fast please tell me you're faster sometimes I get go involved in know I forget that you know in front some people are there. Okay. Okay. Uh you can pause there and say hey [03:55:18] Sadi you are going very fast. Okay. Control your speed. I'll do that. I'll listen. Um so I've taken this particular location. I've taken a state and I'll take the bubble size is the same thing. So I sometimes I rush here. Uh say size [03:55:31] So I sometimes I rush here. Uh say size is like your sales. Okay. We have this. Now what I want is now what I want is I want to color not a gradient color. Okay, in that case I'll [03:55:45] just go to the bubbles. Uh we have a color effects. In the effects what I do is I'll go for the rules. Instead of the gradient I'll go with the rules. Okay rules are like positive and negative. So again I'm going to apply the rules based [03:55:59] on the profit. I want to apply the rules based on the profit. I say if the profit is greater than or equal to it is taken a percentile I'll say number if it is more than zero and less than okay less than uh I say max I don't know what is [03:56:16] the maximum value see profit um we saw that California was having 76,000 profit and whereas um New York was having a profit of 74,000 I believe 76 was the some straight even with the lesser sales you know there might would be given so [03:56:32] when I say max whatever the maximum value is there in the profit that is what it is. So if it is greater than zero and less than max that means a positive value I want to show in green color I'll just put it as a green maybe [03:56:45] I can go with a very dark green yes okay and if not I just add another rule here telling that if it is greater than or equal to a number which just says minimum I don't know again what is the minimum value we saw that Texas was [03:56:59] having the dark red color which was around 25,700 and something around 20 minus 26,000 minus 26,000 instead of telling that minus 26,000 or whatever it is. So I say if it is greater than the lowest value [03:57:12] and less than zero that is between the lowest value to less than zero I want that in the red color again I'll go with the darkest red color in the world [03:57:24] the darkest red color in the world okay say okay now we can see this particular chart what is happening here is I can see wherever we have a profit and wherever we have a loss okay so I can just show the profit in the tool tip [03:57:38] I can show the profit also in the 12th period. not one or two places we have a negative profit. We have many many many what do [03:57:52] profit. We have many many many what do you say um uh states where we have a negative profit. So if you look into that almost around 30% of the states looks like negative profit. We see that Texas has minus 26,000. Florida has [03:58:06] minus 3,400. Um we have what do you say? Oo has Um we have what do you say? Oo has -7,000 profit. Uh Pennsylvania so -6,000. So we see that oh there are many states where we have a negative profit. [03:58:20] So that means as a data analyst or a business manager so I know that we are running in a loss almost 30% of the states we are running in a loss. So that means I have some work to be done. So what is the work to be done that we will [03:58:33] see in further okay see we have a long way to go as I said you know the eight take additional sessions and I'll do more okay but I want your support guys that's it okay we'll try to do lot more things so here how will you correct this [03:58:48] one so as I said in the Texas there might be some products which is having a positive profit there might be see if it is 20 minus 20 5,000 for 1 lakh 70,000 is almost 8 to 10% negative profit we are thing. Okay. So that means there [03:59:02] might be out of 17 different types of categories of products there might be would have been having a negative profit. So at least some two three products would have been positive. What are the products which has having a [03:59:14] bestselling product. So those things only you try to sell here and those things which has a negative profit try to see those those products how they are they're performing in the neighboring states how they have been performing try [03:59:26] to shift over there and sell that over there and with those products which is it's a positive there might be some products might be having a low profit okay a low profit so those thing you try to push it into the see that products in [03:59:40] to do that interchange supply chain management systems so that things can be done so that we can study further okay see as of now we are learning one by one charts so later we'll see how do we collaborate this charts Okay. Slice and [03:59:53] dice. What we do in the this one? No, Excel. We try to do those things. [04:00:06] in my regular Excel classes. No, we do this PowerBI. So we'll do much more than this one in PowerBI guys. This is an Excel what I did. So we can see oh we data. We can see what is the total sales, what is the total profit, what is [04:00:19] the profit percentage, how each product has been performing, how each region has been performing based on the mode of shipment and so on. Now I want to see each year how their performance is in 2015 how their performance is. When you [04:00:31] see in 2016 how their performance is and you can see accordingly all this particular charts everything is getting updated. In 2016 we see chairs are in the top leading okay and we have a total sales of 6 lakhs 9,000 and we have a [04:00:44] profit of 13%. My overall profit was only 12%. So when you see 14 may we have 10% profit. In 15 we have a profit of 13%. Even though profit sales was less furniture how things have been happening in the furniture how office supplies has [04:01:00] been performing or technology and furniture together how they have been been performing. So we are able to do all the slice and dice and all these things. Okay. uh this is something like you know what you have done this in your [04:01:14] Excel classes okay uh in my Excel batches what I did so I used to cover this things also this was just a simple dashboards we just created okay so like interconnected and not only that in your powerbi it'll be connected between the [04:01:28] visualto visual also okay action filters will be there so we have a long way to go so as of now we are just learning one by one see we created a separate what do collaborated all the p tables and we interconnected that that's it of this [04:01:42] you have done this in Excel. Okay. So now okay uh see similar kind of see this is nothing this is small thing it's much more than this more powerful dashboards than this what you do in excel we'll be doing it in powerbi see according to him [04:01:57] also here charts here also we have something called as a shape map okay and this again I'll take a location as I can take anything any geographic field I'll already familiar with this so you can see all the states have been filled with [04:02:10] the color okay so here we have something called as a color saturation Okay. So here I will take the sales. When you take this one, so you can see When you take this one, so you can see it is just filling this one based on the [04:02:25] it is just filling this one based on the sales. It is same like what I did in um Excel uh just some time before I did here. No, I did here is it? So what I did here it is like filled map. We call this as a filled map. Okay. So it has [04:02:38] been filling. It is not having a bubble but the complete state is being filled with a color based on the total measurable value. That is what exactly is happening. We see that California is the highest dark color. So highest [04:02:50] sales. Then we have a New York. Then we have a Texas. Then we have Washington. Then we have Florida and so on. We see that we have very low sales coming from this. Okay. And we can also add instead of this you want to add legends. [04:03:03] are going to be a dimension. Say region wise you want to fill the color. I can just do that or color saturation itself if I say region uh it is going to show me that region wise which region what all the states comes into what region [04:03:17] count of region it is taking okay fine so now if I just take this particular cells see one thing here what happens is uh this is a very good chart this is a lightweight chart because it doesn't have too much of that kind of uh what do [04:03:31] have too much of that kind of uh what do I say geographic u like you know lot of background city state and all the things we don't have that kind of a clumsy things. It's very neat and clean. It is very neat and clean. But in this chart, [04:03:44] we cannot have second dimen a measurable value nor a second dimension. Okay. Um I just want to know in California segment wise how it has been category wise how the sales are happening or what is the profit? No, we can either show sales or [04:03:57] we can show profit because we don't have a bubble there. Since we have a bubble, of the bubble and the other is the color. But here we have only color. So we have only one thing here. So when you don't have to second dimension or a [04:04:10] second measure in that case I feel this is the better one because it looks very neat and clean compared to this particular kind of a graph. Okay. It's a very lightweight also. Okay. And looks very neat and clean compared to the [04:04:22] other but doesn't support a second dimension and the second measure. But when you have a second dimensions and we have a second measures I think this is going to be a better one. Okay. And not only that you know you can [04:04:35] also go with this particular maps you know in this case of maps we have the map settings or aerial view you want to have it or you want to have this particular light view dark view which you cannot do it in the other maps. So [04:04:47] since it has all the features this are little heavy okay this is little heavy but at the same time it also gives you more features kind of a thing. more features kind of a thing. Yeah. Then by how you want to [04:05:02] Yeah. Then by how you want to cool. Yeah. Clear about this one. See for this you know we can even add a slicer and then we can just filter it. I can just keep a slicer. We'll I think maybe after the [04:05:15] break we can talk about the slices kind of a thing. So I can just have a a region or something. Okay. And I just want to see only the central region. Yeah. All the charts are being shown accordingly. [04:05:28] Now within that region how they have been performing we are able to see that been performing we are able to see that okay now um we'll move ahead here see I think we did discuss about the cards in the day one okay if not we'll discuss [04:05:42] about the cards uh what is a card is nothing but used to show the summarized value is used for displaying the value is used for displaying the summarized value okay if I have to go to [04:05:54] the card so we have this thing called as 1 2 3 [04:06:26] Uh just get into this one. Uh there is this 1 2 3. You see here in the insert visuals we have this 1 2 3. This is for displaying [04:06:39] the card. Fine. Okay. Now uh we will see with this particular concept here uh of uh displaying this tree map. Okay. [04:06:52] We have seen with the map. We have seen with the map. Next is we'll see with with the map. Next is we'll see with tree map. Okay. Uh when I go to the tree map, this is your tree map. Okay. This is your tree map with a collection of [04:07:04] rectangle boxes. We call this is your tree map. See here, this one is your tree map. See here, this one is your tree map. This is your tree map chart. We will go with what you call it as a tree map chart. [04:07:20] tree map chart. What is this tree map chart? Okay. Now when I say tree map, a tree map is a collection of rectangle boxes. This chart has a collection of or rectangle boxes. [04:07:35] So first thing is when I say tree, it is not looking like a tree. You know how a tree is a tree will have um main branch then we'll have sometimes will have a further subbranches and the leaves. Okay. uh [04:07:51] in that. Then some branches will have a sub branches and the sub branches and then they'll have a what do you say leaves and fruits and all those things. strongest branch or the branch which has the more number [04:08:06] of fruits. Okay, I want to look into that particular things. Okay, so now see it doesn't have a shape of a tree but still it is called as a tree map. Okay, still it is called as a tree map. Okay, because this chart is mainly used this [04:08:21] chart is mainly used to represent hierarchical data. What did I say? This chart is mainly used to represent hierarchical data. What is a [04:08:33] hierarchical data? Hierarchical data is something which is showing the levels. We have a category within the category each subcategory. Now we have a state of wise how they have been performing. I [04:08:46] want to look at that that is where exactly we use okay the main purpose of going for the tree map is to represent the hierarchical data. So make a note of it now I will come to that hierarchical [04:09:00] data first we'll see understand how do we work with this I have added a tree map by just selecting the tree map. Okay, here I'll just take the category and the values. Category obviously should be a dimension and values has to [04:09:13] I'll take it as a sales which we are using commonly for a measure. Okay. And I'll just take the category say example I'll just take the region. I take the I'll just take the region. I take the region. Now here when you look into this [04:09:27] out of my data in my data I have four regions. I have four regions. Now this particular tree map is divided into four regions and which is represented in the regions and which is represented in the form of a rectangle boxes. The regions [04:09:41] form of a rectangle boxes. The regions are represented as a rectangle boxes. Now when you see this rectangle boxes they have been just arranged okay in the different shapes but it's a rectangle either is horizontal or vertical. Now [04:09:57] they are of different sizes. They are of different sizes. Yes, they are of different sizes. Okay, sometimes it's a noticeable, sometimes it might be not noticeable. But you can see the box which is a big size is the high sales [04:10:11] value because we have taken one measure and one category. Category is my region. Okay, the boxes are divided based on the region and it has been sized based on the measurable value. What is the measurable value? Sales. The uh region [04:10:25] which has the highest sales has a bigger box. The lowest sales have a small box. Fine. The what do you say region which has the high sales have a big box and the region which has the low sales have a small box. Okay. According to that I [04:10:40] understand that west region is a top leader. Okay. Now one thing is a confusion here is I see that um um this both boxes look [04:10:53] alike the size wise. Yeah. If you exactly measure this will be a bigger size okay than this. Okay. But generally when you see the from the far okay first when you see the from the far okay first view when you see hey um they both look [04:11:06] alike or mean size- wise okay so if they you feel that you know they both are of the same size in that case what I do is hey the box which is in the top we see the high sales and the one which is in [04:11:19] the bottom is the low sales okay high sales and the low sales low measurable which is in the top is high and one which is in the bottom is low and when which is on your left hand side is the high and the left right hand side is a [04:11:33] low. So on that basis when you see the central and south region central has done a better than the south region. So one is like you know it's not only the size of the box it is not only the size of the box it also the position of the [04:11:47] of the box it also the position of the box how it is being placed. Clear? So by knowing this information I can clearly say that the west region is the top leader here. It has done the highest sales compared to all the regions. Agree [04:12:01] with me? See if you want you can show the values. values here. How do we add a values? Hey, see data labels here which shows me the values actually. So we can see there. Okay. Um we see that you know [04:12:14] west region has contributed 725k, east has contributed 678k and the central has has contributed 678k and the central has contributed 500k 391k has sadik. You know I don't like the numbers. Okay. I always say a good chart is one with [04:12:30] without seeing the numbers without reading the numbers. If I can read the numbers and then understand then what going for the charts even we can have a tables tables with the conditional [04:12:42] formattings gives you really amazing things okay but still for your sometimes the company manager says hey sadik I want to show them this one I the day I want to show the numbers also yes labels are there one single click [04:12:56] the labels will be displayed okay got it this one understood so that means it's a rectangle of boxes uh thing and the way the it's the size and the position based on that we can decide [04:13:09] which has the higher and which has the lower value. Okay. uh one thing I see in this one is one thing I see in this one is okay uh I don't think so this Google [04:13:21] also will explain you this or chat GP also might okay chat is far better than me I don't deny that okay I learn a lot of things from chat GP is really amazing thing okay but sometimes you know I try to uh this is with my experience I try [04:13:34] to give this additional info I say okay now when you see here we don't have something called as x-axis y-axis Axis scale values. In the axis we have something called as a access title. This is sum of sales. Then we have the [04:13:48] is 1 million, 2 million, 3 million or whatever it is. And the same way we have a title here and then we have a axis here and then we have the values also next to that you know east west or whatever it is lot of things but in this [04:14:01] case in this case in this case there is no axis there is no x-axis there is no y-axis there is no there is no x-axis there is no y-axis that means this particular charts can have any [04:14:16] layout I can have it horizontally I can even have vertically I can have horizontally, I can have vertically, I can even have diagonally. If it is left hand side is high sales value, right hand side is a low sales value. So [04:14:31] when I just keep it like this, okay, the one which is in the top is the high low sales. You can see automatically they are just getting okay reshaped kind of a thing. So that means I can use this as a legends also. Okay, I can use this [04:14:46] as a legends also. Okay, I can use this for filtering also. Okay, but okay, we can do that. Fine. So that means if I just have a see that means this just have a see that means this particular chart is can get fit in any [04:14:58] space in the top. You want to place that. Yes, you can do that. Vertical space. Yes, you can do that. So it can fit in any size. Okay. So that is one additional things what I generally see what I have understood compared to the [04:15:11] other charts. Okay. So this information I did not get from the YouTube or Google or anything but my with my experience I got this. Okay. So I don't deny I have not not done so might have not done too much of research in the YouTube and [04:15:23] understood this is something which is different from the other charts apart from that apart from that I said the main purpose of going for this chart is this chart is for representing the [04:15:38] hierarchical data see if I have to go with a single dimension I can have this explanation with the column chart this gives me a far better information than the tree map is it okay this gives me a far information better than the tree map [04:15:52] I don't deny that okay but um one good thing what I see is I don't have a x-axis when you see in the column chart we have this kind of x-axis title labels here you can see if this is your chart area this is your chart area and only [04:16:07] this much is your plot area only this much is your plot area okay and you can see the areas has been wasted here it has been wasted here you have so much of wastation here. So which we don't have it in the this one. Okay. Uh in the case [04:16:21] of this one, we don't have that. So we can resize to without compromising the plot. I can resize to whichever the dimensions you want and so on. So so okay. But I said the main purpose of going for this one [04:16:35] is hierarchical datas. So what do you mean by hierarchy is we understand that west region is the top leader but within the west region how the each state has been contributed which state how much they have contributed. Okay, in the west [04:16:47] we have a California, Washington, Oregon. How much California is contributed? I want to know that one. So what I do is here we have something what I do is here we have something called as a details. Here we have [04:17:00] re we have done the region. We have done the values. We do also have something called as details. In the details we can give the second hierarchy. Under the region I want to see the state. I'll take the state. As soon as you add [04:17:15] states here. So what is that we see here? Hey, I know that west region is top leader. But again in the west region, we see that maximum business is coming from California. Okay, if this is my west region, okay, is this this is my [04:17:31] west region and you can see um this might be something 50%. more than 50% more than 60% more than 65% I think almost 60 to 70% of the business is in west region is coming from California alone okay um then we see Washington [04:17:48] other 10 to 15% we see that you know we have almost around 15 different states have almost around 15 different states in California but um we see that um um sorry we have almost 15 plus states in the west region but out of that the [04:18:03] California and Washington they have almost given me 85% of the business or more than 85% of the business. Remaining 10 12% or 15% of the business is been [04:18:15] contributed by Arizona, Colorado, Oregon, Nevada, Uta, North Dakota, South Dakota and things all this 10 different 10 12 different states they've hardly contributed around 10 to 15%. Okay, the 11 1% business. So that means but you [04:18:30] know California and Washington they itself has contributed more than 80 85% of the business for me. Okay. So we see that west region is a top leader because we have California and Washington over there. Okay. You know India is strong [04:18:44] College is there. India is one of the best team they say. Is it? Yeah. Okay. I'm I'm just telling an example. I know India is best even without them also but India is best even without them also but yeah we always depend on that rat ro is [04:18:59] there ro you know they say India is the top team kind of a thing nobody can beat India okay so something like that okay and you see we understand the west region is a top leader because we have a California and Washington so they both [04:19:12] are contributing 85% of business the remaining 15 states are just contributing only 15% of the business okay when it comes to east region yeah Again we have a kind of a um a big player here. So say New York almost not [04:19:26] 50% but less than 50% of the business is coming from New York. Okay. And we have peninsulven around 10 15% business. U around 10 12% business is coming. So we business is coming from these three states. Okay. So we have some kind of a [04:19:39] top leader. So we see that. So see as I said there are two things here. One is the size of the rectangle boxes and the way it has been present. So if it is on on right is low sales compared to when you compare between this California [04:19:53] clearly say that. So when you compare penicellin and oo okay Pennsylvania p Pennsylvania has done the higher sales than oo okay New York has done better than Paul so we can see it's a big box okay and we see that this three is been [04:20:06] having almost look like alike but we see New Jersey has done 35,000 okay um this particular has done 28k and here we have 27k okay so you can see higher sales lower and so on okay but in the central we see the Texas but not that big player [04:20:21] okay higher still I mean coming from the Texas 170k then alenius then mikan and so on but we don't have that 50% or something contribution no uh the same way when you see that south region we have all the states are coming but [04:20:34] Florida so we are able to see two things here which region is top leading and much they have been contributed or which state is the top leader so we see that the west region the highest business coming from California then Washington [04:20:49] then Colorado and then the other states so the same way in the east region we New York. Okay. Then we have Pennsylvania and so on. In central we see the Texas has given me the top business. [04:21:02] Got it. Hello. This is where exactly we use this particular tree map chart. And this looks more of coloring the boxes but not like chart. Hey. Oh um you [04:21:19] know slicers slicers again is a very um thing which is there in Excel no in the p tables in the dashboards dashboards what I showed there this is completely this is self is a slicer guys this itself is a slicer no [04:21:34] wait what we have this furniture it's showing only the data of furniture when I say technology it's showing only the technology sales office supply sales everything is getting sliced all those particular things are getting sliced is [04:21:47] particular things are getting sliced is So this itself is a slicer is it this called as a slicer. No in Excel you know this. No these are called a slicers guys like you know we have this no hey um so we go to this what do you say the insert [04:21:59] p table and we call this as a slicers. What is the job of the slicer there use interactive way of filtering the data. That's it. This is a slicer. No these added here. So now what happens is like you know when you do a slice you can see [04:22:13] Yes. All those visuals are getting filtered. That is the job of the slicer. Okay. See nothing new. See I think almost whatever we have done till now is we can do it in Excel. We have all done in Excel. [04:22:30] PowerBI. We can publish the report. We can share you get. We have something as a better role level securityurities which we'll be learning. DAX functions are amazing. We'll be learning. Uh power query also [04:22:43] we'll see how do we do the power query and things there are a long way. Okay. First we'll see about the slicer. Okay. Um when I go to the slicer how do we go [04:22:55] to the slicer here. This go to the insert or you can see this symbol here. insert or you can see this symbol here. This is a slicer. [04:23:08] icon. So that means it clearly says hey this is a slicer. This is called as a this is a slicer. This is called as a slicer. I'll just include the slicer. slicer. I'll just include the slicer. I'll include the slicer and I say, "Hey, [04:23:21] I want to see this information." I just take this. Okay. And I want to slice it. U So now I can in the slicer, we generally add a dimension. But not only dimension, it can be used for anything. Measures, states or anything. Let me add [04:23:35] a dimension here. So let me just take a category. Okay, I've just added a slicer. I've added a slicer and we have the field here in the build. It says slice it. Say example I want to go over the category. You can see here we have [04:23:49] three categories. We have three categories and shows me hey you have three categories based on what you want to show the data. Say example if I say furniture. So what is happening? This both are getting updated. Okay. So [04:24:02] they're getting sliced office supplies you can see. means um so you can see this particular chart also is getting the updated [04:24:15] information when I say for uh what do you say furniture so we had a sales of 4 lakhs 57,000 but furniture is only 156k okay now if I just say technology so okay now if I just say technology so technology you can see it has only 127k [04:24:30] oh east is doing good in technology okay uh east region is doing see when you see see the changes here so west region was doing Good. But when you say technology, east is doing better than the west region. Okay, that is what we are able [04:24:44] to understand from this one. And then we see again in the east region, we see the New York has contributed the highest. Okay, and so on. So when it comes to office supplies, again, West is doing good. Okay, it is automatically updating [04:24:58] everything. So this is your slicer. Okay, so now by default is showing a checkbox. When I say checkbox, you can even select multiple. I want to see furniture and technology together. Can I select? Okay, I can hold on the control [04:25:13] key and select. So, it is showing the furniture as well as the technology. >> Now, the next thing is when I see your slicer, [04:25:27] it is displayed on your report. We also look at the good interface. We also look at the good interface. Okay. Uh I feel this is better. This is like a tiles. No, I like tiles. Now new technology everything is a [04:25:42] tiles. No, instead of a checkboxes, I want to have a tiles. I have a f slicer settings. Just select this. Go to the format. can see we have something called as a slicer settings. In the slicer settings, [04:25:57] selection whether you want a single selection or multi select. If I go to the single select, you can see this changes to the radio buttons. So that means you can select only one. Okay. So if I say no, I want to go with multis [04:26:09] select. It is selecting multip select. But it is without the with the control key, it is asking. I want to have a multi select with the control key. You can just multi select with control key off. Okay. So now without the control [04:26:21] key also you can do a multi select. See based on the requirement you have too selecting with more than one options you'll be selecting with multis select with without um without the control key or okay you want to have a select all it [04:26:35] shows me the select all and then you want to deselect again this will be items out of that you know you have to deselect few items you can say select all and deselect only what is not needed so based on that you can take a call [04:26:48] otherwise you can go with the default things fine okay here the next thing We do also have a slicer settings here. Okay, that was a selection option. Okay, so now we do have a slicer settings in the slicer settings. So we can see we [04:27:02] have a style. So which by default is vertical. We can have a drop-down. Drop-down what happens? It shows me just a single drop-down box and on demand you get a list of values and you can just do a selections. [04:27:14] Okay. So now I say no, I want to have a tile. We can see tile. tile is like this where you can see oh furniture office supplies you can see oh furniture office supplies technology what you wanted. [04:27:30] is showing only the furniture only the technology or only the office supplies or you have a clear filter. You can either keep vertically or horizontally and say hey what do you want and you can do the cosmetic works for this. Okay [04:27:43] like just select this and um we have a style and appearance and things. So we can give back color for colors and all the things we can do that. I say back alone color instead of white color like just say [04:27:56] borders how you wanted and then say shadows if you want to have a raised shadows if you want to have a raised effect. [04:28:37] measurable values. It can also be used even for the date fields. Let me just give an example for that. So let me just have a table with a dates. Okay. So I'll take in the rows what I will do is from the order date I'll take the months. [04:28:53] In the rows in the columns I'll try to take the year from the order date I'll take the year in the values I'll take the sales. So that means so we I'm trying to do here is each year within the year the [04:29:08] month wise how the sales are happening. So I can just format this view. So I can just format this view. I can just go to the view here and say customize the current theme. I can say text make it bigger. [04:29:42] there is a values no we can increase the font size here font size here yeah it just so what I've done here is sales are happening so we just have this data we just have this data [04:29:58] data we just have this data Now what I want is I want to apply a Now what I want is I want to apply a slicer for the date field. Okay. So I will take a slicer. I will take a slicer. The same slicer what I did. Now [04:30:12] I'll take the date field. Say example I will take the I want to slice it for a particular year. Okay. See one thing is I can just take the complete date field. If I take the order date I'm not taking any portion of that date complete date [04:30:25] field. So you can see the slicer comes like this. Right now it is like a continuous values. Okay. It says the first day of the sales is we have the first day of the sales is we have the data from 3rd of January 2018 to [04:30:38] December 30 2021. That is from the January 2018 to December 2021. We have this four years of sales data. Now I don't want to see from here. I [04:30:50] Now I don't want to see from here. I just want to see from 2019 or something. just want to see from 2019 or something. uh I say I want to see from 2019 July uh July 15th onwards 2019 July 15th onwards. Okay, you can even give the end [04:31:03] date or you can even slice it from here. Hey, what is the start date to what is the end date? You want to see only the sales data between this even you have a total sales you have a regions or even like you know map or anything. So it [04:31:15] just automatically the chart gets filtered only for this. Okay. See it's say example you have the simple data here. Okay. So maybe I'll just take the here. Okay. So maybe I'll just take the subcategory [04:31:30] sales or maybe I'm little fast here because this charts we have already done. Keeping that in mind I go a little fast in this kind of situation. If you kind of a things you know that is reason sometimes I try to ration this things [04:31:44] might be you feel that is fast. You know yesterday there was a big escalation telling that you know none of the colleagues have understood 100% sure so very confidence it was been told okay I know as it felt little kind of a scary [04:32:00] for me that's it so that that happens fine but I enjoy teaching that is what is my this one is but the way it was been killing I'm 100% sure that you know nobody understood anything from the session and it was so bad and think [04:32:13] session and it was so bad and think little kind of a I know should not consider those things. I'm not even supposed to say but uh that escalation was little um disturbing kind of things. Okay. Hey now we just have the sales and [04:32:26] all the things I can show you even the values also in this uh let me show the values also in this uh let me show the values. Okay sorry uh data labels [04:32:38] labels show this. Okay we have the values now we can see this you can see automatically they are showing according to this. So that means the complete all the visuals are getting filtered. Okay. What I mean to say here is when you're [04:32:50] We are taking the data from the April 2018 to March 2020s. of a things and not only that this data is also filtered only for that and has [04:33:05] been showing. Okay. So that is what exactly is happening. Okay children. Hey continuous values but I say no no no hold on sadic I don't want like this but particular year for a particular quarter for a particular month okay so sometimes [04:33:21] continuous values between a specific date to a specific date yeah this is the better one okay this is the better one but but at the same time I say no I just want to see okay um only a particular sales of a particular year or something [04:33:37] so I want like a tie I want like a tie so You cannot create a tile on the hierarchical datas because it has a hierarchy. It has a year, quarter, month, day on, so on. But this what I do is I take a slicer. I'm just taking [04:33:49] is I take a slicer. I'm just taking another slicer. I'm just taking another slicer. I'm taking another slicer. In this slicer, what I do is I just add a this slicer, what I do is I just add a field where from the order date. I'm not [04:34:02] before I selected the whole order date. Now I'm selecting only the y a r year. since whenever you take a numerical values by default it shows you like this [04:34:15] okay since a measurable value you know a continuous things but this you can change the slice of settings to a tile let's say I'll make it as a tile or we have this four years okay now when I hit on this 2018 oh in 2018 was phones were [04:34:30] doing the leading sales with 77,000 in 2019 we see chairs have done the highest 2019 we see chairs have done the highest sales okay with 71k in 2020 Again chairs is still continued doing in 21. We see the phones have been in the top. So it [04:34:43] is this everything is getting updated accordingly. We can hold on the control accordingly. We can hold on the control key and say 2018 to 20 18 and 20 or 18 19 and 20 for things happens. Okay. This showing the complete limited value. [04:34:56] showing the complete limited value. Okay. Fine with those things. actually what we have learned is bits and pieces. Okay. We have not learned a complete dashboard kind of a things. We are learning bits and pieces. Okay. We [04:35:09] are still in the learning stage. Okay. And once we learn see tomorrow in the power query where we see about how do we take some kind of unclean data and how know one or two sessions we can see about power query about how we can clean [04:35:24] we'll see about the DAX and then we'll this how do we create a dashboard and also we'll see about then we'll see we'll also see about some kind of a how do we publish the report and how do we [04:35:38] securities and uh I'm implementing the things okay we'll try to learn those particular concepts as we go further okay we will go with the Power Query concepts. [04:35:51] Okay. Uh today we'll go with the Power Query. Hey, what is Power Query? So, generally when I say Power Query, Power Query is one of the ETL tool. [04:36:03] Power Query is one of the ETL tool. Power Query is an ETL tool. What is ETL stands for? Uh I think I was discussing about this in the day one also. Uh ETL about this in the day one also. Uh ETL stands for extract, transfer and load. [04:36:17] to extract the data from the different data sources and once the data has been extracted it helps you in performing the transformation on that particular data. Transformation is nothing but we say it's a data cleaning operations. Okay. [04:36:31] So that means preparing the data in such a way that we can easily question on that particular data. Okay. And then um loading the data for the PowerBI desktop to go ahead and create the reports. So that means in the last four to six [04:36:45] classes we've been discussing about the data which was um not required any kind kind of a data cleaning. We directly pull the data and we understood about what is dimensions, what is measures and then we saw with the various types of [04:37:01] calculations to be done. Uh the table calculations, the percentage calculations and then we also saw with the concept of what you call it as um clearing the different types of charts. Okay, bar chart, pie chart, um what do [04:37:16] you say? Donut chart, line chart, area charts. Okay. Uh geographic charts, waterfall chart, the tree map charts and all this. So each chart has its own um um purpose. Okay. You get the extract the various informations. We looked into [04:37:30] it. Okay. So today we'll see some how do we um take a raw data, clean that particular data and prepare the data for the analysis and make um do some kind of a analysis on that particular data. Okay. We look into that particular [04:37:45] things. Uh fine. So now let us look into this particular data over here is into this particular data I've just taken a CSV file here and when you look [04:38:00] into this particular data okay uh this data is something about the sales again okay uh we have the information like order ID the product name what category it belongs to and then we have the location details city state regions and [04:38:15] things and we also have a sales value and also we have the mode of payment how the payment was being done. Okay. Now when you look at this particular data [04:38:27] when you look at this particular data here um is the data good looks good. So when you just read it like you know it looks good but from the data analytics tool okay it is not good. The reason is here [04:38:43] we can see the product name and the category has been merged and stored. So find what is the total sales of furniture. I'll not be able to do it. Nor I'll be able to find out what is the total sales of tables, chairs, bookcases [04:38:57] because um we don't have a separate product and category. Okay, it has been merged and it has been stored. So I'll not be able to slice and dice on this particular data. I'll not be able to create a tables like you know category [04:39:11] the products comes under furniture we'll not be able to do the analysis if I have to do that one so we need to have this as a separate field the product name and what do you say the category name fine so if it is there so then we can see the [04:39:28] category wise sales the product wise sales u I can group it based on the I can do that but since it is been stored like this the club and it has been stored will not be able to understand from this one. We'll not be [04:39:41] able to do the analysis from this one. Okay. Fine. Uh I have shared I have Okay. Fine. Uh I have shared I have added in this one data set. Okay. So now the next thing is uh the same thing with the location also [04:39:56] we have a city state region. So if I store it separately I'll be able to see the more granular information uh city- wise. uh I cannot create a geographic data when the data is stored like this. But the same data if I split city name, [04:40:09] state name and the region name. So I can see like you know okay um state each state wise how the sales are happening or within the states city wise how the sales are happening. I can even go with the maps geographically also we can [04:40:22] present the data. Okay. So if I'm not splitting if I'm not splitting if the data is like this we'll not be able to do that analysis. Fine. Okay. do that analysis. Fine. Okay. Uh what is the next problem here is [04:40:35] when you try to read the data into the PowerBI or any BI tools it is trying to read the it is going to read the data from the first row okay but first row and this is not the data for the analysis it is some heading let's have [04:40:49] some name and says hey what is this particular data about it's a power query demo and we have some company name here okay and then we have an empty row so okay and then we have an empty row so now this three rows in the first will be [04:41:01] on that. So that is not the data to be considered. Okay. So I don't want the top three rows is it? So it should be removed. So then fourth row whatever is there this should be my actual headings. This should be my actual headings. [04:41:17] Okay. So I'm telling what kind of a why this data needs the cleaning. And then empty row. So when I say empty row what does it mean? When I say control A it selects only the contiguous range of data. So when there is an empty row, it [04:41:31] becomes a non-ontigious range. Okay. So I don't want this particular blank rows in between. Okay. We need to remove this. Hey, we also have lot of data which is repeated. The same value has been repeated multiple times. Same value [04:41:44] has been repeated multiple times. So when I say same value has been repeated multiple times. What exactly will happen is if I try to find out what is the total sales, we are getting that as 8 lakhs 51,000. Fine. But actually that is [04:41:58] not the value. The data is repeated that has been added repeatedly. Okay. So this lakh 5,000 but actually the sales is only 35,000. So here okay so there is repeated datas here. Okay. So we have a data but we are getting a 2x or 3x [04:42:14] values because there is a repeated values. So I need to remove this particular repeated datas. You can see here also yes we have a repeated datas. duplicates. when you remove the duplicates. Okay. Then we'll get a [04:42:27] accuracy on doing the sales and all the things. Fine. else? Uh we have this particular payment method here. Uh when you look into this [04:42:42] particular payment method, um we have a payment method which is null. Which is payment method which is null. Which is blank. Okay. Which is blank. We should not have a blank palace. First thing is we should not have the blank [04:42:55] thing is we should not have the blank palace. Okay. Uh we need to um identify how we can fill that particular blank. Okay. How to take care of those particular blank cells. Now what do you feel has to be done here? Uh we have [04:43:07] something called the payment method. So when the payment is made through the or the UPI information has been captured. Okay. But uh if the payment is done through the nondigital mode I think the [04:43:21] information is not captured okay in that case if the payment method is not given I can consider this as cash non-digital mode that is nothing but the cash cash payment we can consider it [04:43:35] isn't it okay so we need to replace this particular null values what is there in the payment method in the payment method if the payment method is null so because you know the order is there location is there, product name is there, even the [04:43:48] sales value is there but the payment method is null. So that means here I can say the payment has happened through a digital mode of trans uh sorry the payment has happened through the cash. So I want to replace this [04:44:01] particular null values as cash in the payment method. by yeah um so I think that is what is needed so that okay once this particular perform uh transformation is done this kind of a cleaning is done so then we can easily [04:44:15] find out what is the total sales total sales by each mode of payment digital mode non-digital mode how the payment has happened or like you know how the what is the sales of product category wise so I can group it category within [04:44:28] the category I can subgroup it into the products okay uh I can even do the hierarchical datas of regions in within the region state within the state city wise how the sales are happening we can do those analysis only if you perform [04:44:42] this kind of a cleaning this similar kind of a thing in excel this excel also think till now whatever we did excel supports all those features yes this also is there in excel okay I generally use the same data set in my batches for [04:44:57] in excel also so that when they jump to powerbi becomes very easy to understand so that is what is the thing I follow when I go with Excel fine power query concepts was there in Excel it's not a new thing [04:45:13] it's not a new thing now okay now is it fine uh we'll try to do this in PowerBI here uh I'll just open this what do you say PowerBI and since that my data is in the CSV format I'll say get data from the [04:45:27] CSV format I'll say get data from the other sources select say connect [04:45:41] go to u the data set where this data is present I think um it is present in this present I think um it is present in this one I need to read the data sales data CSS we have shared this in the chat okay so I'll just try to open [04:45:56] the chat okay so I'll just try to open this data. So it is it okay. See now uh what did I do? Let me [04:46:09] do it from the beginning. So I just go to the get data. So we can see here when I just go to the more as I said 100. So when you scroll this it is really more than 100. Okay. Since I'm reading the data from the text of [04:46:22] the CSV file. I'll just select this and I just say connect. Okay, what is the data source that you are using? Okay, so I'm using this [04:46:37] particular what is say sales data dot CSV file. Okay, selecting the respective file. Selecting the respective file and say open. [04:46:52] files, it gives me a preview here. It gives me a preview of this particular gives me a preview of this particular file which just says okay what are the columns it has column 1 column 2 3 4 5 respectively and also we can see there [04:47:06] is what you say the blank rows in the top three rows in the top which is not required for us and we see that we have the data something like this so this has to be split this has to be split and the same thing is your location details so [04:47:20] this again has to be split into city state region Okay. And again when you look into this particular thing here, so we have a null values here. So we don't advise to have a null values. Okay. Should not have and also we see [04:47:37] that you know there is a blank rows. Okay. The row itself is blank. Should not be there. Okay. And we need to even promote this as a header. This should be my headings. Okay. So this should be my heading columns. [04:47:50] So that means this file is can be analyzed but not in this particular way. We need to perform this particular corrections and only then we can analyze it. Got it? Okay. So we understand that this [04:48:05] looks like like our answer scripts. No. Uh when you have written some answers and we give it to a teacher, teacher comes out and says hey no this should be like this. It should be like this. And she uses lot of red ink. Okay. Um so [04:48:20] this looks like my answer scripts of the school days. Okay. Yeah. So this is not particular things. We need to convert this uh reading into the blue ink or the this uh reading into the blue ink or the green and then uh I need to go ahead. So [04:48:34] You know you cannot be promoted to the next um class. Okay. If your ads script next um class. Okay. If your ads script has so much of rating correct. [04:48:47] promotion, you need to remove the headings, you know. Yes, I said this should be my headers. So I don't want this first rows. I don't want this blank rows. Okay, you need to replace this null values. So I don't want this merge [04:49:02] cells. Okay, so I need to perform the cleaning. That's it. I just did not do anything here. I was just just using the annotations and telling like you know uh this is how my answer scripts used to be and the readings is not good. So if you [04:49:15] have a readings scripts will not be promoted to the next class promoted to the next class is it okay? So now but you know even though we have the data something like this which needs lot of cleaning. So [04:49:29] this which needs lot of cleaning. So here so in the real time what happens is um you will never get a clean data like what we did in the last four sessions. Okay. So we're using a sample super store data which was not having those [04:49:42] this kind of a uh cleaning required there. It was almost a clean data and we directly went ahead and did lot of analysis on that various different types of charts. But you cannot do those kind of analysis in this. So if you have to [04:49:55] do that one I have to do this much of corrections. So now let us see how do we correct this. So that means I need to perform the data cleaning or data transformation. So for that we have this option here called as transform data. So [04:50:09] load data. So when you hit on the load data what happens the data gets loaded to the OB desktop. Whereas the transform data what it does is it takes this particular data to the power query editor. Okay. Where I can perform the [04:50:25] editor. Okay. Where I can perform the transformation on this particular data. Okay. I'll just hit on this particular transform data. [04:50:43] perform this particular data cleanings over here. So now as soon as you hit on the transform data when you click on this transform data what exactly has happened here. What you can see from this one is okay uh first thing is we [04:50:57] see that the data is loaded to a new editor new window called as power query editor new window called as power query editor okay and in the power query applications which has this particular concept of ribbons and the tabs. So here [04:51:13] also we have a ribbons and that various tabs in that. So which provides a various tools with the help of which I can perform the data cleaning. So as I need to split this particular columns. I need to remove the blank rows. I need to [04:51:27] this generally in the programming language this data cleanings is done in the programming language. I do this in Python language. Okay. So I do lot if I have to write a lot of coding for this. So now here or VSA is no problem. So you [04:51:42] can do all the things and I will generate the code to you and you don't do all the things with the help of the over here. So we have something called as a remove rows, remove uh columns, uh [04:51:56] the columns, append the columns, you want to split the columns. So you want a splitting and all those things. No. So all those particular operations like you as a header, use the first row as a header. So after deleting this but [04:52:09] dropping this rows, you can make this as the first row and make that as a header. So I can do all those particular data cleaning jobs. Okay. So with the help of the tools so it has a various tabs under the transform also we have many tools [04:52:22] add columns we have a many tools and in the view we can see the quality of the of the column tells me what is the kind of a quality now. So the quality says 77% of the data is valid in this column but there is no error but there is an [04:52:35] empty rows of 23%. But the same thing when you just move here um it says uh when you just move here um it says uh 33% of the data is valid but 67% almost 70% of the data is not valid. So sorry it's having the null values so there is [04:52:50] no error. Okay fine. So otherwise you can see generally we see that you know in the interviews also they ask like you know um uh how do you know like you know um or what is this particular line [04:53:02] indicator? They say again so this indicator says the quality of the column. If it is green that means it is a what you say valid data. If it is a black means it's a null values and there is a red color line. So if there is a [04:53:16] red color that means it's a error. Okay. Right now here it says there is no error and it's showing the form of percentile. So if this is 50 so we can see this is less than 50% of the data is valid and more than 50% almost 70% of the data is [04:53:30] more than 50% almost 70% of the data is having the null values. Got it. Red means error. [04:53:44] quality we have this error. No error. You can see the green color is valid. Red color is error and the black error is empty. [04:53:58] Let's go to the uh view. Go to the view. Under the view we have the quality of the column. Yeah. Um next is what we have on the right hand side. We can see uh it says you can have multiple queries here. More [04:54:13] generally call that as queries here each table. So it is they're using SQL terminologies. Terminology is what is used in the database. Okay. And here it says query setting. What is the name of that particular query? Okay. See the [04:54:26] name of my file was sales data. It is taken the same names but you want to have a name for the table better name you it's a text box you can just go and edit here okay whatever the name you want you can just give this okay I so [04:54:39] why I say is it is very similar to uh the macros when you record a macros okay all the steps gets recorded what all the activities you do the same way here also steps now in the data transformations you're going to perform some [04:54:53] transformations okay so all the transformations you do that will be recorded as the steps will be recorded as a steps. Okay. And just like in the behind the screen the code gets generated and that code is nothing but [04:55:08] is called as a VBA visual basic for applications. Okay. the scripts get generated. The same way here also when you perform any kind of operation so you perform any kind of operation so it'll just capture [04:55:27] particular thing uh when you perform this one so you can see automatically a script gets generated for this okay so I'll just expand this and I'll show you okay so here you can see uh the script gets generated it says first is your [04:55:41] source from where are you reading this particular data data and after reading column with the data type it is trying to align the data type and the script what is there and this language is called as the script is written in a [04:55:54] language no and the language is called as what it is called as M language heard about this M language [04:56:06] >> machine language >> no no no no ML is machine language or machine language we say M lang ML we don't say as M language. Yeah, I know it's called as machine language but in short they call it as ML is it okay but [04:56:20] when I say M language it is called as mashup language or they call it as a mashup code or mashup script kind of thing end code mcript or m language we thing end code mcript or m language we call this as so that means [04:56:33] uh this is mainly used for okay when you say mashup mashup is what it is meshed meshed up you want to perform the cleaning on that particular thing so Okay. This is a language which is mainly used for cleaning. Okay. Okay. Fine. [04:56:51] Hey, let us move ahead. So, it is called as mashup or meshed up is it? So, it's a So, it is more like a functional language like in Excel it is starting Excel. Okay. It is different from the Excel. So, I even teach mashup code but [04:57:06] in this we don't have a time to get into the code but okay uh this is how the behind the script screen the script gets generated. Okay. So now okay this was just a walk through uh this was just a walk through. So now [04:57:19] um we will see how we can uh it is written as a mashed up language or mashup language. It's called as mashup language. Mashup ma ma mh I think mashup. No again mashup language mashed [04:57:33] up language whatever they say m language which fully called as mashup. Okay fine. Um so uh now now let us go ahead start. See till now I haven't done anything. Till now I haven't done anything. I have not done any kind of a thing. Okay. [04:57:48] Yeah. It's called as mashup language. Correct. Uh so now u till now we have done we have not done any kind of a things. So now let us see how we will do this particular cleaning. So let us go ahead and start the cleaning now. So [04:58:03] what is the first thing I said we have to do in this one. So first thing is I don't want this top rows. I don't want this top rows. First three rows has to [04:58:15] be removed. So the first three rows to be removed. So to remove the first three be removed. So to remove the first three rows, what I will do is I'll just go to the remove rows. I'll go to the remove rows. And here we have something called [04:58:28] as remove top rows. This is going to remove the rows from the top. Okay. Remove top rows. This is going to remove the row from the top. So as soon as you hit on this remove rows, uh there in the remove [04:58:42] rows. Okay. I just selected what? remove top rows because I want to remove the rows from the top. How many rows you want to remove? I want to remove the top three rows. I want to remove the three rows from the top. I'll just select [04:58:55] three. I'll say okay. You'll see three rows have been removed. And then next is what we need to do. Whatever the data is there now in the first row that has to be the headers. This has to be the header. So in this [04:59:09] to convert the first row in this particular source. What is there? that has to be the header. We have this option here called as use the first row as the header. Use the first row as the header. So when you hit on this one, [04:59:23] whatever the first row of the data is there that has been moved to the header and you can see here in the headings we have order ID, the product name, locations and all these things. Since it has promoted as a header now after [04:59:36] promoting you can see it has even got the data types. This is 1 2 3. 1 2 3 means it's a number ABC. ABC means it's a text type. Okay. So we have a data types like when you just click on this one so it tells you on that if it is 1.2 [04:59:51] that means a decimal numbers we can change this. Okay. Uh if I say like you know 1 2 3 is a whole number percentage date time will have this kind of a symbol calendar with a clock date will just have a calendar symbol. Uh time [05:00:04] will have this particular clock symbol text will have a ABC boolean value will have a true false and all this particular things. So automatically and we can see here whatever the activities we did till now it has been recorded. So [05:00:16] by default we have two. So first thing is what we did we remove the two uh top three rows it says. So table dots skip this is the mashup code. So from the table it is going to skip the number of rows is how much three rows. Okay. And [05:00:28] then after skipping that we promoted the headers. Whatever the data was there first row I made that as a header. After making that as a header, I did not change the data type. But PowerBI says whenever the promotion happens, it will [05:00:40] then it'll try to uh recheck what type of datas and it'll try to assign the data types which is being done. You can see each and every column it has been assigned with the data type. So order ID is of type in 64-bit integer. Product [05:00:54] name is of text type. Location is again the type is text. Okay. Sales is of type integer type. Payment method is again a texture type. Got it? the code. So if we have to manually write the code, we have to write so [05:01:09] language. But generally people do this particular transformation in Python. So write, how much of scripting we write and things. I don't do this even Python for I teach Python also. So I do the same things. But you know we have to [05:01:22] write lot of coding there. Okay. Even a comma miss if you miss out and dot if you miss out it becomes such a big error and to find that error to fix that error it takes hours together okay it's a very challenging thing but here everything is [05:01:38] tick tick tick all the just clicks okay fine we have promoted this as a header what is the next thing that we are supposed to do supposed to do we have to remove the black rows the rows which are black so [05:01:53] have Since we have to remove the blank rows. So it is remove where it will be So it's very English like kind of a things but we have to be creative. We are human beings is it? Since we have to remove the rows. So I have to remove the [05:02:07] remove rows and check is there any kind of thing called as remove the blank that. All the blank rows will be removed. We can see now there is no blank rows. Before we had lot of blank rows. [05:02:20] Okay fine. We have done. We have removed even the blank rows. Okay. So people not understand it. So we have removed the blank rows. Next [05:02:32] what is the next thing that we are supposed to do. I said we have lot of duplicate values. No this this is the repeated. We have a same order ID, the [05:02:46] same product name, same location, the same sales value and even the payment method everything is the same. So that means the entry has happened twice. Sometimes we scan okay the a scanner has scanned twice. You know it happens like [05:02:58] you know when you're getting into the office okay we scan the sometimes notice it automatically scans your sensors and all this thing. So might be know times. You standing in that gate for a long time you just moved a little and [05:03:12] has double scanned. Okay. So that means the same employee has entered two times uh at the same hour. So that means that has to consider as one row. Okay. So if that data has been given by the uh for the HR okay so enter employees login [05:03:27] time. So in that case in the same hour maybe if the employee has logged in two times that is that should be considered as one login. Okay fine. Um so what is that we need to do? We need to remove the duplicates. We [05:03:39] need to remove the duplicates from this. So how do we remove the duplicates here? So I want to consider the duplicate is if all the fields together same order ID the same product name same location same sales that if all the columns together [05:03:54] is repeated I want to consider that is a duplicate value okay so how do we do that one removing I just say control A so where I'm selecting all the columns and then again I'll go to the remove all sorry remove rows there we have [05:04:07] something called as remove duplicates so here I have selected all the columns the reason is if all the fields together is repeated then I consider this as a duplicate. So I say remove duplicates. Yes, it has removed the duplicates. You [05:04:19] Yes, it has removed the duplicates. You can see so before we had some 25 30 rows can see so before we had some 25 30 rows it has become to 17 18 rows. Fine. Okay. So we have done this particular removing the duplicates. Now [05:04:31] looks data looks better than the previous one. Is it okay? Um fine. So I said you want to remove the duplicates. I said uh if uh I consider duplicate if all see uh order ID product if all the fields together is repeated. So I say [05:04:46] control A. Control A is selecting all the columns. Ctrl A is selecting all the the columns. Ctrl A is selecting all the columns and then under the remove rows. Under the remove rows we have this option here called as remove duplicates. [05:05:00] Remove duplicates. When you hit on this remove duplicates you can see the duplicate rows have been removed. U so now uh we have removed the U so now uh we have removed the duplicates here. So the next thing is [05:05:13] what is that I said we don't want this blank values. We don't want this blank values. We don't want this blank values. Okay. We don't want this blank values. So if I have to remove the blanks what is that we do? Okay. So since now I want [05:05:28] to do some kind of a transformation on this particular column level. Since I want to perform the transformation at the column level. Okay. So since I want to perform the dam transformation or the column error, I'll select this column. [05:05:41] Okay. And then when you right click you get a context menu. Whatever the re relevant option with respect to that column, you get that. Okay. So there we have something called as replace values. We have this replace values. Okay. We [05:05:57] have this replace values. Okay. So just hit on this particular replace values. Even you have the replace value under the transform also. Either you click here or there. It's one and the same. Okay. So I suggest is yeah [05:06:12] right click on this and say replace values. Here what is to find and what is to replace. So payment method it is null. If it is null I want to replace with cash. Okay. So as I said so this is a [05:06:27] like you know might be if there is null I'm considering that it is a cash mode of transactions. So what I do you what to find what to find is not even a space I'll just keep it blank. If it is blank replace that with c. [05:06:42] So what did I say here is so I just right clicked on this payment method and right clicked on this payment method and I said oh I want to perform the replace. So what okay I just clicked on the replace value. Right click and [05:06:55] selected the replace values and what is that? So I set keep it blank. Okay. And if it the list we have to look for the blank values and if there is a blank in this particular column replace that blank with cash. Replace that blank with [05:07:08] cash. Replace that blank with cash. Okay. I'll just say it as okay. You can see all the blanks are replaced with cash. So now I can come to know how much transaction has happened through the [05:07:20] through the credit card? How much payment has happened through the UPI? Fine, we can do this things. Fact, this looks better. But still if I have to do the analysis like hey how the sales have happened for the category wise, product [05:07:36] wise, city- wise, state wise, I'll not still not be able to do it when the data is something like this. I need to split this. I need to split this. Is it? So now we'll try to split this one by one. The first thing is the product name has [05:07:51] a combination of the name and the product uh and the category and also we can see they have a delimiters here. So generally it has been separated with the comma is it comma and a comma space or something is it? So how do we do that? [05:08:05] Just select the product name. Okay. Go to the home tab itself. There we have this thing called as a split the column. Okay. So when I say split the column Sometimes you know I want to split the first three characters and the last [05:08:18] three characters or something. So we can split based on the number of characters, uppercase characters, the lowerase characters, based on the digit and the Yes, we can do that those particular things also. Uh but here what I wanted [05:08:33] is I want to split this based on the delimiter. What is the delimiter? Comma is the delimter. Comma is the delimter. Based on that I want to split. So I'll just say split this column by what is a delimiter. [05:08:49] What is the delimiter? Comma is the delimiter. Okay. Uh you have a left most right most. We have only one comma. So it doesn't matter. So you can even select say left most values you want to split or something. Say okay. [05:09:03] Yeah. So you can see we it got split into two things here. So one was the your category. So you can rename the setting. Just double click on this say setting. Just double click on this say katy [05:09:19] even right click and say rename is there no rename or if you double click also it'll make it as editable this is a product name and this is a category so we can see now we have a product name separate we have a category separate [05:09:33] okay the same way I'll do a replacing for splitting for the location details so I'll say split by delimiter And what is the delimiter here? Comma. And we have multiple occurrence. City, comma, state, comma, region. We have multiple [05:09:47] occurrence. So here we have this option as each occurrence of the delimter. Yes, as each occurrence of the delimter. Yes, better go ahead with this. Say okay. Okay. So we can see we got this into three splits here. So city, state and [05:09:59] the location region. I can just read. I see this region [05:10:32] particular thing. In case of a delimiter is not see um information there will be some kind of a pattern there might be space will be there or they will be hyphen or there [05:10:44] might be uppercase lowerase characters or something. So based on which we try to identify that okay so um like when they are storing all this particular information together city state region so there will be some kind of a this one [05:10:58] either you stay start with uppercase characters some things will be there so we need to identify that see I'll tell you one thing is um almost you one thing is um almost all the jobs are taken over by AI [05:11:11] agree most of the jobs are taking whatever we did in the last few classes was the visualization part I think we did see with Q&A if not I'll show you in called as there are something called as built-in AI tools also okay for creating [05:11:25] the visuals so one single click I can create any charts with the simple questions we can just prompt it and we can get the answers but I'll tell you power query is one kind of a skill which AI cannot immediately take it over okay [05:11:40] it'll take some time so here human beings are required okay so that is where like you know as Sneha was asking asking what if if the comma is not there but there will be some things like you know there might be every characters you [05:11:52] that you are going to split okay so we need to find out the algorithms we need to find out you know what all why what are the informations are there or on what basis you're splitting and so on okay so now I think we have done this [05:12:06] particular data cleaning here once it has been done so now we can see that we have so many columns it has been separated order ID product name category city state region in sales payment method and you can see there is no what [05:12:19] do you call it as null values so everything is 100 0 0 100 100 0 in all the places okay so it's not only 100 0 apart from that you know we have split the columns okay and removed the duplicates and all the things we have [05:12:34] done okay after doing this so what I will do is so here I can in the power query what I can do is only data cleaning I can do it okay so we cannot create any kind of a charts We cannot do any kind of a charts here. Okay. The [05:12:48] visualization, the presentations, we don't do it. So for that what we do? We need to say close and apply. Okay. When I say close and apply, this power query will be closed and that changes will be applied into your PowerBI desktop. And [05:13:00] now you can see the same data which was actually in CSV format which was like this. Okay. And the way it is going to be transformed and it has been brought in in the PowerBI desktop is something like this. [05:13:15] So same data is presented like this. You can see the difference between this data and this data. So you can see the difference here difference here the way it is. So there was no proper [05:13:27] headings itself the first thing. So we have a proper headings here. Okay. And headings with the respective appropriate data types. Okay. Um so then um what see there is no duplicates here. So there was a duplicates. So now we can [05:13:42] see product name and category is separate. Okay. Here it was merged. So it is there. So the same thing is your city location. You can see city, state, regions has been stored separately. Okay. And there was a duplicate values. [05:13:56] There was a duplicate values. So you can see there is no duplicates now. And you can see there was a null values. There is no null values. Null is replaced as a cache. Fine. So so much of cleaning is being [05:14:08] Fine. So so much of cleaning is being done. So much of cleaning is been done. Okay. Um fine. So now what I will do here is uh once you have loaded the data here I can do [05:14:21] the various analysis. Hey I just want to know what is the total sales I can get a card 422k which was the sum of that. So this 422k which was the sum of that. So this is before it was 81 uh so 8 lakhs 15,000 [05:14:35] because there was a depleted values. So it is taking a unique and then trying to it is taking a unique and then trying to find and then what it is um thing is that is doing um so I want to do the various analysis like category and [05:14:48] subcategory wise I can take a matrix here I'll say category wise sales yeah under the category you want to add a product name [05:15:04] sales is there yes we can have this so you can tell through the hierarchies and things. Yes, we can do all those kind of operations here. Yes, can be done. Okay. know city- wise, state wise. You want to create even the geographic map, I can [05:15:19] easily do it. So, I can just say geographic field. So, maybe I take a state and the bubble size. I'll take the sales how the sales are happening for each [05:15:32] how the sales are happening for each state. I'll just take the city okay it's a hierarchy now so you can see [05:15:44] it with the hierarchy within the state city wise how the sales are happening so we see this is Maharashtra Mumbai and this is Karnataka Hubli and this is this is Karnataka Hubli and this is Bangalore this is Canada that city [05:15:58] Bangalore this is Canada that city G A L O R E Fine. Okay. So we are able to do all those kind of analysis and if I just say [05:16:14] all the things slice and dice everything is working. Yes. That is what it is. is working. Yes. That is what it is. Okay. Now the main thing is what if if I do see um so understood what exactly you have done here is the concept clear. [05:16:29] What if tomorrow if I add a new data say example if I add a new data says 23. example if I add a new data says 23. Okay. Um so I have a pen standard in front of me. So that belongs to stationary [05:16:49] place. So we can give something like say Pune Pune or Maharashtra [05:17:02] say how much sales they like sales of 24,000 so imagine like you know I'm just given a blank row okay so I'll try to have this particular values repeated multiple times [05:17:16] so things might be in between also you have an empty row in between okay so you can see the way it has been stored it has been repeatedly stored four times in before also I have an empty row okay let me save this and um I just want to bring [05:17:30] this particular data here so I don't have to do any kind of a things I don't have a refresh button just hit on the refresh what happens here you can see we got a new product here so we can see we got a new product here and the way the [05:17:45] new product was in Excel so here what happens is how these things are happening. Okay, is that should be the question that is what I was expecting from you people. So now what happens is okay here I'll just hit on the uh see [05:18:00] from we are in the powerbi desktop now I'll just click on the transform data I'll just click on the transform data I'll click on the transform data okay in here uh we have something called as in the home uh we have this under the [05:18:15] home under the home tab under the home tab I have this option here called as tab I have this option here called as advanced editor okay and just before you You can see whatever the activities you did all the activities has been recorded [05:18:29] as a steps. So you read the data from a source. We changed the data type. We removed the top rows. We promoted the headers. Uh after promoting it, change blank rows. We removed the duplicate rows. Replace the values. Okay. [05:18:43] Splitting the columns. Change the data types. Rename the columns again. Split the columns and all those things. Whatever the activities we did, all the activities has been recorded. And I said every steps is been recorded as a mashup [05:18:57] code. Every step is been recorded as the mashed mashup code. Fine. So now we are not a programmers. Okay. I can you cannot become a ma programmer in 1 hour or I'm not that kind of a magical person where in 1 hour I'll make you as a [05:19:11] programmer. No, I cannot false promise those particular things. But still when you see here you go to the home and click on this advanced editor. You can see it gives me the complete script whatever the activities has been [05:19:24] done. So you have first read the data. Okay. Oh courage was asking how it is linking. So you can see oh it is reading the data from this file. Okay. And then um it is performing this particular changing the data types skipping the top [05:19:38] rows promoting the headers. Okay changing the data types. All the activities whatever we did is been recorded and a code has been generated for you. Now when you hit on the refresh when we hit on the refresh there. So it [05:19:50] then it is why executing. So at that mean when you hit on the refresh this whole code was been executed. This whole code was been executed. So the code may says hey I need to go to the source data which is present in this location this [05:20:03] file bring that particular file then promote the headers keep the first rows. Okay promote that blank rows and all the things. So whatever the activities we did so all those particular code has been executed. So now you are you can [05:20:17] you have written this much of code even though you have not written the code but actually the code is worth you okay and you can tell your boss oh I spend so much of time in writing the code. So you hit on the transform data transform data [05:20:31] will load the data into the power query editor. Okay now so you can see I have to remove the first three rows is it? So okay and row and then we need to promote. So for that what I do I say remove rows what [05:20:47] top rows I want to remove the rows from the top it's very simple English words okay so remove the rows from the top say remove top rows so it'll ask me how many rows you want to remove three rows say okay three roses from the top has [05:21:02] been removed and then what I do whatever the first row is there this should be the header so we have this option here called as use the first row as the called as use the first row as the header [05:21:16] on this. So I just told you. So I just have a data something like this. I just want to uh read this particular data into my uh read this particular data into my what do you say the PowerBI. Okay. And I [05:21:30] just want to do the analysis on this particular data. particular data. So what I will do here is [05:21:48] you showed us earlier before giving us more info. more info. Um I think I'll stop reading the take up all those things in the last 20 minutes. Um so let me just treat this [05:22:04] particular data. So for that I'll just say get data. Uh the data is in Excel file. So I'll say Excel workbook. Fine. I'll just select this. Say open. [05:22:24] name. I think that is a sheet in Excel. So this is the data that is what is the data, I'll not be able to find the total sales. So, I'll not be able to find the total sales for a particular [05:22:36] category directly. You cannot do that. Okay. So, I want to prepare the data so that I can do those analysis. I'll hit on the transform. [05:22:54] see uh the name of the sheet is sheet one. So it is coming something like this it is since we already have a name. I'll go with this name itself. So now we have a category product okay under the category and then we have a monthwise [05:23:10] sales. So I feel this is more like a ped data you know what is p no p is like you know transposing the data into two dimension rows and columns. Okay so that are transforming into the row by columns. That is what exactly it is [05:23:25] looking here. So I need to unpivot this data. Unpivot is what? data. Unpivot is what? The data which is actually in the form of columns the sales you know January, February sales, March sales which is [05:23:40] something like this columns I wanted like this suppose hey bookcases for the month of January, February, March, April, May what is the sales? Okay. um do you say chairs what is the sales in the month of January, February, March [05:23:55] every month all sales. So I wanted something in that particular way. Okay. So how do we do that one? So how do we do that one? So for that what I will do do that one? So for that what I will do is okay so I will just take this what [05:24:09] you say the source data. I will take this particular sorry uh I just have this particular data. No. So I want this January to December whatever the data is there sorry January to October I want to transpose this and unpivot this what do [05:24:25] you mean by unpivot unpivoting is the data which is in the form of columns I'm converting into the form of rows I'm converting in the form of rows okay converting in the form of rows okay thanks so I can select like this uh [05:24:38] purposefully I want I know you people will not be away from the keyboard so purposefully that is what I've done. Okay, I'll share it immediately after this. I've selected from the January to October and I can unpe the data from the [05:24:54] transform tab. From the transform tab, columns. Okay, I can do this. But one thing I'll Okay, I can do this. But one thing I'll do here is see whenever you do something [05:25:08] your architecture things has to be for the future reliable. Okay, we always look for that. What if tomorrow November data comes in? Okay. So that also should be accommodated is it? So when I'm unping instead of telling as January to [05:25:25] October I can also do the other way around. Now I can select the category and the product. I can select the category and the product and unpee other than this column. So that means actually I want to unpe only the January to [05:25:38] January to October selecting and doing it, I'll just do it like this. category and the product name. I'll select this and say unpee. Don't unpivote this column other than this selected. Okay, these are non selected columns. Unpote [05:25:51] these are non selected columns. Unpote that. So I'll do that way. Okay, so what I will do here is I'll just go to the unpee columns. There we have this option unpee columns. There we have this option here called as unpee other columns. We [05:26:06] have something called as unpee other columns. What this will do? This is product name. But other than that what are the columns are there. So that is going to unped. [05:26:18] So what you can see here is we have this particular each category. Okay. It says for the month of January what is the sales? February, March and okay again you can see this particular product says in the month of January, [05:26:33] the month wise how the sales are happening. Each product for the month- happening. Each product for the month- wise how the sales is happening. wise how the sales is happening. Okay. So what is happening here? Now [05:26:47] now what uh I have done this. So this is nothing but sales value and this is the month. I can rename this. I say this is the sales month or I say yeah sales the sales month or I say yeah sales month [05:27:02] sales value sales. Total sales. Now if I load this data I can try to find out what is the total sales. total sales for each month, total product wise for a particular month. I'll be able to do the analysis. So what [05:27:16] I'll be able to do the analysis. So what I do, I just go to the close and apply. one the way it is being appeared it is only three columns. Instead of that 10 [05:27:31] 12 columns, you know, 12 columns, it is only four columns. Now when I go here, okay, we have a sales here. We have a sales. So if I take a sales, it tells me what is the total sales. 1 million. But I want to see the sales for [05:27:45] each category. Each category wise, how the sales have happened. You're able to see how much sales have happened for each category. Cool. You're able to see that. So within the category, I want to see the product. I [05:27:57] can go with this particular little options here. So category and the product, how the sales are happening. Within the category, you can do all the slice and dice and things. Yes. Fine, we are able to do this. So now I [05:28:10] want to apply a slicer on this. So for the month of January or February, how sales have happened? Yes, we can do that. So I can add a slicer [05:28:30] data. If you're able to do I can learn from you. from you. Okay. [05:28:43] the sales are happening. So we can do that say January or July or something. So the sales for that particular month. Okay. Or month wise sales you want to see. So we can see that also like say just add a table. I say sales month and [05:28:58] just add a table. I say sales month and the sales value [05:29:10] you want to have further divide like you know so I take this as a matrix so that you want to go with the category wise you want to divide it yes we are wise you want to divide it yes we are able to see all these things [05:29:26] on this one which you could not do it on this particular data I do the same thing in excel and do it so people find it very easy see generally what happens is in Excel also we have this option here take this data to the power query and [05:29:40] take this data to the power query and perform the same transformation this in excel so this is the excel power query and here what I do I do the same thing selecting the category and the product name and we have this particular [05:29:55] transform and we have unp the other columns so it has been unpivoted I just say this is nothing but the month and say this is nothing but the sales S and say this is nothing but the sales S A L E S and then I just say uh close and [05:30:09] apply. Just load the data. The data gets loaded to Excel and you can see your actual data was like this. Okay, your actual data was like this. But your um [05:30:21] have a transformed data like this, we can very easily we can do all the slice and dice. Uh we can easily very easily do all the Uh we can easily very easily do all the slice and dice. No. [05:30:45] lakh uh 10,000. Fine. Okay. 1 million 10 11 lakhs is it fine. So I want to see that for the month wise. Yes. And I want to see the column category the same thing. So you want to apply the slices for that product wise whatever it is. [05:30:59] Yes. You can do all those things. Same thing what we did in Excel only we Same thing what we did in Excel only we doing it here nothing new okay would have done this please recap your videos what you have done uh it should be there [05:31:12] I think uh you would have missed out okay um but otherwise it's easy otherwise you have understood the concept um so I'm reading the data it's a excel file [05:31:32] data. Uh what is that? Viewed sales data. That is the name of the file. I'll data. That is the name of the file. I'll just say open [05:31:46] select the sheet. I'll just click on the sheet. So now you need to select this sheet. So now you need to select this particular sheet. Say then since I need to transform this data since I need to transform the data I'll [05:32:00] since I need to transform the data I'll click on this transform data transform data what I will click I'll click on the transform data when you click on the transform data the [05:32:15] when you click on the transform data the data gets loaded to your PowerBI power query editor When I click on the transform data, When I click on the transform data, the data gets loaded to the [05:32:33] power query editor. Here I will perform the transformation. What is the transformation I will do? See I can even do like this January to See I can even do like this January to October. January to October. You can [05:32:46] select the data. We can select the data from January to October. Select the data from January to October. How? Click. Hold on the shift key. Go to the end. [05:32:59] Hold on the shift key. Go to the end. Okay. Without releasing the shift key, select on this. I hope you know how to select this multiple columns. People who work with Microsoft Word at least know the selections and all. [05:33:18] Excel also. multiple columns and so on. Then what we do? Then what we do? Click on transform data. There we have unpo columns. [05:33:31] Okay. Unpumns. You don't have to go to the other columns because I've selected the other columns because I've selected this columns only. Unped columns. Unpumns. We'll click. Yeah, just a click. And you [05:33:45] can see automatically it has been done. So what you do is that's it. You can So what you do is that's it. You can rename the column. This is month rename the column. This is month and this is sales. [05:34:11] Close and apply is in the home. Just if you close also what happen? It'll ask you do you want to apply the changes? Yes, that's it. So, the data comes here and it applies the changes and comes here. [05:34:23] Even if you close the Power Query, it also it'll do. Okay. Then you have can go ahead and do whatever the operations you wanted. You can see the data is something like this. So, you can say what is the total sales? [05:34:36] Yeah, card is there. And so, I just want to see the total sales for each month wise or something. So, we can go ahead with all different types of things. So monthly sales. Yes. Monthly. All the sales are [05:34:51] happening. So you want to just add a category or So you want to just add a category or something. [05:35:05] do it. Okay. So what is power query? Power query is just an ETL tool means it's a tool which helps you to extract the data from the different data sources and whatever the data has been extracted so you can perform the transformation on [05:35:18] that transformation is nothing but we call in simple terms we can call this as a data cleaning so cleaning is the data which is not ready for analysis okay where you cannot directly ask a question uh Excel because there is a schedule [05:35:33] issue from single okay uh things I just talking about the power query it helps you In data cleaning, cleaning is nothing but uh data which is really not ready for the analysis. You're preparing the data so that I can easily analyze [05:35:45] the data. You can summarize the data. So we have seen some of the things like you we remove the duplicates? How do we remove the blank rows? How do we remove the rows from the top? How do we remove the how do we make the headings? Okay. [05:35:59] And um we have seen that how do we fill the null values replace in a specific when you want to fill the null values to a specific column just select that columns okay it's more like recording the steps we our instruction has to be [05:36:12] very clear okay u that is what we understood and we understood about unpoting unpoting is nothing but what um the data which was in the columns we're just trying to summarize it for the row wise okay we do also have a transpose [05:36:27] 90° kind of a thing so we can see some example about transpose we can see some example about text functions functions and the date functions here. Okay. And VLOOKUP and all the things you would have done in Excel. So something similar [05:36:41] to that we'll do it here also like merge and append queries and things. Okay. But um you know been better if you have done Excel kind of a things. Okay. So I feel few of them would have done excellent sitting here. Okay. Uh fine. [05:36:56] >> And with that we've come to the end of this course. If you have any doubts or questions, feel free to ask them in the comment section below. Our team of soon as possible. Thank you for joining us and keep learning.