Creating Impactful Reports with Power BI

1. Introduction: Hello and welcome to the class in full force with Microsoft Power BI. I'm Sunil Gupta, your instructor for this class. And I have 12 years of experience working for data science, machine learning, AI ended analytics. So in this class we will explore the powerful features of Power BI and learn how to use it to create stunning visualizations and interactive dashboard that can help you make sense of your data. Whether you are a business analysts, data scientists, or just someone who wants to get better insights from their data. This class will provide you with the skills and knowledge to create compelling data stories and dashboards that can drive informed decision making. So this class will start from the very basic, from downloading the Microsoft Power BI to creating a basic report, creating the canvas, adding the background to eat them, and it's chart options to the reports and then creating the interactive task force. So let's get started and explore the world of data visualization with Power BI inside the class. 2. Downloading and Installing Power BI: In this lecture we are going to download the Power BI. So to download the Power BI, we need to go to the Google and just type Power BI, download and hit Enter. You'll see the forestry chart. I'm saying first, you may see the second or something, but you have to go to the downloads Power BI, power bi.microsoft.com. Click on here. It will take you to the Power BI homepage on the Microsoft website. And here you can see various options like Power BI, extra up on my Microsoft on-premise data gateway. Okay? So you need to go to the product and we need to in the products x. And you can see our BI Desktop or be a pro or a premium Power BI mobile, Power BI embedded and Power BI Report Server for all the visualizations and the next job creation and report creation. So we need to click on the bar. Next job. And when you come to this page, you'll see that go from data inside to x. And with bargain shops and with bar get extra, create rich interactive reports with UW let analytics at your finger sticks for free. So here you can go in detail what Barbie I can do if you want to read about it. Okay, but to download, we need to click on the Download Free opsin, and this will take you to the Microsoft Store. So click on open. Microsoft Store will be opening here. So now this is the Power BI. I have already downloaded it. If you have not downloaded, you will see here, get upset. Click on the gate and it will be downloaded in your next top. So this is a desktop app that you have to download. No need to download the Dr. yet. So click on gate and it will be downloaded and installed on your system. You just click on Open here. It is downloaded and installed. Click on Open and Microsoft Power BI will be opening in your next stop. So it is pretty simple. No need to download the dot EXE or MSA file. Just go to the Microsoft Store. Click on there and see this is the feeds off. This is the homepage when you download and open Power BI for the first time, we will see this page, see this homepage after Power BI Desktop. Here you can see the report view. Here you can see the data view where you can see your tables. Here, you can see the model view, where you can see your models, how they are connected. And on the menu page you can see the home. Then you can see the insert where you can insert a new visual key influencers, decomposition tree, smart narrative, all those things that we'll learn. Then modelling. You can see view, you can see and help. And here you can go and see the Microsoft Power BI, next up related guide that had been created by the Microsoft. And here you can see the opsin of good data where you can see the various options like Excel workbook. If you want to get the data from the Excel workbook. Microsoft Power BI dataset data flow data was SQL Server, Analytic Service analysis server text or CSV file. If you want to get from the web that also we can do. So there are various options that you can. If you want to get your data, Excel workbook, data hub, SQL Server. So all the jobs and here, and here also you can see SQL Server. So all these options are here. And here also you can see add data to your report. You can get data import data from Excel. You can import data from SQL Server, or you can simply paste it onto a blank table. You can try a sample dataset that Microsoft has given. Okay, So all these things will see from the next lecture onwards. So this is the overview of the Microsoft Power BI. Once you are done with the report, you can save your data. If you want to import from somewhere, you can import your font to export, you can export. And there is another option or Nick, when you create your data you can listed, okay, so this publics will enable when we create some visualizations and does dashboard. If we generate some reports, the Publish button will be enabled. And it's pretty simple to enable. And it's pretty simple to visualize them with your top management or your client dealt by putting the e-mail ID and they can open it on any platform like they can open it in on the mobile using Power BI, mobile. That is our visualization tool where you can see all your visualization in a mobile. Okay? So see you inside the next lecture where we'll see, I will try to import the data from excel. 3. POWER BI FRONT AND BACK END ENVIRONMENTS: Hello and welcome back. I hope you're enjoying this class. And in this lecture I'm going to talk about Power BI environments. So what are the environments that we have in Power BI? That is very important to understand. So let me start quickly. So Power BI has actually are to end moment. You can say one each front end where we do all the visualizations and it Analysis part, where you can see the graphs and reports and dashboards. So that is the front-end part. The other one is back end environments where we import the data tables, the data sets, various databases and various data sources that we our Power BI, report generation and dashboard creation and everything else. What are bi? So there are basically to enrollment, front-end and back-end. So when you look at the power bi, so this area where we are to all our Visualization part, this is the front-end part of what we can see here on the front, in the front-end part of the pod be. And when we use this, Get Data and get the data from the various sources. So this is the back-end part where we have the radius option to import from various data sources that excel or data flow towards SQL Server text or CSV file from the vape, we can export the data. So all these other back in thin skin thing. So these are the two end moments that we have in Power BI and Microsoft Power bi is a powerful Business Intelligence told that allows users to analyze data and Sales Insights. Biodegrade consist of both front-end and back-end environment, each performing different operations to support data analysis and visualization. So both these environment front end and Mackinder, important because back and restore the data. And front end we visualize the data. We create charts, graphs, where you saw now regularizing our tools we used to visualize the data and then we're done based on don't Visualization. We can analyze the data so we can do the Data Analysis part on the front-end. So let me go in little more detail about the front-end environment. So for Internet moment in power, bi is the user interface where data, where data analysis and visualization take place. Like I said here, when we create reports and all that is done. Whatever we can see the user interface, it is part of the front-end environment. The key operations in the front-end environment include the data connection. You just can connect to the various data sources using database. Databases like SQL, my sequel, or whatever you are using that we can connect with the Power BI and then the cloud services. Then the second one is data modeling. Data modelling. This involves transforming and sipping the data you didn't Power Query Editor. To create a data model that serves. Our data analysis needs data connection to connect with the data sources. Data modelling to transform and save the data for Data Analysis. Dashboard creation, you just, you can create interactive dashboards and reports by creating visuals and defining the lessons in between the datasets and various other options. And we can also add majors and calculation. We can add our own calculations to it. We can add our own majors to the dataset that we have and we can create report. The fourth one is very important that each Visualization, so Power BI offers a wide range of visualizations such as charts, graphs, maps, and tables to present the time into two-way power bi, away. Barbara is so popular and said you to you because it offers a very wide range of visualization tools, such as charts, graphs, maps, and data tables to present data in very intuitive and ready to present data in very intuitive and very modern way. So we can visualize data very easily. And the data which lay your son, or the Charts and Reports created with data visualization tools in power bi are very attractive in nature, very into two. So astronaut, you look at them, you will understand some part The data that you cannot analyze or you cannot understand by looking at the spreadsheet. So when you visualize data, you have the better understanding. Then the next part is the dashboard creation. So once we have the reports and all, we can club the reports together. We can put it on a dashboard and create a dashboards that will tell a story to the users. So dashboards allow user to create a consolidated view of multiple Reports, providing a holistic overview of the data. So as you have the data, so as I said, we can create various reports and graphs in RBA. But when we put all the reports and the views together, we make a dashboard. And the dashboard will be the entire story of the data and add data analysis that you want to do on the dataset. That by creating reports and graphs and charts that you put on one page and that it's called taskbar. So dashboard will be very intuitive, very holistic approach, RPA, presenting data or analyzing the data. So one dashboard will have multiple Reports, refuge and graphs, and they will be interrelated when you change one parameter, you, all the graphs and charts will be changing based on that. So that will give you a very broad view of the Data Analysis and no dashboard creation is very important. And dashboards are basically them, a consolidated view of the multiple Reports. And then the capsule very important part as well. Collaboration and sharing Power BI enables users to collaborate on Report and Dashboard and share them with the stakeholders secularly. In the power, bi or any other business intelligence tools nowadays, we get this opportunity to collaborate with our team members are the stakeholders. And we can see what we're creating as report or dashboard that we can directly share with the stakeholders or our team members and our managers, our top management. And they can have a live view of what you are doing and what reports you have created. And if they want to add something onto that, they can something go on to that. They can also do that on Dashboards. So it is very easy way to collaborate with the theme. And C are the what report you have created with your top management or the team leaders, or even the stakeholders of the company, or even the clients are, even though you're team-based. So it will not only enable you to collaborate with them, it is very easy to say add reports and dashboards with them. And they can have the same view, what you are having. They can just click on the things and have a very good Analysis if they want to do on the data. So this is very important feature upsetting and collaboration in the Power BI, which makes it very, very important. Power BI Data Analysis tool. That Analysis tool. Now let's understand about the back-end and woman. So back in enrollment in power bi is where data processing, storage, and management occur. So obviously in the backend, we always store the data, right? So all the databases and database servers are part of the backend thing. So in the backend, Then warming, even in the Power BI also the place where we store the data. We manage the data and we do the data processing, right? So Power BI back-end environment is also the same where we are. We do the Datastore is Data Processing and Management of the data. So all these three important data processing, storage, and management data management occurs in the back-end environment of Power BI, and the key operations that we perform in the back-end environment in power via include data refresh Power BI, I can automatically refresh data for showed up to insights like Ali or what would happen if some businesses that are e-commerce website is there and they have the data data source or they have their restored that usually inflammation. On our database. We pick the data tables from the dead, from their data database tables and redo some analysis on that. We create reports, dashboards and financial analysis or whatever Analysis, Customer distance and whatever you want you do on that data. But the problem was the data in e-Commerce environment or any woman nowadays online, every business is online, every Our second new data is being added to the tables. Are the data is getting refreshed each time. The problem was that the data, the IP tended back data. If tended back data, you have walked on and now you're submitting a report to the management. The scenario has been changed completely because in those ten days they have, they would have run some campaign. They would have gotten the new rituals and whatever the Reports you or you have created, that has changed. So that is of no use for now after ten days. So that problem is solved in power bi, because here data, if you are picking a data from any database server and that Database Service data tables that has been updated. So what Power BI will do? It will automatically refresh the data tables. And when the data tables are refreshed, it will take all the new information into the tables where we're processing in our back-end environment, in Power BI, even the power bi references we'll answers will also get refreshed. And your reports will also get changed based on the new data that has come in. So in that way, Power bi, automatically the phrase data from various sources, whatever sources you are getting data that will be refreshed automatically. You just need to schedule the interval on what interval you want to fetch the data and refresh the data at the back-end of the power bi. So on those intervals, suppose you are selecting for every 10 h, every one day, or every alternate day, every 24 h, or every 10 min. Whatever interval I'll select on data interval, that data will get refreshed and wait on that. You need not to do anything. Your reports, your Dashboards will be automatically get refreshed and you're reports and your Dashboards, whenever you say, it will be very, very relevant and it will be very, very useful for the top management to analyze, to analyze the data in the real time. So data refreshes very, very important features in power bi. Then comes the Data Transformation, Power Query Editor pounds Data Transformation and cleansing operations in the back-end, enabling data modeling in the front end. So for the data transformation is very, very important in if you are going for a data analyst is right. Why? Because if you work on the raw data and you will not transform, you're not add some new majors and features and Columns to the tables. You will not able to get the correct view of data or character information or correct Analysis. You will not be able to do. You have the, all the things Columns are there in your data. Like have the user information, what things he bought, what was the price, and what price it has been sold. And all those theorems for profit and loss thing. So those measures you can add, you transform the data, you create new fields, you add new calculations. If you want to calculate the revenue based on some other things, or do you want to calculate the discounts on a different formula? So all those things you we can do with the digital transformation tool that has been provided in the Power BI, Power Query Editor. So this thing, Transformation, Data Transformation and cleaning or presence or done in back-end. So that you will be able to do the data modelling at the front-end, back-end processing of Transformation and cleaning operations in the back-end of Power, BI will enable you to do the data modelling in a much better way on the front end. So that is the power of Data Transformation in power bi. Then comes the data compression, data compression Power BI compresses the data and optimizing data to reduce storage requirement. Data to reduce storage requirement while maintaining query performance. So Power BI has a very important and very powerful feature of data compression where Power bi, unit not or do anything for compressing the data, are saving the space. Power BI, you will automatically compress your data and optimize that data to reduce the storage requirements so that you will not need a very high stories are the place to store the data, right? So the storage requirement will reduce. But at the same time, Power BI will make sure that this compressing a Brita and optimizing the data for storage requirement, for reducing the storage requirement will not hammered the query performance. So even though the power bi, compressing your data and optimizing the data, it will not hamper the performance of your query It will not take much time. It will be much quicker and agile. Good as it was not compressed. So Power BI will do the data compression by its own. Then come the security and authentication. That is very, very important thing for any enterprise level applications, right? Power BI braces. Study. Power BI, back-end environment handles user authentication, access control, and data security, ensuring that only authorized users can access sensitive information. So even though Power bi is giving you the option to share your dashboards, cereal Report, and collaborate with others. At the same time, it is also making sure that the back-end environment handle user authentication and access control. So while setting the setting you're reports and dashboard, you can give the access control to the users and your team member that they can only perform those particular tasks. Like they can only view, or they can only eat, or they can modify all these. They can delete or they cannot delete. They have only reliable access to all these kind of Access Control you can do through the back-end environment or the Power BI. And each user will be authenticated, and then it will be allowed to log the system. And it also secure your data. So that only authorized. You just can access the sensitive information. Because the company, all the things that are being stored as a data level. So it will also make sure that your servers or your databases are secure and all the sensitive information secure properly. Then the Datastore is Power BI store data in Cloud Storage, enabling fast and efficient query our way, when you imported a Power BI, restored the data in a cloud based storage so that you need not to worry about the stories, things and all. And it is, it has been installed on the Cloud. It is very fast and efficient. When you query the time interval and retrieval, querying and retrieval time when you query something and you get the ritual, the time between the query is going in and the result is coming out is very, very minimal in case of the Cloud-based transactions. So this Datastore is, since it is on the Cloud visit is very fast and efficient. Querying and retrieval both are very, very minimal time it will take and it is very efficient, and it will give you the very good performance as well. So performance optimization is also taken care by the power bi back and enrollment. Enrollment. So what it will do, it will optimize the query Education and leverages the caching, caching techniques to improve the overall performance. So Power BI, internally use the guessing or techniques to improve the overall performance so union not to worry about all these casting techniques and how to improve or optimize the query Education, all those things. Power bi back-end environment will automatically take care of these things. So this is all about the front-end and back-end introduction for Power BI. And in the next lecture we'll see how. Okay, let's, let's do this in this lecture address. So we'll see front-end and back-end and front-end and back-end environment in Power BI work together to provide a seamless data analysts experience that we already understood. Fronted operations triggered backend processes such as data, the praise, and data, some relevancy. So from the front-end, you can control the back-end processes. And you just can interact with Visualization in the front end, which then send queries to the backend, what data retrieval and processing. So whenever you propound something on the front end, obviously it will go back to the backend and retrieve the data and processes. And then it will show you that the Jad result in the form of the graphs and charts back and respond to these queries by fetching requested data and return the result to the front-end Visualization. That is a very normal thing that we all know. Let's quickly revise front-end environment and back and what are the main things they do. So the front-end environment will be used for data models. And then we have the Data Analysis Expressions, DAX query to the calculated majors and Columns. And then front-end will be used for reports and dashboards creation and abused for reports and dashboards creation and publishing and setting will also done through the front-end environment. For the backend, we have the Connect and extract, reconnect to the data source and extract the data. That operation we do, then we transform and save the data. Then we do the profile and query optimization. And then we have the margin append operations on the back-end. So when we summarize this, we have four points. Microsoft Power, BI, front-end and back-end environment collaborate to deliver comprehensive business intelligence solution. Front-end environment empowers you just to connect to data, create reports and dashboards, and collaborate with others, such as your team members, your managers, your stakeholders, your clients, what, whoever you want, you can give access and you can collaborate with them. And said you report back end environments handles data processing, storage, security and performance or to optimization. And the last point is in, the last point is interaction between the front-end and back-end ensures smooth and efficient Data Analysis. Experience. Interaction between the front-end environment and back-end environment is so smooth that you will not feel any lag in data processing. As soon as you click on some Report, you drag-and-drop the fields and majors. You will quickly visualize the data and it is very smooth and that will give you the very seamless and efficient Data Analysis experience. So this is what I wanted to convey about the front-end and back-end environments of the power bi. I hope I can read whatever I wanted in a very clear and concise manner. I hope you understood. If you have any doubt, you can comment and ask. Inside the next lecture. 4. POWER BI Connectors: Hello and welcome back. In this lecture, we are going to learn about the basics of Power BI. Next few things in ethics, and that is type of data connectors cover leveling. So as I have told earlier, also, Power way provides a lot of connectors for databases. Like it's not restricted to one kind of database that you can use with the Power BI. It provides a lot of data connectors, almost all sorts of our data connectors to connect with any kind of data source that you're using. Microsoft Excel, microsoft Excel or my, my sequel or SQL Servers or the no sequel. Databases such as MongoDB grabbed databases, GraphQL at order, not order, Neo4j, any sort of databases that you are using that you can connect with Power BI very easily because it provides in-built data connectors that you can just click and you provide the data source path and it will be connected easily. So that's what, that is the flexibility in that is the features are provided in Power BI, that makes it so, so great. And that Power BI is the number one in the market right now. And think this is because from Microsoft, it is very much useful because you can use it with the all others microsoft Office 360 pipe or apps provided in an office environment, it is very easily to gel up with the other tools that you're using with Excel or PowerPoint or whatever things you are using. So it is very easy to connect with Microsoft along with all other tools. Be it AWS or Google, GCP, whatever you're using, you can easily connect with the Microsoft Power BI. So that's what we're going to learn, are types of data connectors available in Microsoft Power BI. Microsoft Power BI offers a wide range of data connectors that enable users to connect to various data sources. What analysis and visualization purposes. So obviously when you are using Microsoft Power BI, so when you connect to a data source, you'll collecting for our data analysis purposes only for data analysis and visualization. So that's what we do with the Power BI. So these connectors facilitate seamless integration of data from different systems into Power BI. So whatever system, whatever servers you are imaging for your database storage, it will provide you the seamless facility, seamless integration of data from different. So there will be no lag or nothing. It will be very seamless and it will be very efficient as well. Let's understand what other data collectors have level in Power BI. So Power BI provides connectors for popular databases such as Microsoft SQL Server and Microsoft. Microsoft sequel server, Microsoft Excel. Even our Oracle database is also part of my sequel, also, post-scarcity, cool also, it provides the connectors. These connectors are, these connectors allow you just to establish a connection to their databases and import tables, views, even the stored procedure directly into the Power BI. You just can leverage the very query capabilities of the database connectors to retrieve and transfer data before analysis. So these database can extend. Connectors are not only to pull, it also gives you the query capabilities of those databases so that not only you retrieved, but also you can transform your data and then you can start your analysis so you can transform your data before doing the analysis. So what are the file connectors are our levels. So power-based support connect sense for different file formats as well like Excel, CSV, xml, JSON, and C. At point, you just can import data from each file formats into Power BI for analysis and visualization, file connectors enables users to access and refresh data from local files are files are stored in cloud-based storage. Services such as OneDrive or SharePoint. So it not only provide the database connectors, it also provide the file connectors as well. Then it also provides the Cloud service connectors as well as I said earlier, Power BI integrates with various cloud services enabling users to connect to their, enabling users to connect to their data stored in play pumps such as Microsoft brand, G'day, Salesforce, Google Analytics, and SharePoint Online. So these online Cloud Service Connectors also can be integrated easily with the Power BI. These connectors provide direct access to data in the Cloud, allowing you to create reports and dashboards based on the real time and said dual data refreshes. So as we have discussed in the previous lecture as well. So when we connect Our Power BI to any data source or through Beta Cloud Service or the file connectors, or even the database connectors. It will not only give you access to the data in the Cloud or the databases, but also it will allow you just to create reports and dashboards in the real time. And you can go with the real-time as. And when data is getting rephrase, it will be reflected in Power BI, or you can say dual dot data refresh. On that time it will be refreshed and it will reflect in your data reports and data visualizations like whatever you have created. Online service connectors also supports, so far away offers connectors for popular online services like Dynamics 365, Salesforce, Google Analytics, and SharePoint Online. Okay. So these connectors allow user to connect to their data online services, accounts and detailed data for analysis and visualization. You just can pull data in from multiple online services to create consolidated reports and dashboards. So suppose you have a business and your data is stored in various different, different location. Barbie, I can pull data from the multiple online services and create a consolidated reports for you. And that those reports can be combined together. Pause reports can be combined together to create a consolidated dashboard. So that will be giving you the holistic approach of your holistic view of your business, what's going on on our values, our data sources. When pulled together on Power BI, it will give you a very good visualization of entire thing in a very holistic, real. Big data connectors also support power-based support the connectors for big data platform such as Apache Spark, Hadoop, Azure, Data Lake Storage, and many more. These connectors enables users to connect to large volumes of data, volumes of structured and unstructured data. Advance analysis and visualization. You just can leverage Power Query Editor to transform and save data before importing it into Power BI. And it also support the custom connectors. Barbie allow users to build custom connector to connect to the proprietary or specialized data sources. Suppose it goes, if got a big company and you have your own proprietary data sources, are very specialized data sources for your own need. Power BI can be customized to fetch data or connect to that. Those are proprietary data sources as well. So that cache poisoning is also possible. Custom connectors provide flexibility to integrate with unique data sources that are not covered in the built-in, built-in connectors. So there are built-in connectors which will enable you to connect with the big data connectors. Online services connector or Cloud Service Connectors are filed connectors or database connectors. Along with that, faraway has a custom connectors as well that will allow you to connect to the proprietary data sources that is not available in the built-in connectors. So that also possible. And with that you just can develop custom connectors using Power Query SDK. So Power BI query is it allow you to create custom connectors for your space flight and proprietary data sources. And it leverages the connectors developed by Power BI community. So bar gay community is growing and it is very fast even now. And many people have created the custom connectors to connect to a proprietary or a specified data sources. And if your data source is similar to them, you can use those Custom Connectors created by the community as well. So what we can understand from this, let me summarize this for you. Power BI provides comprehensive setup data connectors that enable you to connect to various data sources for analysis and visualize it. But database connectors allow you just to connect two popular databases and important tables views and stored procedures. Procedures, file connectors, Fabrega file formats such as Excel, CSV, XML, and JSON. Cloud Service Connectors provide direct access to the data stored in a Cloud platforms like Judas and Google Analytics. Online service connectors enables user to connect to the online services such as Dynamics 365, Salesforce, Google Analytics, Big Data Connectors facilitates and integrate the data from big data platforms such as Apache Spark, azure, Data Lake, and many more custom connectors offers flexibility to connect to propriety and specialized data sources. So these are the data connectors available in the Microsoft Power BI. Let me show you here as well. So when you come to the Microsoft Power BI, and here you can see there is an Excel workbook option is there. So when you click on here, you can see here import data from a Microsoft Excel workbook Then the second option, easier, one leg Data Hub, so what you are obnoxious. And so if you want to import data from your organization, you can click on here. You can open a Power BI dataset or even data marts or lake house or warehouses. If you want to connect, you can connect with here. Then we have the SQL Server option here. Then we have the, if you want to enter your data by yourself and you can create a table directly by clicking enter data here. Then Dataverse connect to a database through SQL endpoints so you can connect to the database as well. And here you can see the recent sources that you have used. Apart from this, we also have the good data often here. So here you can see that list update common data sources. So lift up common data sources. You can see your Excel workbook, Power BI dataset, data flow data, SQL Server Analysis Services, Analysis Services. You can see your Live Connect to connect our import data from SQL. We connect our import data from SQL Server Analysis Services, database, text or CSV files you want to import that you can do. You can directly import data from the web page. You can just provide the URL, your web page, and it will pull the data from that web page. You can use the OData feed to import data from an OData feed. You can create a query from scratch and you can pull the data. You can use the Power BI template apps, install a pre-built data set and report in Power BI service. And if you click on more, it will give you all other options like you can see here. These are the databases. All type of databases or connectors are listed here. And if you want to see the connectors, you can see your Excel text, CSV, xml, JSON folder, PDF, SharePoint folder, all these four databases. You can see your SQL Database Server, SQL database server, extra database SQL Servers analysis, what I call database IBM Db2 database, IBM informatics informatics database, IBM mysock called Database Postgres, Sybase, Teradata, SAP hana database, SAP Business survey allows application servers, SAP Business Warehouse message server. So all the jobs and other Amazon red impala, Google BigQuery, Google BigQuery a jury, because Snowflake, space. So all these are made on a tenor. At scale BI connectors data, we're actually watch reality the node. So all the jobs and you can find here. So almost all kinds of databases, connectors are available here at Microsoft fabric preview. Here you can see our database warehouse, data marts, Power Platform. So you can see your data was dataflow, job services, database and services you can connect through online services. You can see here, lift up online services, salesforce, sweet IQ, Spark course, March seat, QuickBooks. So all the jobs and GitHub. Salesforce, Dynamics, Microsoft Dynamics, Research, T5, Microsoft Exchange SharePoint. Everything you can connect. You can see the other as well. Okay? So these are the, these are the database connectors available in Power BI. You can see them all listed here. There's so many everything, almost everything that you need will be our label here. Connectors. See you inside the next lecture. 5. Creating Basic Reports with POWER BI: Now we have the basic understanding of Power VA and different data connectors. And we also have the understanding of Power Interface. Let's create a simple project. Let's do the hands-on. Teach you one by one how to create these, that, how to create a graph and it's better to do hands-on with them all data set. So I have my small dataset of Newark and it's an a CSV file. So first thing what we do, click on the get data, and here we'll select the text slash CSV. And here I'll just import this NY properties Sales dot CSV file you just import this data will be important here. So now we have the N by N, the sky underscore party property. So property. First, let me cancel this property. Okay. Go here. Now, I'll go again to get data, text. It's less CSV and the ALL select this. So now we have done UAC, underscore environments, New Relic underscore property, and that's called Sales struck CSP. So this is Neera property data. Since small, fine. Here we have the ID Area, neighborhood, link, class category interests, lambda squared feet. This data is missing, cross the square footage or there he had built in which year it and sealed it and sell price. So these are the data Columns we have in our dataset. But when you look here, rehab, sale price, yeah, But sale prices coming with $1 attached at the end. So when you are directly load this dataset, what will happen? This will be considered as a TextField, right? This will be considered either texts will be called dollar is attached to the sale price. So we need to transform this data first so that it will be considered ADA number or the price. Okay? So for data before loading, you can directly go and load, or you can click on Transform. So you can do both the things. Okay, but I'll suggest that before loading you just transform the data. So just click on Transform data. After loading also you can transform the data, okay? It's not a problem. So here we need sales prices, prices, sewing as a TextField here and here. Every entry having $1 attached to it, that's where it is considered as a string. Okay, so to do that, what we need to do, what I'll do, I'll click, I'll select this column. I go to the transform your home, and then transform, then select the Replace values. So click on Replace value will see that to opsins Replace Values and replace Errors, will see that replacer latter. Right now I'm going to use the replace values, so just click on that. And here what I want to do, I want to replace the dollar with the blank, okay, so whatever you want to replace, for that, it will find the dollar sign in the column values and it will replace with us. Blank. Okay? So just click on OK and it's gone. Okay? And see you. All the dollar values have been replaced. Now, I'll change the data type to whole number, okay? And let's click on whole number. So now it has been changed. Okay? Now, click on Close and apply. So just click on Close and Apply. Now it will load the data. Okay? Now, here, uploading the data, you can see here on the right corner, power and why and that's called property underscore cells coming here and see your sale price is coming as a number. Okay? So now, since our data has been noted in our Power BI, what thing we can do? You can, you can see Report View, Data View, and the model with three views of level in the report view is not visually anything because here we'll build our visuals based on our data. The second one is Data View. We want to see your data. You can click on the Data View and you can see your table here. So CEA price, everything, all that Columns and rows have been imported here. Then if you want to create any model, if you have one or two tables, you can create the model and you can see the model view. Yeah. So model view, UW, and this is the dashboard view. And on the right corner here you can see two options, actually three options, filters that we'll see later to apply filters and visualizations. And there are other different kind of visualize sense of level like a bar chart, Column Chart, bar Chart, Clustered Column chart, bar chart. By chart, Donut Chart, all kinds of charts or their map Chart, everything is available here. And then we have the data option here. In the data option, you can see your table, whatever data table you have imported that Will, we can get along with the columns, names or addresses. Yeah, building class, gross, square, id, land is square sale price here, building. Deep course, everything has been given here. So to start creating Visualization, simply drag this sales price here. And now you can see your simple bar chart has been created. This is a Clustered Bar chart has been created here. So now this is showing the sum up all Sales. Now, if I want to put some more thing, I, if I want to put data here, I can drag and drop here Sales Data. And now it will source the maiden, the date, date on the x-axis and y-axis since the sales price. Okay, he, from here you can select the what you want to sum up sales price or do you want to show the average sum or average up sense that you can see here. You can count for the Sales, you can. So these are the things you can do from here. Okay? So for now I'll keep on some of the sales. So this is a standard Clustered Column chart has been created. Now, if you want to change it to bar chart, you can do like this. Okay, so whatever you want to create, you can know like this, okay? If you want to create some more, you can simply click the cells that here. And now you're destroying the date. And if you want to visualize, suppose you want to realign the sales price here, you can drag here. Now sealed it and sell price again, we can see and we want to create a line chart. You can create a line chart based on that, okay? If you want to add Area, you can put Area here and it will show you that date based on the same data. And it will do I add a date with the idea with this colored light. Next thing you, one more thing you can do, you can create a map chart as well. Create map Chart. So you need to go to the options and settings. Click on opsins. You need to go to the security. And yet this opsin map and Field Map visuals you have to check, okay, so this should be checked so that we can do the map Chart. So use the map Chart, we can simply, if you want all the code and you probably want to make a map and click on the map and CNO them. Map has been can you didn't hear? Let me just minimize, minimize. Drag this here. And what I wonder do I want to make it big? Okay? Now, if you want to put Area, you can put Area into this. So now if you want to put the now, you can see here ArcMap Chart is ready. You can see here and embed it on location if you swing the code and all other details, okay? Of course, on the tape you want to soda can see an artist. So in sum up Sales price based on that zip code. So all these things we can do here. So this is to create a simple visualizations using both to be C. This is a simple Pie Chart. So these are the things you can do. You you can select any date here and and type thing when we can, you can select a job code and it will be changed. So this is important, so be, it will remove any interactive. Not only this will change, but all other Charts will change, but it. Okay. I hope you got the understanding. So in the next lecture we'll try to do more of creating a more reports based on our data. 6. Importing Data into Power BI: So now we're going to learn how to import data from Excel into the Microsoft Power BI. So when you come here for the first time, we will see the add data to your report. And here you can see the opsin import data from Excel, import data from SQL Server, and paste data into a blank table and try sample data set and get data from another source. You can see the various options here. Apart from this, you can see here on the home menu you can see good data. So when you click on here, you can see common data sources. Excel workbook Power, BI dataset, all the data sources you can see here. You can see here Excel workbook, SQL Server. So enter data manually here. Copy and paste or you can enter a table, table. You want to enter manually, enter manually that you also you can do here. So for now, what we'll do, we'll use the Excel seed for our data analysis and dashboard creation for this class. So I'll click on import data from Excel. You can click on here. You can go from here as well. Okay? So click on import data from Excel. And I'm going to use this HR data. So I'll select that seat and click on Open menu. Click on Open. The extra seat will be open. And here you can see there are two things. Cable. One, you just click on here. You can see the data, you can see here. You can see the HR data. So I'll click on the HR data and I'll, what I'll do, I'll click on the load cell, click on the HR data table and click on the load. So when you load the Microsoft Power BI will start loading the data into the Power BI from the Excel file. Now, as soon as you import the data, you will see will visuals with your data. So where does the auditor? I can't see any table here, right. It's just saying that our drag-and-drop from the DataFrame and onto the report Canvas to this ban that we are seeing here. This is the canvas, this whole area is called Canvas and here we can create our Event reports and dashboard. Okay, So where does our data to see audit or we need to click on the Data View. This is the default view. This is the data view and this is the model. There are three views in Microsoft Power BI. That is first one is the report view, second one is Data View, and third one is model view. So right now we are into the report view. Now, as soon as you click on the Data View, you can see here our table on our data from Excel seat has been loaded here. So it's a huge dataset that we have loaded there 1,471 rows in this dataset. But you can see here the column one, column two, the header is not here, right? In our Excel. See you there. Column names, but here it's not visible, right? So how to do that? So we'll go to the report view, will go to the transformed data. See here, when you come here you can see that transform data, transform data, right? So you just click on the tongue from data. And our table will be opening here. See you at our data and see you. Our first column name is Tristen at recent business travel, CF underscore, age band at recent Labor Department, all these things. Actually that is not our first row, this is our columns. But by loading it has Barbara is considered as a rule. So how we can do that? How can, how we can resolve this problem and making this brush to either column. So to do that, we need to come here. You can come here and see huge cost grew as header. Right here. You can see the opsin, huge cost rule. You can see the cursor here. Huge push to ask. You just click on here and the first row will become, hey there. Just click on here and see, you know, our table. Our table is perfectly fine. And the first row has become the header. So now our headers are properly done. This way, we can come to the Transform Data and click on here huge first row as headers. And our first row will become the header up our table. Okay, So this way we can transform our data after astronaut you done with the transformation, you just click on Close and Apply here. Just click on Close and Apply. Now it is applying the changes that we have applied in our dataset. Okay? So now we'll see the return value. So now when you click on the Data View, see your attrition in the first column. Business travel, all these columns have become the first column. The header is properly aligned, right? We can come to the visuals or the report view and modern view. You can see all the column details you have. You can see here what kind of data it is that you can see in the modern view? Colab, you can expand. Okay? So Data View, Report View and model view that I hope you understood and how we can import a dataset from the Excel into the bar where that also we can see. Now, we can see various options here. Now, when you come here, you will see that there are three options here. Data. In data, you can see all the columns in our data set has been listed here, right? Okay. And then we have the visualization where you can hold options for creating the report chart, pie chart, bar chart, column chart, all these options, doughnut chart, pie chart, all these options are here. So this is a visualization opsin and then we have the filter. If we want to apply filter that also you can do. So. We have three important options given here, data visualization and the filtration filters. So we'll use this to create our dashboard and reports. So first we'll try to create reports and then Margo's reports and try to make up dashboard in Power, BI and data. That, that's what will be the very, very interactive. And it will be really amazing when you sold the visualization that we create in Data Power BI. It will be really amazing and you can impress your client because it will be very hard dynamic visualization and asphalt. So see you inside the next lecture where we'll try to create some reports. You're some charts here on this blank canvas. 7. Importing Data from Web: Hello and welcome back. In this lecture we're going to learn about a very important microsoft Power BI feature. And this feature is very important in these days because most of the data source that we want to walk our, our level online. So it is very important to get the data which is available online easily to our Microsoft Power BI and start walking right on that data. So what used to happen? We have our data on some website, I suppose Ana Wikipedia or somewhere, and we want to get that data. We need to copy the data. We have to put that into the Excel sheet and then transform that data, work on that data, then save that Excelsior data. That Excel seat into the Microsoft Power BI, and then upper important into the Power BI, that Excel sheet that we have taken from the web. We can walk. But Microsoft Power BI has given very important feature where we can simply put the web URL address of the website where that table is available on the web. And Microsoft Power BI will extract all the data on that table into the Microsoft Power BI in a tabular, in an Excel format, and write in the Power BI, we can start working on that data. So this is a pretty important feature that we can get data from any source, even from the web. Like we have seen how we can import the data from the Excel. See how we can, we'll see how we can do the SQL Server and SQL Server. And in this lecture we'll see how we can get the data from the web. So see here, when you click on this, Get Data, good data, you can see the Excel workbook Power BI dataset data flow data was SQL Server, Analysis Services, techs and CSV files. And here you can see web. So what is this wave for? This wave is for import data from web page. So if you have data on a table on any website that you want to are important to the Power BI and you want to transform the data or work on that data, or use that data for your dashboard creation and report, Chris, and you can definitely do that with the help of web option here. So let me show you this is a Wikipedia page. We are list of countries and their population is cite wikipedia dot orgy. Here we can see the list of countries and dependencies by population. So here, if you scroll down, you can see the countries rank like based on the population of China is the most populated countries. The rank is one, then comes to India on the second position. And C here, their population, that percentage of population. The restaurant to the wall. And on what did what did this data is updated that is there. And like source. And in the North. So all the countries in the world data up their population has been updated on this Wikipedia website. Now, if I want to work on this table of country's population in the world. What I can do, I can simply delete. I can, I can copy this data and put it into the x, z and then save that Excel sheet and import that excellence it into the Microsoft Power BI. That is the one way that is the traditional way to do. But I can import this data as is it, as it is in my Power BI. How can we do that? You just need to simply copy the URL. So I'll just copy this URL here. This copy it. And then we need to go to the Microsoft Power BI to click on the Get data. We need to go to the web, import data from the webpage. Click on that and you will be prompt. See, this prompt will come from web. Basic. Adults will stay with the basic one. And here it is, asking you to put the URL. I'll just paste, paste the URL that I copied. So this is the URL that we have, corporate wikipedia.org slash wiki slash list of countries and the pregnancy by population. So this is the URL from where I want to extract the data or the table. So after putting that URL, we just need to click on, Okay. Click on Okay. And Power BI is smart enough to fetch all the tables available on that particular website. So see, now it is getting the tables from that webpage. Now see here these are the tables have level on that. Websites. So see here, this is the table that country rank, country name, population percentage, population data source. And also this is the, this is the exactly is rank country population, percentage, population data source and nodes. So exactly same thing has been populated here inside the Power BI and all the data has been populated here. Apart from that, there are some other tables as well, like column one, column two. This table column, all these supplementary tables are also there. So now, which are the tables you want? You can just click on that. So I want this country name and the respective population data to be important. So click on this option and here, there are two options. You can load or you can transform the data here itself. So what I'll do, I'll just load the data. And latter will go for the transformation of the data. Okay. Click on load, and that table will be loaded into our Microsoft Power BI. So see here it is being loaded. It will take few seconds. And older data models will be added here, c. So now we are into the visuals. But when you come here data view, you can see the table here, see the table rank, country, population, population, percentage, date, source, and nodes. So all these things have been added here, right, right into our Microsoft Power BI. Now, once we have this being added, we can come to the report or visuals, visuals of pain. And here we can start creating our reports. Here you can see the table detail. What are the columns are there, okay, and here dw can see the data. So suppose if you want to transform the data, you can simply supported I don't want to, I'm not too worried about date. So I can simply select the date or date column and I can simply delete it. So just click on here and delete. Record date column is not required for my any thing. So I've deleted and similarly the sources also not required for social, so I'll delete North also not required. So I'll delete the notes, column management, and heal if you can see your rank. And here, if I want to rename this column, so I can go and click on Rename. And here I can just remove the dependency on what country. So now the column name is country, population. I'll just see what is the thing given here. So it is given as a population. So I'll keep it like this, this one, I'll put percentage population, okay? So I'll just remove this and I'll put parson did before. So now the column name is rank, country, population, and percentage population. So this way we can transform the dataset. If you want to change this country name, you just click on this and you just put your name. You can just put frauds. Okay. See ya from Yeti, you contain the datatype. All these things you can do. Again if your undergrad or new column, you can do that also. Just remove the predator and all the countries will come here. Now if you want to add some tortilla, you can simply click on here. And you can come to the data in here. You can put the rank, country, rank, population. So see you. Now we have this bark dark red even though country name and percentage population, contact button, this population. Okay. So this way we can get the data from the web and use them part of our report. 8. Importing Data from Access Database: Hello and welcome back. In this lecture, we are going to learn about a very important feature, again, connecting to that data sources. So we have seen how we can connect to our Excel, does it, and how can we, how we can import the data from the x and z. Then we have seen how we can support that data from the website in the last lecture. Now, we'll see how we can import data from database. So because actually in real world, when we walk on the data of some companies or some organization, most of the time, we'll have the data either in Excel or on a database. In a database like equals or Word or Microsoft Access or any DBMS server. Or even in some cases in the no sequel databases like MongoDB or something grabbed database like a graph, DB and L. So it is important to know that we can get the data from all these data sources. And Microsoft Power BI has given us the opsin to get data from any of these data sources. So when we click on More, we'll see the places from where we can get the data. So here you can see you name it and you'll get the opsin. So like from everywhere you can have from Google Analytics, from salesperson report from the Common Data Service dynamics study session five. I have figures, data, GitHub, LinkedIn, Sales, so Mixpanel, QuickBooks, smart seat. So whenever you store your data or other apps where you store data from there, you can get the data easily into the Microsoft Power BI. That is the great thing about Microsoft Power BI here you can see that GOD Power Platform database. So here you can see the list of databases from where you can import the data into the Power BI here. Sequel Server database, access database, SQL Serverless Analytics and which database? Oracle database, IBM Db2 database, IBM informatics informatics database, IBM neck jar, my sequel database for SQL database, Sybase databases, data data, SAP, SAP hana, SAP business warehouse, Amazon Redshift, Impala, Google BigQuery, Google BigQuery or on agility, what the car Snowflake. So all the data sources you can see here, MariaDB. So all these things are available online services. You can see you online services, like Michael said, Point Online, Microsoft Exchange Online dynamic statistics to five online agenda DevOps for subjects, Google Analytics, Adobe Analytics, GitHub, LinkedIn. So funnel from anywhere. So we have all the options, so we should be knowing how to do that. And that's why I'm trying to add the lectures on each and every thing, like getting the data from the Excel workbook, getting the data from the web, getting the data from them, our databases. So in this lecture, what I'll do, I'll select the Microsoft Access database. Okay, So from Access database, how we can get the data into our Power BI. That's what we're going to see in this lecture. So let me explain from the real here. We need to come from, come to the Get Data. Click on there and then you just click on More. And then click on mode. It will take you to the get data navigator. Here you just click on database and here we will see the stop diversity that does or that are our level. So click on Access database in this case, and then click on Connect target. When we connect and we need to go to the directory where we have kept our Access database file. So here I have kept a north wind dot ac dot DB Access database file here. I'll select that file and electric and often just click on Open. And as soon as you click on Open, it will show you the various options here. So in Navigator will show you that the split opsins. So see, there are many things that are being shown here. So let me tell you what are these opsins. When you see here are two rectangular or square boxes here. This is the symbol of a query. So when we get data from a database, so on that database we must have performed some queries, we must have performed some join operations. We would have some temporary tables, some permanent tables and relationships among the tables as well, right? So databases will do all sorts of things. So when we import data from a database to our Power BI, it will import all those things as well. It will import the queries like you can see here, customer extended, this is a query, embrasure extended query or order subtotal. This is also a query. So all these queries that we have written in the database that is also will be important. And this is the symbol of query. So these are the query that are being sown here. And then we have the, this TMP is a temporary table that had been created into the Access database Alia. Okay. So these are the TNP or the symbol for the temporary databases. So these are the these are the temporary tables that has been created into the Access database. Okay? And when we come here, these are the tables that we want to walk on. So these are the real tables, customers, customers, employees, order details, orders, products, and suppliers. So these are the queries. These are the temporary tables, and these are the tables that we're going to walk on based on these tables. This temporary tables has been created. And based on these tables, these queries have been written to get the required information from the table, like product sales by category. Products on backorder supports extended subclassed, extended order subtotal, all these things we have written a query, okay? So now what we can do, we can just select the tables. Okay? So if you want, you can select all the tables and click on Load, or you can go and transform the data as well. So what I'll do here, I'll just select customers, employees are that it is products and suppliers. So as we know, when we have more than one table in our database, we maintain some relationship between the tables like customer and employee, like order. So order details table will have some listened with the customers because the cash for customers who only we have the order details, right? So an order detail, we'll have the product reference, product reference, we'll have the product table will have the reference for the steeper. Suppose we'll have the references for supplies. So all these tables will have some relationship with each other. And when we import these tables, Microsoft Power BI, we'll also import the relationship between these data models as well. So Microsoft Power BI is smart enough to import the relationships as well and to suit at how it will do very intelligently. What I'll do, I'll select all the tables except the orders table. Will not import the order tables. Okay? Okay, but what we'll do, we'll select here, there is the option of select a related tables. So when I select customer implies order details, products, seaports, and suppliers. And when I click on, i'll, I'll leave the Orders table and not select DoorDash table. But when I click on here, select the related tables. What Microsoft Power BI will do, it will look for our tables and it will find the related tables. And based on the the relationship between the tables, it will automatically select the tables that is left out. So if there is any relationship between these tables and orders, the order tables will be automatically selected by the Power BI. Okay, So here I'm not selecting the Orders table, and I'll just click on Select a related tables. And I'll just click here and see, see the order table has been selected automatically by the Microsoft Power BI because it has the relationship with other tables as well. So if you want, you can select R, You can. Click on here, and Microsoft Power BI is smart enough to select the tables related with each other by default or by itself. So now we have the table selected. Now I'll click on the load. So just click on the load and the tables will be loaded. See here the all the tables customers implies the parts, suppliers, products. These are being loaded into our Microsoft Power BI. Okay, So it will load the data, it will load the relationships also, and it will create data model as well. So now we are into the report. Will visuals arpeggio. So now I'll click on the Data View. And when we click on Data View, we can see the table here. So we can see this is the customer table. We can click on the employee and the employee table. Then we have the order details table, we have the orders table, we have the product tables, and we have this super stable, and then we have the supplies table. So see you in the customer table has the ID, company last name, first name, email ID, and job title, weirdness, phone, fax city. All these details are there for customer details. All the customer details have been stored here. Then we have the employee where we have the employee ID, company last name, first name, email address, job title, and all other details have been stored here. Similarly, we have the order details page where we have the id. Then we have the order ID, product ID, quantity, unit price, discount status, ID, and data located, purchase order ID and inventory ID. Similarly, we have the Order table where we have the order ID, employee ID, and then the customer ID. So now we have this Orders table, which is having the references to the employee ID, which is therefore which we'll refer to the employee table, the Customer ID, we will report to the customer table. And then we have the order date, zip, zip named, suppose SIP address, all those things. Okay? And then we have the product table where we have the ID for a core product name. All these things, okay? And then we have the sequence suppliers table here. So these other tables we have, now, I have what I have told you that by our Power, BI will do the relationship between the tables or data modeling by itself. Or it will import the whatever relationship we have created into the database that will be imported and that will be created into the model view as well. So we can see that into the model. You just click on the model view and see here we have the employee table. So this is though. See we have the employee table with all these column. Then employee table is related to the orders and the customers. So customer table have these things. And then we have the orders table. So customer in order, so listen to this. And then employee is having the listen with the orders and orders will have the customers. And then order table has the relationship with the order details, which is related with the order ID, right? Okay. Then we have the syllabus, which is related to the orders. So this way we can see the model view of our data tables, okay? So how they are related with each other. There is no relationship between the suppliers and the other tables. Okay? So this way we can see the model view, adolescence in between that tables, data modelling of power. Now, I'll show you how this thing has been taken care by the RBA. We go to the File and we go to the options and settings. And then we'll click on the, we have the two options here, opsins and opsin. Further data source setting. So here I'll just click on the options and see you. Now in the options you will see the two Global Options and current file option. So global option will be applicable for all the files that will open into the Microsoft Power BI. Here you can see the type detects an opsin is there, so type protects and we have a selected by default it is and it tends to be selected like detect column type headers for unstructured sources according to the each file setting. Okay? Or you can select the other option. Always detect column types and headers for unstructured sources, okay? Background data allowed it up. Previews to download the data in the background according to which will set. Okay. So these are the opsin saw that I had been by default are given. So you can Power Query Editor CDS adoption selected, ah, for artist scripting. So all of these. Now what I'll saw you current file, see here data load. So what it detect column types and header file structure. And see here how it has imported the relationship between the data sources are tables. Because we have already selected this. This is by default selected important relationships from data source on first load. And that's why when I have not selected the orders table, it will automatically selected. When I click on that, I'll select the related tables, right? Because of this opsin that we have selected here, an auto detect new relationships after data is loaded. So these two options will be helpful in detecting and maintaining the relationship between the tables and dot data sources. So just make sure that this is selected. Okay? So this is how Power BI makes sure that it will maintain. It will import all the relationships from the data source on the first load. Okay? So I hope you understood. Click on Okay, and here you can see the data tables. Table View. 9. Web scraping in power bi: Hello and welcome back. So in this lecture we are going to learn more about the web's crapping in Power BI, or are simply, we can say how we can scrape data from a website to our Microsoft Power BI. So we have already seen how to do that. While we have seen collecting duct, not powder, we add to the website where we can get the table data from the website. In this lecture, we'll go off and see if there is a website, shopping website. And from there, and how can we get the ARP table details for any particular website? So for that, what I have done, I have open this website. This is Dennis warehouse website where I have opened this means apparel section here. Okay, So now what I want to do, I want to get the details of all these are product tables. So the details of this particular product will be stored in a, our database in a particular table. So I want to get the details of those tables into our Power BI for further analysis. Okay, So this is the swapping our website for our Tennessee red house. And I'm into the men's apparel section. Here. I want to get the underlying tables from this website. Okay, so hope we can do that. So we need to simply copy the URL, like we have seen earlier. And we need to go to the Power BI and we need to go to the data. So click on Get Data and click on mode. And when you click on Module get the opsins, various options will be shown. And here we need to go to the other, and then we have to click on the web. Okay, So select Web and click on Connect. Now, it will ask you to put the URL. So we'll put the URL that we have copied here. Now, once you put the URL, you just need to see the full URL I have kept here. Now click on Okay. So as soon as you click on, Okay, the Power BI will start connecting to that website. And it will try to get the data from that website. It will scrape the data from the website. So when you see this navigator option here, you can see there are two options, your table view and review. So when you click on the web view, you can see the object website, how it is looking on your side. Same thing you can see here. Okay? So this is the web view, and then we can click on click Create, Table View, sorry. And when you click on this table, so you can see the column one, column two, and column three. There are three columns. And here the product details is given. The odds column closes the price, and the column three is the description whether it's a new art world. Okay? So this is the exact thing you can see here. See the description that we are seeing your price and attack. So same thing as the same thing has been stored in the table also, that description, price and the back knew these three things, right? So that we got here. Then the column, another table is there where the column one and column two. Column two is the view. So then we have column three. We have the table for table 567, then we have the active one code and this protection all. Okay, so now what I'll do, I'll just important that table one. Table two. We can put the table to W3, W4. Table six. What are the tables you want? You can just check and you can hear the, you can also use the Add table using examples. And click on Lord. They will want to, W3 is being loaded. Now, we'll go to the Data View. See the table one. Then we have level three, level four. So now we can just click on Transform Data. And here the column one is, we can give it a name, product description, description. That column two we can rename as rice. And column three, we can put back. Okay, so now in this way, now our table is permanently scripts and rice and tag. So price here the weekend, make it a fixed decimal number. Or we can put it a whole number. So now it's like this. So that came on Lumber. Okay. Okay. Okay. So again, product description. Then I'll pour the rice. In the same way if you want to do ten, the column name here also, you can do that. Okay? Again, go to the transform data. Okay, so now we have the column named James. Okay? So now we'll close and applied. You can see here product price, product discipline price. And here you can see the data model. Table 3.4 element connect. This way we can grab the data from the web. 10. Project 1- e-Commerce Sales Data Analysis: Hello and welcome back. So we're going to do our first project in Power BI. With this project will also learn the basics of Power BI. So we will take the approach of doing and learning. So we will do the project and will, while doing the project, will learn the basics of RBA edge, but we're not going to learn first and then apply that to a project. Instead. We'll do the project. And with the project, we will learn the basics as well. So this is the best approach to learn anything, any programming language, any tool, anything if you want to learn, if you do hands-on, then it will be good for you. Let's start. The first project is e-commerce sales data analysis. So for this, what we're going to do, we will be simply doing e-commerce sales data analysis. For this, we will get the data for an e-commerce store that sells data cell state Alpha e-commerce website or a store. And this self DW, very, very raw and messy. So since this is, this data will be very raw and messy. We need to do lot of data cleaning activities. That because if you do data analysis with messy data or a raw data, you may not get a better result or you will not get the desired result. That's why we need to do the data cleaning activity on the data to make it clean and relevant. So that's why synthases are raw data. We need to do lots of data cleaning activity button. For this, we're going to use underscore data dot CSV file. We'll see what is there in that CSV file in a way that lets discuss the, what is the goal of our project. So white and analysis will do for this e-commerce. Since data, we will try to analyse and afford things. First thing will be our sins categories. Then we'll see the cells over time, how our cells are getting created a decreased over time. Then we'll see what are the sales promotions that company has run to increase the sales. And then we'll see the, what is the delivery time for delivering the goods at the consumer, the customer side, okay, so what is the delivery time? So these are the pointers on which we are going to do our analysis. Okay? So I hope the agenda is clear for this hands on activity. So let's go to the Power BI. Here. What we need to do, we will start with a new thing. And if you want, you can save the earlier thing, okay? And let me close this. And here. First thing is we get the data. So the data we are going to get from the CSV files, Texans ESP. And here, this is the underscore data dot CSV file that I'm going to click on open. Okay, so now I'll click on load. Okay, so data, information and knowledge will do. In the next lecture. First we'll note and see what are the things are there. So now the data is loaded here. We'll see the data view. When you click on the data, you can see at the column one, column two, like that. There are 17 columns and each column there is a, there are several entries, right? So this is the data. And with this data we are not understanding anything, so we need to do a lot of data cleaning activity on this. So this is the data on this, we will do the analysis. So in the next lecture onwards, we will start doing the data cleaning activity and data transformation on this cell today. The next lecture 11. Project 1 -Editing Rows, Columns, Data Transformation, Data Types: So now we need to import those cells underscore data dot CSV file. And after that, it's freed up loading. What we need to do, we need to transform the data because you can see here the column names are not properly or column one, column two, that is not proper. Then the first two rows you can see that is not proper. And then the column names that here on the third row. So we need to put them under headers, right? Then you can see here lots of N A values are there. Blank rows are there, right? So we need to tackle all these things. We need to do the data cleaning activities. So we need to transform this data. And for that, we'll see how we can edit that shows how can we make the role columns as a column name, column one, column two? Is this the, this as a column name, how we can remove the unwanted roles, how we can remove the NA values, how we can remove the blank rows and all. So this is the topic for this lecture where we learn how to transform data using NAD and transform feature, editing the rows and columns and making the column headers. So let's click on Transform data. So just click on Transform Data and see, you know, our Power Query Editor has opened. And you, if you want, you can make it full screen and CEO. Now, we can see here the first two rows are not of any use to do that, to remove this, because that is not of any use. We can go to Transform. And then, and then we can go to select the rows to remove the roles we need to come to the home. And then we need to come to the Remove rows here. And here, you can remove, use the various options are given here. But we want to remove the first two rows are from this dataset. So you can select this option, DMO, top rows. Click on that. And then here, how many rows you want to remove from the top that you can give. So here we need to remove the first two. So we'll put two here and click on Okay, and those two rows will be the proper. So now we have removed the unwanted Raj from our data. Okay, now next thing is I want to make this column, this row, order ID, product ID, and store ID, all these as a header. So to do that when you go to the transform, and here you can find the opsin either huge first row as header. You can see here huge wash dries header are huge header size first row. So what we'll do, we'll use the four stops and huge wash true antenna. So this washed row will become the header for our data. So just click on that and see now the first column names has maintained order ID, product ID, column three, store ID, order, date, order, sales. So now our data is not really proper wave, it has the column names also. So this is the way. So first thing what we have done, we have removed the rows using the Remove top rows. That is another option, or remove bottom rows if you want to remove the blank or unwanted Raj from the bottom, you are unwanted Raj from the bottom you can use the remote bottom rows and you can put the number of rows from the bottom you want to remove. Similarly what we have done, we have used that remove columns. Let's study this. We need to, okay, So what we have done, we have used the Mode column or remove rows, okay, sorry. And we have used that approach. Okay, so here we have two n that we have and remove the top two rows. And then next thing what we have done, we have done the first row. We have met the first row is a header. So to do that, we went to the transform. And then here the first option is use flash drives and as you wish, wash dries enters and the flash drives become the next thing is we want to remove the rod, which are not having any values. So there are so many blank rows, rows are there. So that if we want to remove so we can Simply go to the rows. And here we can click the move blank rows. So see here now on the blank rows has been removed. Okay? So this is the way we can remove the blank rows. Next thing, we want to remove this NA values. So to remove any values, we can simply come here and we can add Andrey can search for n. And we can simply select and click on. Okay, so all the n value will be having more or this column. Similarly, we can do this work. They saturate. And in the same way we have to do not also you can remote blank also. This that is my name. So you can check and you can anymore. And so now our data is clean. Similarly, you can see here this column three has no values, is chewed up this NS or we can select the column three and we can go to that Remove Columns option. And we can just click on that remove column. And that column has been removed from your hockey. So now our data is looking much better. Okay, So what are the opsin to rehab? What are the steps we have followed? We can see here in the applied steps. So I'll let our data was like this. Unwanted rows and there are no proper column names, headers. So what we have done fasting, we have gender, diapers that is not required. Then we have removed the top rows, and then we have made the flush draw other hand, then we have removed the blank rows from the data, and then we have filtered out the rows. So any values we have filtered out and then we have removed a column three. So this way we have edited that. We have learned how to edit the rows and output data, column Hotelling, how to handle the NA values, how to filter out any values, and how to transform the data using the transform function here and how to make the first row headers. Okay, So this is the basic editing we have done. Next thing what we will do, we will check the total sales, will try to find it out. Okay? Report that you can see here. We cannot change the data type from here as well. Just click here. If you want to make it like order IDs, text to yours so we can make it a whole number. Then product ID. Let it be like this, then store ID, and let it be let it order date is not a data. We can make it a day type. So now it is proper type. So now it is proper order that too also we can this is the x, we can make it our date. You can select the time as well. We can make our sins, will see how we can handle this kind of situation where cells, one cell, cell, cell size when added here for each value and it is text. We will make it a number and we'll remove the cells that we learn in the next lecture. Then revenues would be no decimal number. So we'll make a decimal. The stock is the number. Whole number, I guess. Yeah. Okay. Also there to stock also will change the datatype to decimal and then the price. Price will make our decimal. Here we are tending the datatype, promo type, it's okay. Then promo beans, okay, promote type toys, okay. And delivery date format is texts will make it to date. And delivery date format will make it again. We'll make it up. Okay, So this wave for automated everything. Okay, so we have the order ID, product ID, store ID, order date, sales, and revenue. Revenue is already a decimal number. We updates. Okay, So this way we have of what we have made our dataset a proper. We have done the basic transformation on the data. Now it's done so we can click here Close and Apply. Okay? Just click on Close and Apply. And it will be, data will be loaded again after applying all the changes that we have done. We have the cell to data, yeah. Okay, So see you inside the next lecture. We'll really try to do some more transformation 12. Project 1 - Handling Errors: Hello and welcome back. So we have gender datatypes and we have removed some Rows, and I'm Columns, and we have removed the dilutes as well. My using that filtering, we have removed a column, we have removed the roads. We obtain the data types for the columns as well. But here, when I change the datatype for this order date, what I got, we got error for some of the drugs here. So it is sowing date format. We could not pass the input provided as they travel. From 14. Jen. Too thin, it is not parsing and the data and destroying us to add on works, it's throwing us data. So one thing we can do if you get this kind of thing, because it was same comment, but I don't know why this triangular bi can see the order date one in order to both the theme data. So what we can do, we can remove this column. It is not relevant because both the data same. If you want. You can think both are same. We can just click on here and we can duplicate this column order to. So click on Duplicate. Now we have the copy of this and now these both are same. So we can just remove this column, the n-th column. So now the editor has gone and we have the order date and ordered it. Started to copy whether they could moment this side. Okay. So now each column we have remote. Okay? So we have to remove this or Columns. So when we remove this column, now we have to audit it to order that too. So we'll rename this by double-clicking here and really make it date. Okay? So now we have no Editing hours it out. Okay? Now you can see on the price thing, we have the edit and walk is that we had really getting at. Here is some better. Anyway, I lose. Okay, So it is throwing enter for the MA Wanli toward three. All right? Okay, so far this water we can do, we can select the price column and we can go to the transform. And here you can find an option of removing the edit. So you can get, you can come to the Replace Values and here you can click on Replace. Values are implicit or so we click on Replace Errors. And for replace week, you can put zero. Now editor will be removed and it will be replaced with a zero or you can replace with W. Okay, heritage relu, all those things. Okay. Next thing what I want to do see here for delivery format to also, we have the editor. So we can do the same thing. What do we have delivery format to? What Will do will make a duplicate. So right-click on that and we'll remove this. So let me and George Column. And so they didn't work colon and go to the remote Columns and remove this. Now, I'll make it make a duplicate of this. And seeing them, they sell them, they said delivery format. Okay. Okay, so now our data is free, so this is how we can anymore. Okay? Next thing is, I want to remove this tense. So for this we need to collect, select the Sales column here. And then we need to go to the palm. And here we need to go to the Replace Values and click on the place value. And here, report sales and sales with replacement. Now, we have now, since decidua, all lumber, we can make it true or lumbar. Now we have the cells also. Neck thing is any other thing that we want to change? If you want to change anything, I think we're good to go. This is where we can work with the data and we can transform our data. And then we can start walking. And we can start visualizing if 13. Project 1 - Sales Analysis: And welcome back. So let's do the final simple project and conclude this project section by creating some visuals. So we'll do the analysis, it creating some visuals. And here we are going to do the analysis of this census data. The first thing we'll do, we'll do the self-weight product and then we'll do the sales by store. So since my product, which product has been solved for home and how many sales that happened for product Analysis then, and then we'll do the same spot store and then we do the revenue. What about a month? And then bi promotions for each chromosomes. How many since happened? Okay, So let's get started. So first thing we'll do Sales byproduct. So for data, what I do, I will just drag the product ID here and then drag those. Okay? So see here now you can see for product ID 101, number absence is zero for bi, 17. 17 is 182 like that. You can see three at one like that. Okay, So now what I'll do, I'll convert this into a bar chart. So C, and now we can look at it and we can, and so now to get it in a better way, what I do, I'll make a bar chart. And now you can see for each product you can see that number upsets like this. You can Canidae since bi, product it ourselves by product. If you wanted to change this heading, you can come here and you can the x-axis, whatever you want, the font, everything you can see, that league ball, who want to change the color. You can tell the color at all, okay? So whatever things you want to change, you can do it from here. And you can make it bold or whatever they want. You can tightly, you can tend to your title, you can right-click the product PRO. Okay? And then our y-axis, you can change. Similarly values. You can select how you want, okay, So all discussed myosin you can from your hockey. Next thing is, if you want to change the title of this, some options here we can work. Perhaps six. Okay? So see how you can even you can change the font also. And you can change how you want displayed. Heading for all discussed mice. And we can do here. And even you can change it, make it bold, and make it like this metallic. So all these smallest one thing so you can do you contain the color by putting tread. And if we want, you can make it make it like this, but this is not visuals correctly, so you make it blue. And background also you can change this. You can see macro null so you can X colored background, everything you can in here like this. You can line it. In my day ladder, laptop. At this, you can do hockey. Subtitle if we want. You can ask or ketone and you can put the subtitle. The subtitle Will also come. You can put that the rider, the rider will also cover a little nicer. And all these things you can do from this format visuals. And for general, you can change the properties. Like, how are we done height up this thing. You can do it from here as well. And you can do it from here as well. And producing new content by adding new content, all these things you can do it from your effects if you want to change the background of this thing, you can see This backlog also you contend to make it live. You can portrait this. You continue this background as well, so that you can try some of the lectures. You can change this background as well. Okay? So now we have done Sales by product ID. Next thing we'll do right, Sales buy stuff. If you want to see this in a different charts, you can see, you can put a line chart here. You can put Area Chart. You can put stacked area chart like this. You can hi, you can put down Donut Chart also, Pie Chart, and you can put it in a Donut Chart as well. Okay. So this way you can what did they take? Whatever you want, you can try. I think this is looking better. Okay? Next thing is we will do the sales by store so far that This click on the blank area and when cells, cells, and then my store. So we'd need to track the store, iTunes store, and see it. Now you can get stored idea how many cells are happening, okay? So here also you can go to the front like this. You can put Donut Chart here. So plot stored, you can see that either. And see gone to tender background. You can go here general properties. And he had, you couldn't come and see any effects. You can change the background. This whatever you want, you can fork whatever looks better for you, add your theme, you can decide that you could. But I'm going to put the border edge like this. Ligands and all these things you want to go onto, you can change your store ID. So all these customization, you can do bi along. I'm not going to take much Nick thing is that I went anyway, yes. So for this cell, click on the blank and then track the revenue. And for yeah, I need to order date. Yet I can see it now. You can see the detail here. So per quarter revenue you can see, okay. And the controversy about a month, you can see by looking at this, then you can see that quota. Then buddy has, since we have data one liquid protons aren't in this column and only for one year, you can see that this quarter, one, quarter, two, quarter, three quarter for what? Okay. So the way we can unlike bi, I never knew bi. Yeah. Next thing is we need to lay the cells by promotions. So click on the blank, then Since then, he had we have the promo discount and pour this water little Report date here. So now you can see promulgate is gone. You can see the cell type problem discount here. I put in this, you can remove this or that ID. Wonderful quote, that revenue has gone. So now you can see the promo discount. What was the sale and what was the revenue. Okay. This is how you can Analysis. So let me go. And they put on to insert some Xbox. You can lobby that I spoke here. E-commerce. This is you can put it like this. Here, has a heavy chain, the background color, you can tend to. Next color you want into scholarly content from here. X colored blue, background. E-commerce, Zcash. Since Data Analysis, a simple project that we have 14. Getting Started with Power BI: Now we have the data imported in Power BI and incorporate that report for you. And he had a UTI and reports. So this blank space is the canvas. So first thing what we'll do, we'll try to add some color to this so that our report will look nice. So to do that, what I'll do, I'll go here and go to the Canvas setting. So when you're here, when you select something here and go here on the Format Painter. Information, you can give the name to display the Canvas setting. You can attain the setting up your canvas, how it would look, the ratio. So I'll keep cysteine is tonight. And then I'll go to the Canvas back. And from here you can select a color for your canvas. So you can select an image. And here are the transparencies 100%. That's very snarky way. Now we can see the image here. So what I'll do, I'll just move this and I'll put this color sienna, color, background color. I can save this way. We can customize the background now for Canvas on that, we're going to put out reports. So for now, remove the color and add it. And I use this image here. And here. The perfect to the page and transparency, I'll keep it. Okay. And he has you can put out a wallpaper as well if you want. Okay. So for now we are just going with the canvas background later, if need tried to do more cached minus and on the front. Okay, so now what I'll do, I'll try to create an header for our dashboard or report. So I'll use a textbook here. You can see a textbook option. Click on that. Text box will open here. Now. You can drag anywhere. Anywhere. I'll just leave off our dashboard report here and put HR, employee attrition, the capitalist job creation Analytics dashboard. Okay, So they sell, put in bold. And then I'll align it to the center and try to put this as cysteine. Now see the header. We have. Can you didn't header for outed and header for our. Now, if you want to change the background of this, again, we need to go to the properties. Here. You can see from here also you can tell the high and the high decrease the height. You can increase or decrease the read and the position. You can change it around substance as their title. If you want to enable a title for this, you can protect title. Okay? Then if we want to put some background, you can put here. So here, what I'll do, I'll select a color for this. I just choose this color. So matching, matching to our T to R T, we select a color. So this could try to purge some other color. You can go back really to change colors. So from farm to color changes. So they say, no, we can come to the edit icon. Now, the nice part is coming along. And he sent an icon. Can create a saddle darker and darker. So nowadays looking pretty good. So now we have created that upon our dashboard. So next thing, what I'll do, I'll try to put some Garcia, which will be related to our data so that we have various options, import options. Here you can see the card option, I'll click on card. The card will be here. So now it's now discard the side here. Crashed right like this. And if you want, you can go here and select this. And tried to employ here. So you see here and here to search employee in flight count here. So C and now we can see on this card, we can see that total employee count. Now I'll go and customize. So I can go here. And from here, this option for your visual, you can format your regional, you and how it is looking so you can go through the handle. I can put a title on here. I'll put the bright color and keep it black. I can increase the size and I can align it into the center. Now, the sum of implied com that has come by default, I want to remove that. So how we can do that? I can, I can remove this category level. So you can come here on that general and visual clubs to come to the category level. If you take any level, if you click it on the by default, some are from flight count, the column name will come here. So if you want to remove this, you just click on, off here and it will remove. So now our color thing is reading. So now what I'll do, I'll just call out and try to reduce this. Alright. I'm trying to keep the grantee. So now this is one thing. Another thing we can do nothing we can do. We can go to the agenda. If you want to change the font of this, you can change the font. So I'm going whiteboard. So now this looks better. Next thing is, you need to change the background of this thing. So I'll go to the title, select the background color, and select this. This is for the title. Are they affect? Select that. Now, this is looking good, but the problem is the color black. I'll put it right. In artists with talking to the title, texts, collaterals. Now, this is looking good like total employee. So we have created one card here. Next thing, we can change the shape of this now it is a rectangle. We can. And also how the property, okay, we will come to the virtual border and you just click it on. And now from here we can just see Px. So the border will go to General and then go to that effect. And you just put it like this little. Now it is looking really cool. One least one probability that the border is black color. I want it to be maximum with this. Now, looking good, right? Now it is perfectly fine. The next thing, what I'll do, I'll just copy this and paste because I want to create accretion. Then I want to create attrition rate. And then I want to create that number of current employees. Then I want to create that imply 8 h, so on, so on preliminary and it stands. Now it is becoming correctly so I'll try to so next guard will be next name as pressure. Okay. Then the accretion rate. Then this right? Gabe Raza, current employer. Okay. So now let's just try. This. Looks good. So now we have the cart ready now attrition. So here I want to find how many implies nafta company. For that. Click on HR data and create new major. So it is we can create a new column, a new major cell. Click new major. And here I'll create a new measure, the accretion. Ecretion to take some off hotel imply Coke. Current flow. This really gave us electrician. See here now we have the total attrition. And put all of these, we need to create a custom column and custom majors that we'll see how we can create in the next section. The next section. 15. Creating Custom Conditional Column: In the last lecture, we have added that position by using formula. And that was like a number of important implying minus the current in that regard, number of employees, that company. Now, what we will do, we'll try to do the same thing, finding that Tricia number of employees are treated from the company using a custom column. So for that, what I'll do, I'll click on that transformed data. So now when we click on the transform data, now let's look at and columns. E, column is like yes or no. It imply is left the company. It is, yes. If it is still working, it is not. So here there is no column where we can find the total number of employees that have left the company that had been left the company. So this is your S or no. So for this, what we can do, we can create a custom column where we put the condition like if employee, if it is yes, we'll put one. And if it is know when you put zero and we'll create another column called attrition counter, Kristen. Something like that. Okay, So for that, we need to come to see you. There is the option of add column. So in this, we have three options, Conditional column, index column, and duplicate columns. So here you need to create a column. So let's select attrition. We'll click Create, click on the Conditional column. Here for this new column name will keep the Trisha. Trisha. You can give that to sum count. And here we'll select the column name attrition. And operator will select Equals and value if it is yes. We'll give one. If it is not, yes, no, then we put zero. If it is yes, one, output will be one. And if it is not equal to yes, that concept is no equal zero. Click on Okay. Now look at here. Now we have a new column created here, custom column that is attrition count. So now the values are 10101 kilojoule. So right now we'll put the column type. We need to put the whole number because it's the number one. So now we have changed our data type of this column also to the whole number. Now, we'll click on Close and Apply. This way. We have created our custom column that is called attrition count. Now we'll select this. We remove this filter icon. And when you come down here, you'll see a new count column is here. Now see, yeah, we got the same number that we got in the last lecture, seven. So now this is the way we can create a custom column, column of level in our data set. What I'm column recent count based on the attrition column of level in our dataset here equals yes and no. And with this, we cannot find, find the number of employees who are working in. After some count. For this, we have created a new column at recent cone where we have given the condition in the custom column that if it is yes, we put one. And if it is null, we'll put the GTO. And with this now, decision 1010. So we can perform some operations. We can find the evidence and we can find the total. So this is the way we can find the accretion and we can create a custom column in column ba. Students at the next lecture. 16. Creating New Measure and Adding More Cards: Now the next thing is we try to find that recent rate. Okay, So before that, what I want to do, I want to let this one is not looking good. I want to make the background as white, but all the cards here. So let me do that. That we need to come to the call out here. I'll put black and then I'll go to gentle title also. Texts color, I'll put black. And then background color, I love no fill. And then we've got the effect and the background color to white. Now, attrition, same thing I'll do here, also called Black Kendall title. Let's select black, Macedon and then effect. So now what I'll do, I'll do the same thing with this also. Okay, so this is creating confusion. So let me remove all this. So next is our creator attrition rate. So for attrition rate, what we need to do, we need to find the attrition rate will be the number of employee left the company divided by total employee, right? So for this, we need to create a new major. Click on the right-click on the chart data. And here you can find a new major opsin. Click on new major. And he had an attrition rate. And further attrition rate, what I'll use. Some recent count divided by sum of total, employ them play count. So this will give us attrition rate. So now your attrition rate that has been created, I'll click here. So now it is coming in point one-six. So for this, what I'll do, I'll put in percentage, I'll click here, see there is often a percentage. So now I've clicked on percentage. So it is this thing in-person distorted scintillators, 16.12 per cent. Now, it is swinging two digit if we want to put in one shot in one digit, or do you want to just saw 16%? You can do that. So I think TO digit is looking good. So this way, we have created a new measure called attrition rate. And by using a formula. So when you click on attrition rate senior, we have created our attrition rate new major that we are using for this field, this card here, some of some column, total number of employee left the company divided by total employees in the company. So this will give doctors and read. And then after that we have made it up by scientists. So display that value in the percentage. We click on percentage here. From here you can select the number of. Similarly wanted to show. Attrition rate is 16.12. So now we have learned how to add a new major and create this sustained 0.12. So now we have learned how to add a new major and create a new measure and put that into a percentage. Okay. So next one. The next, I'm going to create its current employee. So far this alkane name, title of this current employee called. Okay, so now I'm creating a current employee. But apparently imply so far this and remove the attrition here. We can put some CCF underscore current implies Alpha. So now we can see we're getting the total number of add these two, you will get the minus 237. It'll get this right. Now we have created another con that each current implies. Next one. I'll create another card. Let's copy paste and here, and change this to title. Two. Threads. Imply, imply it. Okay? Okay, so for average employee is which field will views? We will be using the age column here. Let's select that. So now it is showing the sum of total employees is what we need to put the everyday. So we'll just click on here. And here you can see some average minimum. So if you select minimum, minimum ages 18, and if we put maximum maximum play in Mississippi, and if you put evidence, so this will show you the average, imply it. So again, when we are here, we can rewrite, need to come to the color. And now the average employees is doing as a 36.92, which is not looking good. So we need to solve only the whole number. Here. You can call it y loon visual color. Here you can see Display Units, Otto, and here value decimal places or two. Here we'll put zero. So when you put zero, it will show you that 37, okay? So it will not solve the decimal values. Okay? Next column, next card, we want to create a village. Stay at the company. Here. I'll change this title to the company. So how will we find average day at company? So for this, we have to find the column name, which is your set company working. But there is a column, air sac company, let's say like that. So now this is giving the petal like totally yourself all them. It is summing up all the employees that have worked together. So now here we'll put the average. And so a real estate companies seven. Once a employee joins the company, in place, there, the company's seven. So this way we have created these cards, which is give a very good look to our dashboard. So now we have created card with the help of this card opsin. Okay, in the next lecture, we'll try to create some reports here and complete our dashboard will try to add more and more elements. More and more. Elements of sport will try to add more and more elements, more and more elements and we try to make it dynamic also. So, see you in the next lecture. The next lecture. 17. Creating Reports with Pie Chart Clustered Column Chart and Donut Chart: So in this lecture we are going to add some reports CEO or to our dashboard. So we'll add some charts here so that will get them more insights from the data. So I have already told you that here we have the options to add charts to our boards. So first, I'll start with adding a pie chart. So just to add any chart, just click on that chart and an icon. Can you hear? Yeah, I'm going to create the pie chart, which will be sewing the implied by each department. So like in our dataset, you can see there are departments like R&D and HR. So all the departments are there. So I want to see see in play on my department. Okay. So for that, what I'll do, I'll drag by chat. And in the legend, I'll put the department here, i p. So departmental legends. So now we have the department HR, R&D, and cell. There are three department in our data. So that has been coming in now in the values, what I'll put in values or employee count. So here I'll put the employee count, the values to see you. As soon as we drag the play count in the values and legend in the department, our pie chart has been created so easily and so frequently. See each department is being sold and see how interactivities. Now total employees, virgin 470. When I click on R&D departments. So with this, you can see that 94961 total employee in the R&D department. And amongst them, 133, husband left the company. Attrition rate for that Andes starting 0.8 for current employees working in R&D or 824. Like this. We can get the details for each and every department. Okay. So now we have created this pie chart. Let me just eat, eat, eat. I want to change this heading. That what I'll do, I'll just take it and go to court with agenda and title. I want to keep employee sorry. This is the heading I will give you the font, change it to. Okay, and I'll align it to the middle. So now see how it is looking. Pretty good. Some more modifications we can do. If we want to give some other font color to these things, you can choose any color that you want, Department R&D, these things so that we can do. Concerning the pie chart format, your ritual, general properties. You can increase the size. Items. Charming song or Jason, you can select Advanced title we have seen. Okay? And similarly you can go into effect. You can put the background color for this also like this. You can stay with the white and you can go with any color that you want matching with their team. So for me, I like the white background and I'll stay with this. Okay? So apart from that, what other things we can do, you can go to the Effect background visual border. We can click on here. We can select the medial border at this. So this will come like this. Okay? Digital water and round and round corners. You can click this and see how it will be around it. Okay? If we wanted to do like this, you can go to the general effect, visual borders and you can select title and px. Okay? And then select again the agenda. I'll go to the Effect. Sad or if you want to create a setup. We'll add another effect to this. So C is the setup that will also look good. So I'll keep like this. Okay, apart from that, if we want to give another color to the things slices, you can select now blue if you want to put it. So this will be, this will be blue. This will be, suppose you were to put an L Also like this. You can select the color. So all these things when you walk, he'll be come to know and you'll be more frequent if we want to rotate this. You can see here to rotate it like this. If you want to store in some other way, you can do it. So now we have added a pie chart. Now, this title, I want to put some background color for the implied by departments. Choose the background color, little darker. Choose this one. Okay. So like this, we can put the color, yeah. Okay, next thing I want to create another chart here, down data, clustered column chart. So click on the Custom column chart. And in this I want to add at recenter it by department. So far this algorithm. First Porta X is here. So for x-axis and put the department, so departments, so its axis, I'll put department y-axis and attrition, diocletian. And then for the legend Alberta, gender, gender reported letters. So CEA, fact recent rate by department. Okay. So for this now I'll give the customization option here title Alberta recent rate by department and gender. And just put it into the center. Text color and I'll keep it black only. And the cell is select. And then I'll put the background color, something like this. Okay? And then you can go and select each one, borders and border color. Something like this. Magic to our team. So I'll put an artist coming like this. Okay, so now if you select HR department and male, you can see the details. Apricot, female, male, female. The all the jars fill all the data you can analyze. Okay. So like that, we have created a clustered column chart. The next one is create a doughnut chart here. So for that, we need to click on the doughnut chart. Sorry. I just okay. The eta doughnut chart here. For this. Just suggest these things. Okay? The donor chart, I'll create a rate by department. So far this what I'll do, I'll put in legends and public education. So I'll put education and then I want to analyze the education at recent rate by education. So the next thing is I'll put the attrition rate. I'll drag and put into prison rate. So C and now we can see the attrition rate, my educational qualification of the employee. So now let me customize this first. So I'll go to the general title actress and raped by education. And upon which we are going with. And then in the middle. And then select the background color. Select something similar to background like this on any other color. And we can go with this. And then in effect, just click on the region border on the Select little darker one here are the background and then here. Okay. Now the E, the course has been, have been added. Click. Okay, so now we have added these reports. There's charts to our dashboard. Next, we'll fight to add some more charts here and try to make this dashboard by adding some buttons here are that we say some things here that is called slicers. So we'll add slicer so that when you click on slicer to your own data will change. It's like a filter button or something that will. With that you can make the data more frequently and more easily and it will add a very good appeal to your dashboard. So next we'll add some more charts here. And we'll make this the last four more good and we can add more data. So students like the next lecture. 18. Creating Stacked Column Chart Area Charts and Matrix Report: So now let's add some more visualization to our dashboard and some more reports that we can realize with this libor. So next one I want to add is that I had a stacked column chart. Stacked column chart and select hand here. What I want to port one to port number of employees by age group. Okay. So for that, washed out to sea of each band, I'll put on the x-axis, okay? And then for the y axis and put some often play. So y-axis, like summer, like on my y-axis. And for the late teens and gender. So now we can see, we can see that implies the age group. We can see that in place how many employees are there, particular age and with the gender difference it or so-called male, female weekend and lay the data. So now let me customize this. So yeah, Jane, the title number. By booking center, the font, I'll choose this color that I choose this. And then go to the border with select the con and as ten. Santos. All these also we need to go this things. So the gender and the fact that we have done already settled Guam for this wholesome setup. So now this is the number of employees by age group. So you can see here for 25 to 35. So you can see that if I went over. So now this system next, what I want to do, next, I want to, if we wanted to change the color of these things you can do here. And then go to Legends. Gentle, you can go to the stapes here. Text, background, all those things, effect, icons, colors, and all you can change, okay? X-axis, y-axis, all of those things you can change. Okay? So next chart I'm going to add is stacked area chart. So I'll click here and I'll drive. And what I want to enlarge it, I want to enlighten other job satisfaction by department. So for that, for an x-axis and put them back when. So I'll put department. And then one can lead to job satisfaction. Job satisfaction. Job satisfaction on the y-axis. So see you in our chart is ready, so you can later. Job satisfaction. How much it is there evidence also. So can they able to job satisfaction? Recording Center. Choose the background color and go to that effect. Because borders, but it's something that I could get corner select. And so now we have done stacked area chart based on the department and now we can have any job satisfaction vital department. Our department has at least job satisfaction and R&D and then sells to pipeline. Now next one is, I want to create the job satisfaction rating chart here. So far, this and use the metrics here. Click on metrics and just drag it here. And for this, what are the things I want to enlarge? I want to enjoy it. I wanted to see the job satisfaction rating based on the job and the job rating, satisfaction rating. Okay? So far, this for rows, job role, the job role, those. And for the column, I'll put jolly specs and quality, multiple job satisfaction, and front of values. I'll put some often play called. So CER now, no job satisfaction rating advocated. Now we'll go here and go to the title, switch it on. And job satisfaction operating center. Put the background color as. Let us look at the effect. And Barbara create sad or alone. So now job satisfaction rating has been created. So now we have the matrix also. So this matrix will give you the whole sensitive. I can't get it presented to what is the job satisfaction rating? Yeah. Okay. But even the source never actually seen the whole reports on getting tens and build on what we are selecting. Okay, so next we'll add some watery wash to our dashboard and see how we do fully. Our guys Paul is getting created and how interactivities. So next we'll try to analyze some more data on a dashboard here, who tried to ask them more. Ready for the next lecture. 19. Attrition Analysis by Age Group and Education Field: So now we'll have some interesting reports added here. So the first thing, what I lied, I like that christened by education. So here we have seen in recent edition. And I will see that recent by education free. So for that I'll use the stacked bar chart or stacked bar chart here, on here. And for this, I want to y-axis. I'll use education, education on the y-axis. And then put x axis and the summer electrician. There's some condition, at least some electrician. So see, you know, the best under it to collision-free like Laden's as medical, marketing, technical department and other. You can see the accretion. So now what I do, just put the title accretion by education for this column. I'm just putting Christian, Christian byte as you can freely change the font. And then I put consent. I'll choose a background I'll put then. In fact, you could walk around on a lot of selected cupping. And then sad or alone. And then sad or alone. So now we have it. Patient isn't free. Stack bar chart. Now, I want to change this bar chart color. So how can we do that? We can go through the agenda here. Yeah, boss color to something. Just looking good to know. So you can select color that can suit. I think this is looking good. Looking good. We can go, we can call it. Thank you. Now, this is looking good. Okay. Even we can select the different, different colors for different from marketing as Select, select that and sell it. Now, this way we can change the color of each and everything. Okay. Next thing I want to put some recent count by age group. So part that what I wanted to go, our textbook here, heading. Heading, I'll use a text box on the menu on the top here. You can see the Xbox opportunities there. So this is a textbox. Just click on here. And our text box will be created. And for this whole area, accretion, ecretion by gender, quad different age group. Here I want to analyze the recent rate based on the age group. Okay? So this center and the background color, I want the background color as this one. This one is good. For this. It looks good. Next, kidded, each age group, I want to store the data. How many liquor for male and female, how it is differentiated, how actress, and it's been happening. So for this, I use the doughnut chart. First. I'll create one donor chart here. And for this cell for latent gender. Because I want on the agenda. So legendary gender and then values and some appropriate electrician accretion. Electrician come. And for detail, I'll post the CFO H band and lifestyle for each band in that it is. So now you can see here. Now we can see there are so many H. And for male and female you can see this thing. No. Me squashed this title. For the cell. I'm quite sure the font. We're using the same background color as this. So now I wanted to put this I'm not going to be fine, but it is suing. It is suing for all age groups. So go to filter these four. These four only can do under 25. I can go to the filters to see your data visualize and pull the three options are there. We have not used the filter, they lead. But now I want to filter this data only for the age group which is under quantified. So see you under filter. It is to see if each band is all. When you click on here, you can see the H band here. So I'll select the under quantify for this. And that's it. Now. This is being so on as under 25, 25. Okay. How artists and he's well-known for male and female. Now, I want to create, I want to create this donor chart for each and every age group, like under 25, 25 to 34. Tactics like to 44.45 to 55 and create some more similar doughnut chart and put the filter there. But before that I want, I don't want to solve this. How can we do that? So let me go here. Let's see. We'll switch up the leggings and it will not soda here, but we can see. Now we have created one. I'm not going to be five. Similarly, I want to create for cuneate for all others. Now I want to copy this and paste onto a similar one. I'll change that. The saddle, saddle it to settle. So this also we in effect. Now, this one, I want to change this text In Part D. Cells come here and select. Okay. Then I will copy this, paste it here and plot these help keep the director loves to find food. This time I'll, what I'll do, I'll try to find people. Come here and I'll select 35 to 44.44 to 54. And then I'll create one more here. And for this, I'll keep the title. Agenda, title and hopeless. Hopeless. Here I will select this tuple. See here now I have created this, made on the age group. So now we can see that recently male and female age groups 2,520, 520-530-5305, different people that I know of it. This way we can enlighten the next thing. What I wanted to do, I want to slice it here. They've done the marital status. We can analyze by creating a slicer here we can select whether the half-cell slice up correctly. Some rate for that married people thought that he was to confront the single tuple. So we get that written down. Those slicer we can select and we can actually send is happening for people on the DeVos report. So that analysis will do in the next lecture. 20. Adding Slicer to Dashboard: So now I want to add a slicer to yet for marital status. But before adding the slicer on this doughnut chart, suppose this each part under quantify. This is what 25 productive for. Likewise, here you can see 18 for male, female, and 24, right? So total 38 number of people up in the company. So I want to solve this number here in-between the donor charts to add the visual appeal to our deport. How we can do that. It's pretty simple. Let's do it. For each age group, I'll put the total number of people who are a number of people, those people who have left the company. So total number of tradition here. So for dad had the guard here again, I'll click on the card. And for this card, what? Usda. Alright, Duration, column, vector, sum count here. So now it is showing the electrician, right? But I want to solve for the age group made on the age group 25. So we can come to the filter and CEO. Now we, it is being shown for all. So now from here, I'll search for CF and I'll add this fill here. Predictor type has been added under age band. Now I can select under 25 and now it is showing it in place currently 30, Correct? Right. So now this hasn't read it. I'll just make it a little. And we can go here. Carlo Trello. Okay. When it told him OK. This summer practitioners do that. Okay. So now we have to move that. Okay. How I can put so Sierra Nevada county is being so I'll just copy this. And I'll go to the free. Select five to 25 to 30, 34 or not. Hello. Okay, that is all. Now, this is also being so on. Similarly, here. Filter here before 35 to default. Okay, Next question to try and select here. So now this has been done. So based on the department, the railroads getting to this point, everything. Okay. So now the next thing is we'll add the slicer to our dashboard and with that, our desk port will be completed. So you can see here there is a slicer option on here. Just click Slicer separately. So now with the slicer is created here. So now here I want to slice the data. Antiport built on the agenda, not, not gendered, actually marital status. So select the marital status and worked on the film. So now see you for the, if you select the worst, we can analyze formatted for single, okay? So on the data selected, know that's what will it be dynamically change. Okay, so now what I know, what I want to do, I want to go into the slicer settings here, which will come to the slicer settings. And here then opsins like dye dropped down. So when you click top down, it will become a dropdown. Okay? When you click on work accomplished, it will be listed. Okay. But yet I want to tie so eloquent the title and I'll just drag it to the top. Yeah. Okay. So now those slices, Slicer header can now select day was married, single. So based on that, you can analyze the data. Report. Next thing I want to just customize this so that it looked like code must be diverted time. The background of this things I'll go to Effect and select something like this. Select this. I'll select this one, I guess. Yeah. So okay. And this text color will not good. So now our dashboard is almost created. And now you can see made under marital status, single, married. So you can see made on this. You can realize that if you select married, so the attrition rate is 12% for malate implies for the Wall Street is ten per cent. For single, it is 25% more than 25. So that list is ten per cent with the Welsh people. So diverse people, less tendency to leave the company. For unmarried people, 12%. And for the singles, they have the tendency to change the company very frequently. And that's when you'll see that recent rate is 25 point, the company very frequently and that's where you'll see that recent rate is 25.53. The same way we can see that HR department sense. And for all the departments we can analyze. So than the male and female, you can select attrition rate by education and you get some field also you can see for medical or marketing or technical department. So based on all the data, we can analyze force, nomadic people. You can see the job satisfaction rating for the single. You can see the job satisfaction rating for the diverse people. You can see the job satisfaction rating. So connect the under 25s, divorces, Zillow, right. Format it also under 2050 or single under 25 is coming. So our report is very correct, right? So this way we can create a dashboard in Microsoft Power BI and we can analyze our data. So you can do that attendance is by creating such task. 21. Final HR Employee Attrition Analysis Dashboard: So this is how our final dashboard in Power BI look like that we have created in this class. And if you follow the steps, you will get this very interactive dashboard that when you click on this slicer. So you'll get the details for that particular item like the diverse people. You want to see how people like people are diverse, how is the rate. So you can see hotel employees who are diverse or 327.33 people have left the company. So attrition rate is for a among the diverse people is 10.09 in place where diverse or to 94. And department wise also, you can see for the R&D department, there are 224 implies and none of them. And then we have the sales department. And then you can see the HR okay. Format it also you can see. So if you want to see for the male and female, you can click here and you can see that the veins further treatment matted, OK. And see you can see the female and married. You could similarly, you can click here, you will see the female and D wash or to center it is quite high. 75 per cent. Meal diverse or to center it is not that high. In the same way. If you want to see this is the complete data. If you want to see for the education, how people are likely to support this, you will set a high school. For high school. People want 17 or 18% is the attrition rate is quite high. And for this, if you are unlike the male and female, you could click on this part. Male. You can see for high school, male, this is that tissue draped for bachelor's degree. You can see actually Sunday to 17.31. For the doctoral degree. To center it is 10.42 for the master's degree, 14.3. And among this, if you look at the department twice, you can see all the things you can click and see. See here, if you look at this chart, you can see their satisfaction in sales department 2.75 in R&D is 2.73 and each added is 2.6 launched. So this is job satisfaction by department. Job satisfaction breeding. You can look at here, you can see this is very interactive with asphalt that we have created for education feel like life sciences. Life sciences you want to analyze, you can allay the data automatically. You can align the data for the marketing, you can latch for the technical field. You can analyze all these things you can do and analyze the data. So this is how we create interactive dashboard and visualization by adding these visualizations, these reports into our dashboard. This you can click on here, published and you can see it with your top management or with your client. And it will be, they can open in the tabular setting, in the Power BI mobile and they can look at it. Okay, So this is the, and this will be the project for you also, you just follow the steps in the lectures and just try to implement this and say other your dashboard. And you can play with other color combinations and, or you can choose other visualizations available. You can choose other charts here. And you can create your own dashboard of employee attrition and HR analytics. Okay, so I hope you understood this class and you have learned from it. And you will create stunning visualizations and dashboards with by learning this class. 22. Financial Data Analysis Understading Data and Data Modeling: Hello and welcome back. In this lecture. And from here on we are going to do some projects where we will be doing the financial data analysis. So what is financial data analysis? Financial analysis is analyzing the gender leader of a company for their profit loss ratio and several other financial things like, what is the loss? Which area is making profit? Which region is making profit? What, what are, what are the, what are the marketing budget? What are the spans? Okay. So all those profit and loss and several other finances is that we do are called finance analysis. So that we can do with Power BI as well in a very crude final. So let's get started and look at the data that we are going to analyze. So what I'll do, I'll data. It is an Excel workbook, so I'll click on Excel workbook here. You can click on the Get Data and Excel workbook. Okay, both ashamed. So click on the All we have the file called Data Financial dot Excel. Okay, So we'll just Import Data. Click on Open. And it will be important in our Power BI. So now, see we have this data financing Excel file. Here you can see the several options like Directory charged up account, calendar, and then with this entry, then another four as well. Pbl underscore calendar t will underscore chars. So these are duplicate it with them. He will underscore right here. We need to import this once. Okay. So I'll click on the general ledger. They underscore calendar, charged up account directory. Here. A few suggested tables or their cashflow, see few sadistic tables or their cashflow statement and SOC state when data is being selected because that has been highlighted in our excellence it, so we'll see that later. That's why I didn't that. Okay, so in all these four tables, bar BA, and when you click on the table, you can see the table detail here. So table calendar is containing date, year, month and day. And T will charge the per cartridge containing the account key report, class and subclass. And subclass to account and several columns, okay? And then our table containing entry number, date, territory key account key, details and amount. And then territories containing the tariff root key, contrary. And begin. We'll see and we'll enlighten the toughest thing. We will load this. You can go and transform the data as well. But our data is quite clean and also we will not go to the transform data. We'll just load it. And if we need to do some modification, we'll do upper loading the dataset. Okay, So click on load. So now our data files and tables are being loaded in Power BI. Okay? So see you all this TBL on your calendar. And it is also a number of that. Okay? So now you can see here how power BI has loaded the data and insight here in the data section, you can see here the real underscore calendar tubular discord charged up account. They will underscore underscore editor. These four tables has been added to the new click on here. You can see the columns in those tables. Okay? So now we can see that. Now you can see here there are three things, three sections of the portfolio that will create the reports here, that will be visual here. So if we want to visualize this, Excel sees our general ledger. You can visualize here. Next one is TableView. Tableview will give you that table view. So when you click on the table here, it will show you that data tables. Okay? So let me explain you that table. So first one is table underscored general ledger table. Here you can see the first column, entry number, which is containing the entry number field. And then we have the date on that particular date. Then we have that. Then we have the territory key, then we have the account key, then we have the details, and then the amount. Okay? So these are the six columns, 123456 columns are there in general later. Next is c. Now, here, there are two things. Entry number, enter, tricky. Next one is, We'll see Josh sub account. The charts. You can see that count key report, class, subclass, Subclass to account, and sub statement columns are there. Similarly in table territory, you can see that territory has been identified, are given a key territory, key USA, region, North America and off. Okay? So this is the tricky then we have the calendar table which is containing the date, year, month, and day. So these are the tables that we are going to use for our thing is Model View, Model View, Model of our data. How the different tables are connected. So that we can see here. So let me the array. You can see inside this modal view. There are few relationships already been established by Power BI and how it is done. Because if you can see that they start this table T and it is having the field called Date and develop charts. Not having date field, even the table of poetry and not having debt. But if you can look at these two, it is having account key tables and charts up account also having a county. So if you click on this relationship, this arrow, you can see which field husband, which field has been mapped. So see your account key up table TL is mapped to the account key of the charts up account because both are the same column. So with these two key common in these two tables, Power BI has already established that relationship between these two. Similarly, in that territory, three. And table here, which voltage common, you can see territory key. So that's why the Power BI has established the relationship between the territory table, Yield table. So now these three tables has been already associated with them, with each other. Because if you want the table to yell, you can go to the art. You can access the charts up account by using the account key. And from here, you can access that. They're tricky by using the trick. You can access this table from here. But this table calendar has not been associated. Why? Because see, this table is not having any, having date, the month, quarter and here, and these are the fields are not having this charged common, but table is having one common that is state. So what we will do, we will just click this date and drag this and mapped to the tables yielded C. Now as soon I drag, there is a relationship established with these two. The date field is mapped, okay. And see your y this this date, and this date was not related by listening to people, not established by the Power BI by default because this is the calendar, this is the date field properly, but this is not a date field. That calendar icon is not there on this field. You can see here, but you can see here. But the calendar icon is here. So these two fields are different. That's why Power BI has not able to stably the connection between there. If this would have been a date field, table B, I would have done this for us. But if it is not done, We can do it manually. Next thing is, this is a date field, so we can go to the property and we can go to the Edwards. And here you can see datatype is late here, right? And see the datatype for this date field is date, right? So similarly we can go to the DL table date field, and we can go to the property. So now we have the datatype wait here for this as well. Established that the lessons in the model view. And you can go to the Table View, you can switch to the model where you can switch to the report view. Few other things we can do in the model view. And that is we can change the fill type as well from your CEO. The year field in the, in this is like aggregated, but we don't want to be aggregated. So we can come to the property and here we can go to the Advanced. And here you can change this summarized by from some to none because we don't want ear to be summarized. Okay? So this way we can do these things from here as well. The amount is summarized. That is okay because we want our amount to be summarized. Okay. Any other field, if it is needed, we can go and check and change from here as well. Apart from this, you can change the date, type, data type from here as well. You can change the name, you can change the format, you can change the summary digestion thing. You contain the data category as well, okay? Data type also, okay. All these things you can do here. Jane the table here, and do that, okay? So this is how our data is related here. Next thing, we will try to create some reports. Seems like the next lecture 23. Calculating Sales: Hello and welcome back. We have seen how we can do the Data Modeling in power bi in context of this cell financial Data. So now all the tables are connected. We have correctly model our tables. Next thing is, let's start with visualize this and we'll go to the Visualization report view layer. So add I told you earlier Report View, tab, TableView. And then we have the model will so model we have seen, they will view also we have seen, next thing is you will see how we can build our visuals. So the first thing I want to do with this financial Data, I want to calculate the sales. So to calculate the sales first thing we need to understand, and we need to do the first thing while I'm willing visuals and create liquid relation, we need to choose the right visuals things. Okay, so here you can see there are few options like will visuals. And here you can see the values Chart, option Stacked bar chart, then Column Chart, Clustered Bar Chart, Clustered Column Chart, flush, Stacked bar chart. And then we have the Stacked Column Chart, line chart, various Chart Options here or there, Pie Chart, Donut Chart. So now we need to think properly and we have to choose these ritual correctly. So now I want to create the sales. I want to calculate the sales. For that. I'll choose the matrix one. Okay, So we'll start with the Matrix. So to that, just click on that. And see here one Matrix visual will be added here, but there won't be any. Display their own items, sewing, sewing here, because we have not selected anything. Any field to be displayed on this Matrix which will to select that. See it now, right now, this matrix is there, but that is nothing to radon, right? If the empty matrix. So when I select that here you can see here all the tables that we have important in our Power BI and on which we are working are listed here with all the fields. Here you can see for rows and columns and the values, there are many options available individualize and down here, the add data field at will see add data fields here like Rows, Columns and values. Similarly, we have the filter option here, add data fields here at Data Filson add data for you will understand this, add this as well. Okay, so now we have this item ready here. We need to put the rows and columns and the values. So first thing we go to the, because we wanted to calculate the sales will go to the table, the bill underscore GL table. And from here, we will select the amount and we pour that into the valid because we want to calculate the sales. So I'll just put it here and see you as soon as I kept these values here. Some of our MT or the amount here, some of Total Sales has to come here, sum up among catch, come on the chart. Next thing is, I want to put some more information on here show I'll put something. So for that, I'll go to the Chart of Account. And from here I'll select subclass for this row. So now you can see here we have the subclass adjusting set cost of sale and sum up among sales are coming here. Why Up selector, this subclass, you in the report view. And see here, this is the table up Charts our table here. You can see account key report. In that report Column. We don't have any Sales item in the class. Also, we don't have any Sales item in sub-class. We have the assets, liability, owner's inquiry and Sales. See you in the subclass. We have the sales, right? And even in sub-class we have tap sales, But that sale is including the They've done upsells. Similarly in the sub account, you can see Sales and return cells. Both are the materials, while this is two, That's why we have selected the subclass. Return back to here. And now what I want to do next, I want to filter this by just selecting cells. I'll just select cells here and see your now, we have removed all of that unnecessarily things and Wanli is being sold here. Next thing I want to do, I want to do, I want to add in this matrix. So far that I'll go to the calendar. They will pick the ear and put that into the Column. Now you can see here for each year, Total Sales has been calculated. It in 1920, okay. If you want to unselect this, it will be coming like this. And if you select only cells, it will be coming like this. This way we are calculating the total sales for the particular ear. Okay, so I hope this is Catholics. I will try to do some more formatting on our Matrix. Since I've the next lecture 24. Formatting the sales Matrix: Hello and welcome back. So in the previous lecture we have calculated the sales. Now I want to format this matrix that we have created. So one thing you can do, you can just resize it like this so that it will look better because it was captioning lot of space here, unnecessary space. So just make it look good by resizing it so that we can do from just here. Okay? Next thing is we want to form it. You just select the matrix here. Okay? And then I'll resize it again because we are selecting it here. Let it be like this for now. So select the visual chart which you are making. In our case, this is Matrix, so can select Dax. And here you can see there is a build visuals. And then beside that, we have the option of mature visuals. So we just click on that and we'll try to format our visuals. So first thing is I want to the subtotals are not unnecessarily because there is only one value. So we don't need this, our total height. So I'll go to the values. You just click on the values and scroll down. Here you can see at Columns are proto-life, sum is the right. Just select the column. Subtotal has gone. Next thing is row Subtotal. I'll click off and see. Now our thing is looking much better, right? So in this way we can remove the column subtotal, rows, subtotals. So just click. That will be gone. Next thing is, if you go to the values and tier, you can select the font size from here, font-style and fantasize. So this is little less nice, so I'll try to increase that. So see you now, the font size for the values has increased. Make it 50 Latin issued need. We can reduce it take if you want to change the text color, you can select here and you can change. Okay? So they typically for now it's black is good. Next thing is, we'll see that Column Header. Kilometer also will increase the font size. Descriptive will make it go to the writers. And you can also make, and if you want to change the font color and all, you can change. Okay? So now we have formatted correctly. Next thing is what we want to do. We want to add the heading. So to add heading, we need to go to the rituals. And we need to come to the title here. And then we need to put the title. Yeah. And then we need to select the heading, whichever you want. Let it with three and the font size if we want to change the content here, and font-style and font size you can tell. Then align it to the center. So now our sales Matrix is ready if we want, you can add the background as well, so you can come to the effect and you can select the background. Okay. Bejeweled border if we want to create that also you can create. Okay. So will I explored these things while doing more exercises? Okay, so for now, our sales Matrix is reading. The next lecture 25. Analyzing Sales by using Drill Up and Drill Down: Hello and welcome back. So in this lecture we're going to analyze the sales data in a more detailed way. So right now, we are seeing the sales data for particular year. But I want the opposite so that I can enlight the data of 2018 by quarterly or even bi monthly basis. So that is not possible right now. But that is what we are going to do in this lecture. We're going to Drill Up and Drill down this matrix for the monthly and quarterly data sets data. So for that first thing we need to do, see you in the columns. I have selected ear here. So I have to remove the ear here and we need to put that date, columbia. Remove the ear and put dates in the column, date column. Now as soon as I put the date, our data remain the same. Same sales total is coming for ETO. But when you look at the column here, that is opsin of year, quarter, month, and date. It means that we can look at our data bi yearly, by quarterly, bi, monthly and bi, bi. Okay? So if you look closely at this matrix, now you can see the opsin of you can see the job sense of these adults. One arrow to arrow and then this one for hierarchy. Okay? So these arrows has come off because we have selected the date which is having multiple values inside it. Yet, quarter, month and day earlier it was not there when we have selected one of the ear. So now, when I click on this arrow, what it will do, it will take us to the next hierarchy, next level in the hierarchy. So next level in the hierarchy, quarter. So C and now our data has been splitted for quarterly, quarter, one, quarter, two, quarter, three, quarter, four. And if I next one more time, it will be monthly. But you can see here the ear has been not mentioned for which Year disease. So this January is the sum of the sales of the 1,819.20 all three years. This February sales or the combination are total of the February month up to 18, February month up to 19, and February 20. So this is sum of sum of total up all those three years. January of all those three years. So January sales in 18, 1920 Total is this. So if you want to analyze like this, you can do that. But it is little difficult for analysis purpose because we don't want the sum of all three ears or January month, right? I want to analyze here by ear. So for that, we need to, if you want to go back to the ordinal thing. So you can click on here, Drill Up. And now we're back to the ear right data. Okay, so how we can do bi yearly. See here this thing. Expand all down one level in the hierarchy, expand all down. If I click here, you will see. Now you can. We are data on the quarterly basis, but 2018 quarterly data quarter, one, quarter, two, quarter, three, quarter four has been Sonya for similarly for 2019 also. Similarly for 2020 also. So now we have the clear it is being clearly soon as quarter one of 2018, quarter two, quarter three up 2018, and quarter four up to it. Similarly for 2,019.20. If I put one more time, it will show us Year wise. So here let me what 2018. This is the data for quarter one, January, February, March, April, May, June, July, August, September, October, November, December. Similar leaf it will suffer the 2019 quarterly. And you can analyze the monthly as well as the monthly as well. And here you can analyze for 2020. This way, we will get the option of enlarging the each year by going to quarter one, quarter, two, quarter three, then you can go to the month as well. So this is the opsin, okay? So we have seen two options. One is this, which will soda Yet rise some. And then we have seen this option, which will saw that if for each year it will, so, okay. So which ever is required for you, you can use that thing. Next thing is we can see one more option here, this one, click to click to turn on Drill Down. If we have in this one and this one. Now I'll show you what this will do. Just click on that. So now if you see that these bi, okay, so now it has been selected and it will like this, okay? It means it is selected. Now, if I click on to toggle 18, you can see it will only the data for 2,018.2 thousand 19.20 data will be hidden. So for 2018 you can see quarter, one, quarter, two, quarter, three, quarter four. If you want to analyze one little Turnitin, it will. So you won't lead to target in one. If you click on quarter one, it will throw you the quarter one monthly data. January, February, March, quarter two, quarter three of 2018 will be hidden, right? If you click on January, it will show you the January data, okay? You can go up, up, up. And if you select on Total 19, it will show you Total 19 quarterly data, quarter one, quarter, two, quarter three. If you select on quarter three, it will show you the quarterly data month, July, August, September of what was the sales okay. Similarly, you can select grantee quarter one, quarter, two, quarter three, select quarter two major data equals. So. So this is the, another way to analyze where if you want to Drill down to Wanli particular year and analyze, you can do that from here. So this is all about a drilling up and down the data using Power BI. So this is so cool feature here, right? So now you can unselect it. You can see here how it is mixin all the three years. Some will be soon here. And if we select, Okay. If we select this, this will source the for each year is Drill so as the quarterly, monthly data. Right? If we select this, it will give us the option to see right here my hiring other options we Drill. So only part Total uniting. So I hope this is clear for you, hope this is clear for you. And you can also do these exercises. You insert the next lecture 26. Sales Revenue Visualization: Hello and welcome back. So in this lecture we're going to do some more visualizations on the sales they don't want. So now we have the sales Matrix. Now I want to add some Visualization so that we can analyze the cells more in detail. Okay, so now we have this matrix. What I want to do, I'll just copy this matrix Control C and Control V, copy paste and make a duplicate Matrix from here. Now next thing is, you can see here the bill visuals. We have various options available to create visually digest and ranges Chart option. So if I select any of the Chart option here, it will. The blank here. And then we need to select the fields from here. And then we need to, but the Texas y-axis and it will start throwing the data, right? So after selecting, you can now select the things and it will show you the details, okay? So like this, so this is the way to create visualizations. But I want to, I don't want to do like that term because what I have done, I have already created this matrix and I want to create which lessons based on this. So I'll just select this matrix. Then I'll go to the visual, little visual here. I'll select support. I want to create a line chart. I'll create a click on the line chart and see, you know, are Matrix has been converted to a line chart where it is two-and-a each year Sales. Okay? Now, if you see we have the Drill Down to our level here. But again, it is summing up all, summing up all the quarters of the different years. The quarter one here is the sum of the sales of quarter one after 1,819.20. To get that right. We don't want that. And see the this Rindler opsin, which is our level here that is not available here. That is because you're now we are looking at the sales will leave, but on the axis, x-axis you can see two items are there, subclass and the date. So because of the subclasses they're distributed is not coming out line charge, so the subclass is not needed here, so I'll just remove this. And as soon as I remove that, you can see now we have to explain all down one level in the hierarchy is available. So if I click here, you can see here, now we have the total in 18 quarter, one, quarter, two quarter three data than 2000, or then Total 19 quarter, one quarter, two quarter, three, quarter four, similarly for quarter 2020. So now we have the sales of this thing. So now we have the sales. How the sales is changing over the water, that is our level for total unity. We can see the pattern here, right here. The quarter one is less and then it is increasing up to quarter four. Then again, order one is starting with the less and then quarter three. Quarter four is the maximum disease, the pattern of the sales seen embryo. So now we have a, now you can drill down further. We can select like this for every month it will. And if you go up, you can select any year. You can select 2019 for Total in 1940, 2019, January. All those things that we'll go over back to the additional one. Okay? So this is one. Visualise them that we have created where we can see the pattern. How does sales is happening? Now, I'll just copy this as well and get it another. Okay, so now we have this. Next thing. What I want to do, I want to get it. I want to visualize for each year different line charts. So to do that, what I want to do, I will remove the ear from this x-axis. I'll just click on reboot. I have selected this chart, so everything's in will happen on this year. So remove it from here now. Okay. And Next thing is, I'll put this date in the legend. So I'll just put lessons here. So and after that, I go to the date. And here I'll select the date hierarchy, selected hierarchy and see, and now we have the line Charts separate for each year. This H, 2018, 1920. So each line is representing quantity also if I select on it in here. So this is the data for total unity, Tattaglia and front light Total 19, this 1.2 times 24, this one. So this way we can select and see yet if we select to terminate in here, all the residuals will change. For Total community, I select the 1900s, all the visuals will change. So that is the magic of Power BI. Okay? So now we have the E for each year we have the line that you can drill down further as well. Sorry, not this. We want to see the month twice you can do this. Okay? And if you want to drill down like this, you can draw data as well. Okay? This each part, the DYs, okay. So far monthly if you want to analyze, you can enlight like this, see how it is changing month on month. For each year, the sales Revenue is tending that you can do here. If you want to change the color of these things you can do by going through the visual. This format rituals and you can select lines, colors. There are three colors. You can select any color if you want your content to support. You want to select make it a law. And you want to make it this one red. And this one if we want to make pink, that you can do. So you can go to the format and you can select that if we want to call x-axis, y-axis, that also you can do. You can change the title. So here we can, for Sales Revenue wise, you can put and okay, So like that you can do, okay. So I hope what this is clear and firm that if you want to gain the subtitle and all that you can do by going here. The next lecture 27. Adding Slicer to Reports: Hello and welcome back. In this lecture we are going to do some more things with Power BI and us. As we know that if you go to the table view here, you can see that is a table that is called Territory table. And if we look at the titratable, it is containing the country and region, right? So when we come back to the report view, I want to see these Sales Revenue data by country wise. How we can do that, what we can use Slicer. Slicer is like a filtering by using my Neogene particular field. Okay? So I will know that here. So before we move to the Slicer, let me on in this too, I have an ending, these things. So now you can see, we are seeing so many things on this chart, right? Visualization. I want to remove this month and sum up along from the x-axis and this quarter-on-quarter to. So to do that, I need to go to the formatter visual option, select the chart format, go to the parameter visuals, and I'll just click on off X-axis. X-axis has gone the written things. The quarter-on-quarter towards coming so vague. So I'll just remove that. I will make our visual little more clean. And for y-axis also remove this. So now it is pretty clear. And now I don't want the sum upper mountain a month also. Go to the x-axis inside x-axis and click on tight off the tile. So X axis gone. Similarly we'll do for the y-axis as well. So now our this thing is pretty clear, okay? Similarly, if you want to do, you can do for this as well. Okay? So next thing is I, I, I wanted to analyze the sales revenue by country. So for that, what I lad, I add a slicer here. So you can see here that results Slicer option. Let me find it out where it is. Okay? That this is the Slicer option here. So I'll just click on here. One Slicer will be added here. Now, I want to select a field on which this Slicer will walk. So I'll go to the territory and I'll select the country. And I'll just select the country and drag it to the free option here. So country, so see here now we have a Slicer which is having the list of countries. Okay? So now if I select Australia and tired, all the visuals are getting changed. All the values are getting. So these are the values ASHP or the Australia, this system, Australia, this is particular to the Australia sense and never know for that particular country this Hitchcock Canada, this is for France, this is for Germany. So if you want to analyze for a particular country how the cells are going to sell strained are going that you can do. This is for the USA. So this way the Slicer see all the, all the Charts, all the visuals are standing together. This is where New Zealand for Germany. So this is the power of Slicer in power bi, who just select and you can analyze a particular country. Okay? So this way we can just Slicer. So this is the, we can use Slicer. Okay? So if you want to analyze your particular country, you can just select the Slicer and you can sell it. Okay? So this is the way we can add Slicer to our report or dashboard, okay? Same type, the next lecture 28. Analyzing Profit and Loss: Hello and welcome back. In the previous lecture, we have created the sensitivity. And that's what we have get to where we can see the cells. Yet wise, we can see that since it only via country, we have added the Slicer as well and we have done this. Okay? Next thing is what we are going to do in this lecture. That is what I'm going to show you first and then we'll do. So in this lecture we're going to create Profit and Loss. So we will create this matrix here where it will throw you the Profit and Loss Account, and then it will show you the total. Okay. So CFR trading account it is one the total profit. And then non-operating Operating also to swim Total. Okay. And then we'll see there how we can calculate the gross profit, how we can calculate the Operating Profit, and how we can calculate the profit before tax and interest and tax. And then we'll calculate the net property. So this is what we are going to do, and we'll also add the Slicer here. Lakebed. On the contrary, you can analyst will add another Slicer that will be the region based on the region also you can analyze. So this is the goal of this lecture. Let's do it. So to do that, what I'll do, rather forget about this. Hit, Okay, So we are on this page right now. We have done till late. This is our goal, will try to create a dashboard like this, okay, In this lecture. So to create a dialogue that I have shown you, what I'll do, I'll right-click on here. And from here you can rename the page, okay? Whatever you want to give you can give you, okay. And you can see there is another option called delete, height, rename and duplicate. So therefore options, you can hide the page, you can delete the page, you can read them. The brilliant could duplicate. Here what I'll do, I'll duplicate the piece. Now. I have replicated the page and you just double-click on that and you can give it a name. Profit and Loss, PLN, P&L. Okay? So this is what we're going to create. So here I want to create a matrix. So for this Profit and Loss graph is not required, so I'll delete it. And then this also we don't need, so that also delete. I need a matrix for this. Okay? So first thing, that's why I have duplicated the sales page. Okay? So that's what, because I don't want to recreate the matrix because we have so many things on this matrix, right? So it's better to proceed with this. So next thing to Calculate Profit and Loss. What I want to do, I want to modify this. So here I want to just go to this filter. And here you can see there is a filter or fight subclass. So what I'll do, I'll just move this trade-off. So the very first step is remove this subclass for data. So as soon as you remove the subclass work W can see the details of Sales, right? It ends up, that ends up the revenue. Okay. Next thing is, I want to filter this bike report because if you see the this thing Chart of Account, you can see here that is Profit and Loss is there in this Chart of Account and that it is under this report. Okay. So next thing of what I want to, I want to Calculate the Profit and Loss. So I need to have this Profit and Loss. So for that I'll go to Report View. And here I want to filter this. And I want to see all the, all the amounts are coming here, but I want the real amount to be shown here, those which are related to the Profit and Loss. So for that, I'll go to Charts, count. And from here I'll put that report and I will put it into the filter. Okay, I filter by report. And here, after putting a report into the filter, I'll select one live Profit and Loss. So now you can see here now I'm getting the Profit and Loss for various accounts. Okay? Next thing is, I want to add a subclass here. So here we have the Rows, but I want to add the class here on this row Class and class will be up class subclass, and then there is another subclass to also I want to add, okay? And then I want to add the account. Account also here. Okay. So now we're getting the interest and tax, non-operating, Operating and trading account. So all these details we are getting, next thing is we'll just expand these things and seawater. See your trading account, cost of sales and sales operating and non-operating interest and tax. Now, I want to expand operating expenses for that. So I'll click on here. So here, Operating expenses, Clinton administration, marketing and sales and distribution. So now we're getting a clear picture. Now we can go for Profit and Loss. Next thing is, I want to filter this bike class. So see you. Trading account should be up and then I'll operating account, the non-operating then interests and tax, but it is not in order. So we'll go to this three dot. And here you can find an option for sought by CLL. Click sort by class. Okay, sorry, by class. So now and click on class one more time. So now, after clicking twice, now discovering trading account, operating account, non-operating and interests and no disease in order, right? So how we have done that, when you click on here, you can widely sought by a single thing. Okay? But once we have done with the class, I just doubled, clicked on class. And then again click doctrine. This is just a trick. Now it has been sorted by multiple options. Okay? So now it is in order. So now it is in order. Next thing. We have already having this Slicer you, so when we click, we can see the country Specific Profit and Loss. So see this is the for sales and cost options. Okay? Now, I want to add the subtotal for tidying up on what is the gross profit for trading account and non-operating and all those things. Okay. So for that, what I want to do, I want to slowly at the subtotal. So for that I'll go to the visuals. And I'll go to the select this. Go to gender. Then click on this row subtotal. So now as soon as I clicked on row subtotal, we are getting the subtotal for the trading account. This is the gross profit for two threonine. Threonine and two times grantee for everyone. But everything, we're getting the subtotal here. Now, another thing is this subtotal is showing on the top. I want to show it on the bottom. For that, we need to go to the where I lose row, sub-total columns of protons. Okay? Okay, let me figure it out where it is. Okay, we need to come to the row subtotal. And here you can see the total. And here you can see the position. The yellow will click on bottom. And as soon as we click on bottom, see here now the fur trading account costs, sales and cost of sales. And total is coming here. Then operating account, sales and distribution, marketing administration and Total is coming. So this is a little clear now, right? Okay. So now we have learned how to get the subtotal here for each and every account. Okay? So in the next lecture, what Will do, we will try to find the listing Gross Profit, Operating Profit, profit before tax and net profit. So see you in the next lecture. 29. Calculating Gross Profit Operating Profit and PBIT: Hello and welcome back. So in the previous lecture, we have created this Profit and Loss Matrix. And we have seen how we can order Total here. Okay? Now the next thing is I want to find the gross profit. So far gospel, if we can take a matrix, I'll create a matrix. So I'll just select a Matrix you Okay? We have Greg, just the space. Okay? So are the gross profit. Create this matrix. This matrix, how to use the DL table? A table. And who are these among, invaluable. So I'll select among can put that into the value, and then I'll put date Column C. And now I'm getting them. Profits for 1919 to 29. This is the total for all on the DFS. Now, I want these to be filtered by class because I want the gross profit but creating local. So I'll select the class from peel back into the filter. And yet I select one liter trading account. So C and now we are getting the Gross Profit 438-324-6233 to four same allantois is coming. So they said the Gross Profit for trading account for 2018, 19.20. Now I don't want this portal. So to remove this total, I need to go to the coelom subtotals. So I'll select this and go to the formatting. And here I'll go to the Columns subtotal and I'll click on this. Now. That has gone. So now this is clear, okay, so far. To turn it, this is the gross profit. I want to add the title and click on title and title as Profit and align it and get them micro colors. So this is the gross profit for our Column take. Now next thing is I want to create the Operating Profit as well so that I will just copy this and paste walk on this one. Okay, so now we have the Gross Profit, Operating Profit. So first let me the title here, booklet. Think Profit. Okay. So now that is done. Now I need the operating profit means we need to take into account the operating account also. So for that, I will go to go to the account. I put that into the filter. And then here I'll select, Okay, Sadie, Here in the class, I need to select the operating account. So now, now we're getting the Operating Profit. Okay? Okay, next thing I want to create this D Profit Before Interest and Tax. So I'll just copy this and best. And here. Now for that, we need to just select in the class, we need to select the non-operating as well. So I'll select this. So now if this is what the non-operating, Operating Profit, We need to chain this real quick. Can we need to change this? We add, we can get gentle title and this will be the Profit Before Interest and Tax PBIT. Okay? So this is Profit Before Interest and Tax. So that's what we have created. So MT is coming same, okay? Next thing is I want to create this net profit. So for that, again, I'll just be this and paste. And for the Net Profit, We need to remove the interests and tax. So for that, here, I need to select the class we need to consider learn best and taxes way. So this is Net Profit, so I'll just this general land title tiny to select, make it Net Profit. So this is how we can calculate the net profit, Gross Profit, Operating Profit, and Profit Before Interest and Tax. So you just put it here. Okay, align it properly so that it looks good. Okay. Now, veiled under country also, you can find the Profit and Loss for Canada, France, Germany, dot land for USA. So all these things are coming correctly, okay. So you can see it's coming collective on the right, the one which I have created. Now I want to add one more Slicer here when it on the region I want to see. So when we have to go to the Slicer, select one Slicer and the Slicer. Here what we have done, we have kept the free, Let's country here. I want them in the field. I want report the genital region from the territory and are going to look and see here. Now, Germany. Select gentleman needs. So C, Now we have the region Europe. Europe. In Europe, there are three countries in this table, France and Renuka for their Profit and Loss. You can see or North America, US, and Canada. In this region wise, country wise, you can select and see the Gross Profit, Operating Profit, Net Profit, Profit Before Income. Interesting Dax and Net Profit you can see. So this way we can analyze the financial Data. So let me adding this. So now let me modify this. I'll go through gender and ritual to be jewel in here. Select the background as something. This is Florida. Little lighter like this. Okay. And then title Sales and he had a tobacco. Tried to make it clear. This one. So you can color whatever you want. And you can just like this, okay? I hope you all know how to do financial analysis 30. Cards to present Total Sales: Hello and welcome back. So till now we have used matrix, so matrix to represent our numbers, right? So here we have represented all the numbers we've been Matrix then crush, operate, Operating Profit, How Profit Before Interest and Tax and Net Profit, All these things we have calculated and so on through Matrix. So we have used the matrix form. So next thing I want to, huge cards. Cards is also very important option available in Microsoft Power BI to represent numbers. That's what we're going to do in this lecture. But before proceeding, let me add into dashboards so that it looks neat. Things down here. Gross Profit, everything that we created, the space here to, to do other things. So here I want to add the card. So when you come to the age will options for your license opposite here you can see why this opposite thing though. You can see the scarred and uncaught. You can see 12310, which means it is primarily, mainly design and develop products representing numbers, passphrase, so in 123, okay, so there is one more difference. You can see you when I have to end up cards. Here in the fields option, you can see only one field is there to be added right there or not rows and columns and values. When we, when you click on the metrics, you can see Rows, Columns, values. These three options are there to be added, but when you select cards you have only one added at that Field. So bi or number field, because the guard cells designed to represent number. Okay, so here for number amount. So I'll just go to the general ledger table and select amount and or to tear. So as soon as I put amount here you can see we're getting to 5 million as some of the longer it is, sum of all these numbers, okay? So this is of no use. But I wonder Total Sales Revenue here to be displayed. So for debt, what I can do, I can go to the Charts up account and in charge sulfur Columns, we have the subclass here. Inside the subclass, if you go and look at the table charged up account, you can see here in the subclass, we have the sales define the data. Inside the subclass, we have the sales, right? So we need to go to the Charts up account. We need to put this up class, the subclass, and that's a plus into the filter. And inside the filter we need to select one less. So C and now we're getting some aka Mount Athos Total. If you sum this up, Total Sales for 2018, 1920 will get that number. Let me use the calculator. So you so 3575428 plus 5697845 plus 783569. Okay. Okay. Something wrong at that. Okay. Let's calibrate it. 3575428 plus 5697845 plus 783 by 369c, we aren't getting the same among 17 million. Okay. So this is that Total Sales Revenue for all these years. 2018, 19.20. Now, this will always come in the summer call this three years. We cannot also for a particular year. Okay. So let me do some modification over here. We need to go to the plummet. And here we go to the activity label. I want to remove this some Up amount, so I'll click on here and that will go away. Next thing is color to blue. I want to, okay? I want to solve this convergence. And then I want to put select none. Okay? So now it's common. Good. Next thing I want to add titles. Click the title on and I'll put the title. Since I Report Total Sales Revenue and make it centrally aligned. If you want, you can add that background color to it, the same color that we're following here. So now, Okay, let me go to the visuals to get the color to yellow and use this PBIT. Now tastes good, okay? Now we have this Total Sales Revenue here. If you want to filter it by country, you can filter. And this will also send for Australia for all these three years, for Canada, Germany, UK. And if you want to enlarge by the region, you can see. Similarly, you can put up it's Slicer here for the ear. And you can calculate that for the ear as well. Okay? So that thing also you can do, that is the one assignment for you. You can put us Slicer and try to get the numbers for each year. Okay? So this is all about the cards. In the next lecture onwards will see you inside the next lecture. 31. Using KPI: Hello and welcome back. So in this lecture, we are going to learn about one more thing that is KPI. So till now, we have seen how we can use Matrix and guard. Now, we are going to learn about KPI. So if you come to the visual visualizations thing, you can see here that it's optional KPI. So just click conduct. And when you click on that, you can see the KPI think coming here. And in the, you can see here turned axis and target, there are three things to be kept. For now. Target, we will not do. This. Target means we Sales and Marketing, we'll target, right? And give the target, we can provide the trends and we can prove the value. So it will evaluate your value that support the sales. So it will evaluate your sales in a particular period or what a trend. And then it will tell you whether we have achieved the target or not. So that is quite complex thing that will do. Maybe latter are not part of this course agenda, but you should know that the target is to define the target. And then when you put the value that you have achieved, it will evaluate based on your target and the trend going in the previous years. And it will tell you what is the train and how much you have achieved. So that Visualization look very good. For now, we're not going to define the target. We're going to use the value and trend. So inside this value, what I want to put, I want to put, I want to put the amount that is Total Sales. Okay? I'll put I'm on PR and then the trend excel put the date because I want to analyze that trend over time. Over time. Now we're getting zero somehow come on by data's coming to your ductus quite feel, right. And product, we need to do a little change to get some values here. So here in the trend axis, we have kept date from the calendar table. So here, when you click on here and you need to select the date hierarchy, hierarchy. When you select data hierarchy equals to sum up Total. So this is the total amount by Year. But still we need to do some changes. And for that, we need to, this is the sum of all the values, right? We want only for the service rate, we want only for the sales. For that, we need to take the subclass from the Charts, count and put into the filter. And after that, we are doing this for salmon time. You need to select only the cells. So now you can see here we are getting the sales, right. But when you look at here, this is the total sales of only for the year 2020. This is not the Total Sales for all these three years. They saw him only for 2020. So that is what KPI does. It gives you the, the particular current period. It will give you the total sum, right? So this is what? 2020, okay. Understood. This is not the sum of all three. They say only for current figured, okay, 2020. So that's why this is maximum, okay? And you can see the line in the background, and that is the background, that is the train line, that is the trend line of the sales. So when you look at the trend line that we have created here, this is also going in the same way, right? So that is what the trend line here. This is the trend line that we have created in our previous lectures. This trend line is being shown here in the background. So if it is somewhat different, it will saw in that manner. So since our cells strand is like this only, it is showing us like this. And the colors are there. The DOJ color will be changing if you put the target here. So beta, the target attribute or colors will be some. Okay? See yet when you come here in the target level, you cannot, you can see here the colors and this tends to goal percentage. All those things will be, so when you put those things, okay. So let me change this title here. So Sales, whatever period you will select, Okay. And let me put we can put the background for the whole these things, but I don't want to do that. I want to put back on effect for title. Okay? So now this is the thing, this is the way we can use KPI. Okay? 32. Creating Charts for Sales Revenue Gross Profit and Net Profit: Hello and welcome back. In this lecture, we're going to learn about adding some visuals to our dashboard. So till now we have added the numerical values, numbers, creating Matrix, card and KPI. Now it's time to move up and add some which lays cells. So we have already created for rigid essence in our, our first few lectures. Now, I want to add doors in little more detail on this dashboard. Okay, So first thing, let me just rearrange the dashboard so that we look at this space to add those things. So here we need to just reduce the sales. Sorry, let me make a cell phone records. 11. Mileage also, learn. Everything will fit tangible, kept some space. Title and I'll also make it lower level. Will make it for titles. Subtitles. Okay, that's enough for you. So much space. So I'll just yet in this on here on heroin. So just aren't as your reason is pretty unlikely. And this also this size, we can reduce This also we can make it yeah. Everything should be aligned to each other. Okay. Then our dashboard and reports for local. Okay, so now we got to move further. Okay, So next thing, what I want to add, I want to add a line chart here, so I'll select the line chart for since layout Revenue deck we have already done. We can copy, we can copy. Are these from there, but just to revise, add again and alkylate. So next thing what we have done, we have among them on from the G, L and L contact into the values. So the output amount into the Y-axis. And then we start date and date from the calendar dating to the x-axis And then we will apply the filter. Filter will take that subclass and we'll apply this. And yes subclass will take the Cincinnati. And from here. So now this is the line chart we got here right on it, right? This one, if you, so the same line chart we are getting the right. So now we have identity it also now the C swing, all the sales Revenue is moving. So next thing is we need to tender title and outs. So for that will go to the format features and gender logo here and change the title. Portrait cells. Revenue. And I'll align it in center and retain the background has put our thing. Okay, so now we have those Sales Revenue Chart ready. Now I want to remove this sum up a mountain here also. So let me do that also. It also will go to follow, which will land. You got to X axis. And we'll move the title and then report to the y-axis and we read the title. So now it is critical. Now, next thing it now we have the sales revenue. Now I want the gross profit also. What I'll do, I'll just copy this line chart and paste it here. And then alkane dieting first. Let me change the heading title first and I'll make a gross profit. Okay? Now we have the gross profit yet. Now, I need to change. So what I want to, the first thing is I need to remove this class sales because we want the gross properties. I'll remove this filter from you. And then the next thing to bring the class from the Chart of Account. So I'll bring the class from the Chart of Account and here and select the trading account. Okay? These, these too sensitive or anyone Gross Profit throwing the same kind of line chart, but the values are different because the trend is same. That is looking like the same thing, but the real losers different, okay? You proceed across Profit per quarter for this coming like this. Okay? So Gross Profit We have created. Next thing is what I want to do. I want to add dad, did. I want to bring the date here and then save the date. I want to put the date on the legends to see what is happening. And then I'll change this to date hierarchy. And I want to remove The year from. Now. You can see now we are getting the three different lines for the class Profit, okay, for Total and 18 for Total, 19 into ten continental done differently. So what do we have done differently? We have put the date on the legend, and from this date on the x-axis, we have it in water here. And now we have the three different lines port three different tiers. Next thing is I want to bring the Net Profit. So what I'll do, copy the sensitive ulnar Chart and I'll paste it here, and then I'll keep it here. And then first thing, I want to change the title card that make create confusion. So here I'll put Net Profit. Okay, Sorry, Net Profit. Now we have the Net Profit here. So now what I want to do here, I'll remove the class. So subclass from here. And I'll bring what I need to when I need to bring the for report from Report. And then in the report I need to select the Profit and Loss. So now this is our Profit and Loss. And then what I'll do, I'll put data on the legends. So I would think that date and put on the legends and then the data that key, and then moved it from here. So now you can see here now we're getting though Net Profit also. Want to look at the charge closely. You can click on the air focus mode and it will show you the chart and we got mod Net Profit. Tad's very strange thing it is some months is going down and then it is right up. So this way you can enlight the Net Profit whichever you want. You can take a look at them at the Chart enough focus model for Sales Revenue, everything okay. Matrix or the focus mode. And then you will get back to Report option here. So here you can take a closer look. This way we can analyze. We have created this thing and then we have created the KPI guards metrics. So in numbers and then we'll clutter this Sales Revenue, Gross Profit, and Net Profit Charts also. And this slicers will work on this also. We're going to select, you can select anything and you can select anything and you're going to analyze for that particular region and country. So high hope you got to know how to add. And we'll see more of the Charts and Graphs coming lectures 33. Analysing by Year: Hello and welcome back. In this lecture we are going to add one thing that I forgot. I want to see these t-star based on the ear. So I want to put Slicer to yet where I can select the yield. And for that particular yet, we can see that it is. So let's do that quickly. What I'll do will just copy this slicers here and paste it here. Untick this. And I'll go here and student body region. The region from here. And I'll select yeah, from the calendar table and put it here. So now you can see, and now we have the filter here are the Slicer here, which when we select a particular year, we can see the detail for that particular year. So this way we can analyze this pregnancy later by using the filter. So if you put control and select, you can select all three. To select 19.20. You can do that at 09:20. You can do it at all three, or you can select a particular one. This way we can add Slicer ear in the next lecture. Not didn't know what we have done, we haven't later, they, Dan Webb greater the salts on the Charts here. Now I want to look at this tells Revenue, Gross Profit and Net Profit in more detail. I want to analyze bi, Specific countries. I want to see how particular countries performing so that we will do in the next lecture. So we'll create another, another dashboard. So now our Profit and Loss tasks for each created here. So this was the ampulla created two. So you are introduction now, we can delete this. Okay, I'll let it not a problem. So the next lecture, we'll create another page and try to analyze these fancy later for specific countries. Okay, so students have the next lecture 34. Country Specific Analysis of Revenue Gross and Net Profit: Hello and welcome back. So till now we have created the sales revenue and we have analyze that through the cell scenario of what specific yield. And then we have seen the revenue, gross profit, net profit, or personal property and PBIT, all those things we have analyzed and we have created the radius things like Matrix cards. We have, we have the Slicer. So all those things and we have created the line charts as well for better Analysis. Now, we will move little further and we will create another desk work. So this is the dashboard, which is very specific Total Sales Revenue desktop we can analyze through Year squatters and monthly. Now in year we can analyze by looking at the country. So entire thing is changing and that is not giving us a very clear picture. So what I want to do, I want to create a separate dashboard where I will look at the center, a new Gross Profit and Net Profit very closely to understand how Sales Revenue, Gross Profit, and Net Profit, how the company is performing for a particular country. So that will be very specific to the countries. So what that, I will create a separate dashboard. And for that, I'll create a new page. So we have created two dashboards here. One is the basic one, and then we have created the Profit and Loss. Okay? Next thing is that's part where we will be very specific to the countries. So far this I'll give the name country Specific. So here we will be analyzing the country Specific data. And for this, I don't want to create the charts again and again. I'll the basic formatting we need to do, right, that well-nourished our time. Here. From here, I'm going to use the sales Revenue, Gross Profit, and Net Profit Charts from here. I'll just copy this and based in the new dashboards. So now our Reports are here. Next thing is I want, what I want to do. I want to analyze these things very specific to the country. So now I'll just show me. See here now we can see the sales revenue over the years. 2000 1919 to 20. Okay, if we are down further, we can how it is going create. But I won't have any specific country and I want to see how this performance. So to do that, what I'll do here, instead of the legends, we have not kept anything for the legend. So I'll select this chart, Sales Revenue, go to the territory and select country and put that country or the country into the ligand. So as soon as I put the country into the legend, now see the sales revenue has changed and notice sewing the details for country Specific. So now you can see country, Australia, Canada, France, Germany, and New Zealand you can USE all are being Sonya, which are available in our data. And they're showing with a different color. So now with this, we can analyze that us, this yellow-colored US is preparing a really good. And we can see that for all countries, this here, there is a tip. Here is that if for every country and then here, and then they're going up. But if you go into focus mode, and if you look at the sensitive and new Chart more closely, you will find that all countries are going down in this tutorial, 19 quarter one. Instead, one country that is not going to add down in that low period also. And that is this country which is New Zealand, the pink one. So New Zealand has not gone that much town. It was like low. And then it was going up, up, up and up. And then he had every country is coming lo and then they're going up. So if you take a look here, so in the Total 19 quarter one, you will end was not that bad. It was not having that deep like USA. Even though This is for Canada. Yes. Okay. So you tried performing really well in that low period also. So this is the analysis that we can do when we create a chart like this. I'm going off like this very country specific. So if you look at the heel, the New Zealand was not data, create a warming country in the starting, but slowly it picked up, picked up, picked up, picked up, picked up, and up. And at the end of 2020, it was the second most performing country as per the right. If you look at the detail. So US is eight per P5 for 73 and New Zealand it just behind that 5 million. Okay. So with this, we have analyzed that New Zealand was not performed well, but in difficult period also at Provo upon valence, it tests on the consistent increase and when the old contributor going down, it was not going data down. So this is the way we can analyze data in a cast, country Specific. And now, once we have done this, now let's go to the gross profit. And similarly we'll analyze the gross profit also Specific countries. Okay, So now here also we need to do some modification. So the first thing I'll do in the legend, we have the date so that we can see that Year specific data. Now I'll remove this ligand and country is trade-off date or the year. Now you can see here now we are seeing each line, what, each country. But this is not quite correct because here in the data, we have to move the aswell, remove the date from here now from x's, and I'll bring the date again back. Now you can see, now we have the very specific data for each country. So this is the gross profit UK's going. That trend is similar in all the countries, right? And he will say the top performing country. And then we have the New Zealand second top-performing country. But if you look at this gross profit line charts, you can see this purple color is going down and down and down and it is performing the worst. That is the UK. When we look at this gross profit, will come to know that till 19 quarter for you could watch performing. Okay, ask for the trend. But after that, it is going down and down. And to Tarjan quantity quarter three. And at the end of bi and at the end of the quarter for granted, 20, UK became the worst performing country at the gross profit data. See you, you can Started at the second position and it has gone down and ended by 20 2010 is ended by performing worse in these countries. With this, we can enlight this with the sales revenue. We came to know that New Zealand was not performing well then it picked up the sensor and yet it has performed well, right? And still in the gross revenue also, New Zealand is the second top-performing country after us. And in the Gross Profit, We came to know that UK has become the worst-performing country for giving the Profit. Profit has gone down and down after 2019. Okay, in 2020. And that could be because of the COVID COVID-19 has it the UK very badly. And that, that could be one reason for this tape, because it has become the front, the second performing country, to becoming the worst-performing country. There could be at multiple regions, but what one such region could be 2020 by the COVID-19 and Yuko as though one of the wash to practice countries. So this is the analysis we can do. Apart from this, I'm not seeing any other trend. The trend is same for all other country apart from the UK. Okay, so let's go back to the Report and now we will do the same thing for Net Profit. Net profit here, data here, it is missing. Then the date here, here. And then in the legend and remove the date and I'll put country the country from the tree Database and put your and then we will move up. Let me go to the focus mode. So now look at the focus mode for that Net Profit. So here you can see the Net Profit trend is quite strange for country, even the US, which is the best-performing country in 2022, quarter one, the net profit has gone drastically down from here to here. And that could be because of COVID-19. This is the impact of COVID-19, the profits and Net Profit gone down and then add the district since we are lifted up than the witness came back though, again, the Net Profit is going up and up and Europe and US has picked up really well and become the best-performing country has presented Profit. And then we have the New Zealand, which is very consistent. And then we have the France, and then we have the germany, then we have the UK, and then Australia. So let me show you though. This is the Australia, which is at-large. It has become the least in terms of the Net Profit. And then you can Australia, these are the two worst-performing country. Whether Net Profit, okay? So this is what we can conclude from this. The most Net Profit given countries for this car companies, US and the second one is a villain and the washed is UK, and this one is Gentleman. Yeah, I guess not. Land. Uk and Australia. You cannot really are the two worst-performing country. And Australia is the covered well, but if you look at the data, this country's going down. So this is the, this is the way we can analyze the data. So this is our countries 35. Using DAX Measure to Calculate Total Sales: Hello and welcome back. In this lecture we're going to Dax to create omega. So Dax is Data Analysis explicit. So that is very common. Commonly you will reach adding and even in Microsoft Excel's be. So let's the heat one majored in our dashboard. So let me tell you why we need why we need omega, why we need to use the Dax. See you when you look at these sales total sum this week, what? We just put the amount here in values and record the total sum that is the default feature in RPA. But what if we tastes like if it is not default or if we want to Calculate Total or something, you want to do like we got the gross profit. But if you want to do gross profit margin, then we need to divide this by the total sales. So that thing we can do useing Dax formula. Okay, so that's why we need next. Okay? Okay, so let's create our Measure. So to create a major, we need to go to the modelling, need to go to this modelling opsin. And then here you can find opsins like New Measure will see. So let's get down New Measure here. As soon as you create, click on the New Measure year you can see Measure report to the formula. So the major, we should give the name for that major. So here I'll give Total underscore. And the total sale is some of in Dax we have many formulas like some functions like sum, we have the average. We have how many other formulas you can see here? There are many formula. So I'm going to use some formula here, some function here, and the sum function apply on the amount, so you just type them on Barbie. I will tell you though, among Column from where you can take two we can take them on from the general ledger tables. Tables. So click on that and just close the bracket. So now we will get the sum of this total amount that Power BI was already doing, but that we can also do. So now, after doing that, you just click on. So as soon as you click on Kami, see the Total Sales has been added to Calendar table here, right? But we don't want the Total Sales to be part of the calendar table. We want to put it this into the right table, that is DL table, right? So here, the Home table of sunny there and there you can select the right table cell select table. And as soon as I select that, See you there. Total Sales Manager has come to the DL table. So now, once it this is done, you just close this. And now this measure is ready to use. So now if I select this matrix and I, if I drag this Total Sales to the values, let me put it here. So now you can see on major has been our table or matrix has been updated with the summer command that we're getting by default from the RBA. And here, beside that we have another column that is Total Sales that we have created through Measure, right? So both them on, both the values are coming same, right? Amount and Total Sales everywhere it is same. So this way we can use Dax to Calculate takes that is not available. Some opportunities available, but some other things. If you want to do some calculations, you want to apply some formulas for profit and loss calculation or something, then we can use the measured like this. So this is the way, this is the simplest way to tell you what is Measure and how we can use that. Okay? So now, since we have our own Total Sales and remove the sum up among from this table. Okay? So now CEO. Okay, So we got the thing with our thing. Okay, so now we're using the Total Sales for this matrix that the major that we have created trait. But this okay, so now here we are using the Sales Institute up some Up amount. So this thing we can now, you can modify for all other Charts saturated, okay? So I hope you got to know how to use Measure 36. Introduction to DAX: Hello and welcome back. In this lecture we are going to learn about the Dak, what is Dax Dax data analysis expressions that is very much used in power BI. Okay, We'll learn in detail what is Dx and then we'll do the hands on as well. Let's get started. Power VI Data Analysis Express. That means A is powerful formula language that allows you to perform calculations, create custom metrics, and build advanced calculations within your power reports and dashboards. What does it basically does? It is just formula language. If you would have used the Microsoft Excel and you ever used the sum function to create the sum of your total column values, then that is actually a Xx formula. We are pretty much using it for using it for longest time. But there are many more uses of this x expressions. And there are more formulas other than the average, mean, mean, and all those formulas are there. Apart from that, we can use Dx in a much more advanced way. This allow us to perform calculations, create custom matrices, and build advanced calculations within power reports and dashboards. In this lecture, I'll provide you with an introduction to power Da covering some fundamental concepts and functions. Data Anal is a library of functions. It's a set up library, set set function. It is a library of functions and operators that can be combined to build formulas and expressions in power I used in analysis and pivot table in Excel data models. Okay? Dax formulas are used in majors, creating majors, calculated columns, calculated tables, and row level security. Basic syntax of dx is pretty simple. Dax expression typically follows the pattern of function name and then we need to put the argument, okay? So this is the pattern of basic syntax of a, a expression, function name. And then we need to pass the argument. Here is a simple example that I already talked about, Sum function. Here we are creating a total sales. Calculating the total sales with the help of function sum is the argument from the cell table. It will pick table, it will pick the amount and it will sum up all the amount to give you the total sales. This is function name is sum and here we are passing the argument sales from sales table. It will put the amount and it will sum it up and it will give you total sales in this example, sum is a tax function that calculates the sum of values in the amount column in the sales table. Okay? Understood. This is quite easy to understand. The next comes, the data model before A is essential to have a well structured data model in power way. Because if it is not well structured and there are ambiguity in data, we will not be able to use the formula effectively, okay? Sure you have properly defined relationship between tables using keys. If you're using more than one table, the table should be joint properly. Relationship between the tables must be defined properly using the foreign key relationship, okay? Creating calculated columns is the third important thing. When you're working with the As calculated, columns are computed during data loading and become a permanent part of the data model. To create one, to create a calculated column, go to the Modeling tab and click on New Column. New Column. Here we're creating a total cost, which will be the new column in our table. That column we're creating by using this formula. And the formula is Product Unit cost into product quantity. This is how we can create custom column here that the total cost, total cost, total cost, product of the unit cost. Unit cost into quantity. This is how we create the custom calculated columns. This formula creates new column called Total Cost in productible. The next one is creating major measures are calculated on the fly. As users interact with the report, there is a difference between calculated columns and measures that we will see when we move further. For now, you should know that calculated column, we have created a total cost. Now we are creating a major, that will be the on the fly creation. This will be the total sales and this will be a function that is S. The next is understanding the context is very important. Tax calculation depends on the context in which they are evaluated. Two primary contexts are row context and filter context. Row context works on individual rows and filter context filters data based on the user selections and slicers. Okay, these are the two contexts, one is roll level and other filter context roll level will work on the roll level, will filter the data based on the user selections and slicers. Next one is data function that provides various functions for aggregation, filtering, time intelligence, and much more. Some commonly used functions include some that will add values in a column, count the number of rows in the table or column average, calculate the average of a column, calculate the average of a column, and filter filter data based on the specific specified condition. And then calculate modifies the filter context for a calculation. The time intelligence function that offers functions for working with date and time data such as dates D, year to date calculation. This D is year to date calculation. Dates D will give you the year to date calculation. Then comes the sample period last year, compares values to the same period in the previous year. Then total D will calculate the total up to the specified date. Okay, then comes the evaluation context. Understanding the evaluation context is crucial for writing complex tax formula. You can formulas like all filter or values to manipulate the context within the calculations variables. Dax allows you to declare and use variables to simplify complex expressions and improve performances here. As an example of Dax major uses variables. Here we are creating a Dax major. Average sales is to total sales. Here we are creating the total sum. Total amount. It will pick the amount column from the sales table and it will sum up the amount, the total, it will count the number of. Then it will average sales by dividing the total sales divided by total. Calculating the total sales. Here we're calculating the number of total and then we're returning the total sales divided by total D. That will be the average sale. Okay, at last it will come testing and debugging. Use Dax formula bar in power way to test your Dax expressions. Also, power way provides error message and diagnostics tools to help you debug the Dax calculation. This is the basic for A. We will do more hands on and we'll try to understand more about Dax formulas, Dax functions. And we also try to understand what is the difference between the calculated column and creating measures. Because these two will become confusion. If you not understand in detail, see you inside the next lecture. 37. Difference Between Calculated Column and Measure: Low. And welcome back. In this lecture, we are going to learn about difference between calculated column and measure in power BI. In power BI, both calculated columns and measures are used to perform calculations and create custom matrices within your data model. But they serve different purposes even though we are calculating based on the fields, based on the columns that we are already having. The calculated columns and calculated columns and major will have different purposes and have distinct characteristics as well. How we create is totally different and how we use calculated column and measures. Power way is also differentiated. Okay, here are the key differences between calculated columns and measures that we should be aware of. If you're using power way, okay, then you will be able to use the columns. You will be able to use the columns and measures right place. You can create calculated columns in a condition. You can create measures in a specific condition. Those those things we should be knowing. First we understand calculated columns, calculated columns. Here we will talk about the few things like computing timing, storage, impact, context calculation, and uses scenario. We'll see based on all these parameters how calculated columns and measures are different, okay? Computation timing. Calculated columns are computed during the data loading process. Calculated columns will be computed during the data loading, whenever data will load, or if you have created a calculated columns, it will be computed during the data loading. Computed during the data loading. And when you refresh your data model also, okay, they become permanent part of your dataset. It's like creating another column in your dataset. When you create a calculated column, it is like one permanent column is coming into your dataset. And it will remain until you delete it. Okay. It will become the permanent part of your dataset and stored in the data model or it will be stored in your data model. Storage impact, calculated columns, columns consume storage space in your data model. Which can be significant. If you have large datasets or many calculated columns or many calculated columns, there will be a huge storage impact as well when you create calculated columns, context of calculation in this context, calculation calculated columns, columns work in context. They perform calculations on a row by row basis and stored results for each row in the column. Calculated columns work in row context. They perform calculation for every row and results will be stored in every row for that column user scenario. Calculated columns are ideal for calculations that involve creating new attributes or aggregating data at the row level. It will work at the row level. For example, you might be calculated, you might use a calculated column to compute the product of two existing columns, or to categorize data based on specific conditions. Here we'll see a conditions, here we'll see an example. A calculated column might calculate the total cost of a product by multiplying the unit cost into quantity columns. This will be a calculated column where it will be calculated by multiplying the unit cost into number of product quantity. Product quantity into unit cost will give you the total cost formula. In this scenario, when you want to create the total sales. Total revenue. Total something like in this case total cost, you can use the calculated columns. Major computing computation timing measures are calculated on the fly as you just interact with your report or when you refresh your visuals. They are not stored in the data model but are calculated dynamically when needed. This is not in data set. This is not stored data set. There is no column created. It will be calculated on the fly when you interact with your report. When you refresh the visuals and they are not stored in the data model, they are calculated dynamically whenever they needed storage impact Meas do not consume storage space in your data model, which makes them more memory efficient, especially for large dataset. They will not consume the space because they are created on the fly. There is no storage needed for measures in dataset context. Calculation measures work in filter context. Measures will work in the filter context only. They respond to user selections, slicers, and filters applied individuals and reports the calculation context changes based on the user interaction. User scenario measures are suitable for calculation that involves context changes based on the user interaction. User scenario measures are suitable for calculation that involve summarizing, aggregating, or performing calculation at different aggregation levels. For example, you might use a major to calculate the total sales, average sales, or year to date sales based on user selected filters. Major might calculate the total sales by summing up the amount column in the sales table. Total sales is equal to some. Here we are using the aggregator function, and some is taking the amount column from the sales dataset. Calculated columns are pre computed during the data loading and work at the row level. While measures are calculated on the fly, on user interactions and work with the aggregated data. The choice between two depends on your specific analytical need and performance considerations. Typically, you should huge calculated columns for static level. Typically you should huge calculated columns for static row level computations and measures for dynamic aggregated calculations in your power reports. This is the basic difference between calculated columns. And calculated columns are created in the table. Like a column, they are computed for each row and they will consume the storage space, right? But measures are created on the fly. They are dynamic. They work on the filters you just select. It works on the filter level. I hope you understood what is the difference between calculated column and mejores in power. I see inside the next lecture. 38. Understanding the Business Data: Hello and welcome back. In this lecture we are going to do hands on, on the calculated column and major. So we'll see how we can differentiate and we'll see what are the scenarios where we can create a calculated column or measure in power BI. We have understood what are the basic differences between these two. Now it's time to have some hands on learning on these two. The differences in their uses and where and why we can create calculated column and measure. Let's get started. This, we are going to do a small project where we will be doing dashboard generation for a company called Right Style. It's a clothing company. They sell product categories in like Levi jeans, denim, casual wear, and semi formal. All those kind of shirt jeans and all they sell for both male and female. They deal in India and they sell in various cities like Mumbai. Now back lo, these are the locations. We have their data set. We'll try to create a dashboard and visualize their sins. Okay. That is the main task for this section. Okay? Using data and language. Okay, let's see. Look at the data. This is the data that we got from the company called Styles for Namesake. I've given the company name as Stylish, not actual data, the data that has been created for this exercise. Okay, here we have various columns like receipt number, then location, from where the customer came, the customer location, then customer name, then NID, then sale date, on which day that sale happened, then status, whether it is ordered or returned or pending. All those will be there in this column. Then order type, whether they have ordered it on, on Sop, offline or online. Order through website. Okay. Like retail websites like mean Pp cut all those things are their own website, online or offline? Offline they have given names on Sop. Online is online, order. Okay. Then token number, then product name, which product customer has ordered. Then the product category, what category product belong to. Like here, there are a few categories like casual were semiformal, formal, okay, Formal. These other categories, what is the product category? Product name and then cost per unit. And selling per unit. What is the cost for a particular product? And what is the selling price then? How much quantity customer has ordered for that particular order? Quantity. And then the cost amount, then the sale amount, okay? Cost per unit. Sale per unit number. Unit cost per unit into quantity will give you the cost amount. And selling per unit in quantity, we'll give you the selling amount. Sales amount, okay? For how much amount you have sold, then the profit will be the sales amount minus, minus cost amount, okay? 1,100 -900 is equal to 200, okay? This is how this column is calculated. This is the data on this data we are going to work and then we seat that set. One is having the customer name and a few other details like on. Okay. Then we have the Product Master, where the name of the product, category of the product and cost price has been given. Then we have set two where the receipt numbers are stored. Okay, Let's go to the power and start doing the hands on. But before moving to the hands on, let's see what are the calculated columns we can create. See here, we can create a calculated column. Say, we can combine this to product name and product category and create one column called product, which will have the product name and casual were together. That could be one calculated column. In similar way, we can create a major, that will be something like a percentage profit or something like that. Okay, we'll see. Let's go to the Power BI. And first thing is we need to get the data. We'll click on the Get Data and click on the Cel workbook. Or you can directly click on the Cel workbook. Okay? Click on Get Cel workbook and we'll import the right style Excel file here. Click on Open. This data will be loaded in. Our here will select the data, click the data will be loaded in Our Per it is creating the model. All the rows has been loaded. Here you can see the data, all the columns are here. Now we have the columns from here. You can see this is the report view. This is the model view. Table view here, you can see the same table has been imported here. The model view, this is the table view, This is the report view, This is the model view. In the next lecture, we'll create a calculated column, see inside the next lecture. 39. Creating Calculated Column: Hello and welcome back. In the previous lecture, we have imported the data and we have seen what the details are there in our data file. This is the right style company detail. Here we want to analyze the data. We will also try to understand how to create customized column and major. Let's create a custom column. Let me revise you the receipt, number, location, customer name, son ID, order type, token number, product name, product category, cost per unit, selling per unit, quantity products sold, number of quantity, the cost, sales amount, and then profit. All these columns here, Okay, Now I want to create a custom column for that. What I can do, I can come to the Table Tools here. If you go on home and see the Option Table Tools here, you can find the options new measure, quick measure, new column, and new table. So these are the options given here. We need to select our data here to create a custom column, we need to click on the new column. Okay, to create a major. We'll see how we can create a new major later. For now, I'm going to create a new column. But before proceeding to create a new column, we need to understand our requirement. Do we really need a new column? Because, because there are already so many columns there, right? Why do we need a new column here? See here, if you look at here, there are two columns, product name and product category. What I want to do, I want to create a column where the product name and product category will be together. I want to create a single column which will be the combination of product name and product category. When we look at that column, it will tell us that this product belongs to Sem. Column immediately will come to know that T belongs to semiformal. Okay, So that is the requirement and that's why I'm going to create a new column here to create. Click on Column and see here, now we have to write the Ac. Okay. Here, first thing is Column, as you call it here, we'll like to expression. This is the column name, the new column name that we want to give here. I'll give the column name, product name, category. Okay. This will be very significant. It will contain product name and category as well. We can underscore as well if you want. Whatever naming convention you want to use, you can use. Okay? But it should be a meaningful, when we see this, we will come to not product name and category. This column will represent what I want to do here. I want to create a new column, product name underscore category, which will be combination of product name and product category. For that I, I'll use product name. I'll take the product name from our data, product name. And then what I want to do, I want to concateinate the product category with it in between I want to put the Das. Okay, I'll put Das and I'll use the Ambercent again. And then I'll select the second column, which I want to concateinate here. That is product category. I'll put product category. Now what I'm doing, I'm creating a new, that is product name, underscore category, which will have the product name. Then at product category it will be like formal. Now we are done with this. We can click on here, this is the cancel and this is the commit option. This is the expression that we have written. You can say a formula or a expression, whatever you want to say. This is how we write a query. Now we are concateinating product name with product category, and we as in between. Okay. Now click on. As soon as you click on, come here. The new column, product name, underscore category, has been added here. This is, it will added for every row. Right? That's why when we were learning about theory about the custom column. Custom column will be created for each row. It will created the level new column will be created for every row. Every row will have the values set for. Same for. In this way if you have some other product, we can verify a Genes, Denim casual, Genesis casual were. This is how we can create a custom column. I hope you understood how to create a custom column. Now we'll see how we can proceed with a custom major. In the next lecture, we will try to create a new major inside new lecture. 40. Creating Measure and Understanding Differences: Hello and welcome back. In the previous lecture, we have created a calculated column. Here we have created a calculated column and we have given it a name, product name, underscore category. So it was a combination of product name and category. We have used product name and we have concateinated product category with that. And created a new column called product name underscore category, which will be the combination product name and category. By seeing this a single entry, we can understand that, that belong to the same formal product category. Okay, now we will try to create a major. Before we proceed, let's understand in a better way, let's deepen our understanding of calculated column major. Before we proceed, let's understand measure in power in greater detail. A measure is defined as a calculation based on column values in a table or across multiple tables. It can perform arithmetic calculations. Or aggregate functions like summing, counting, averaging are calculating the minimum or maximum values. When you want to perform some mathematical calculations, then we use the measure. These measures are defined using data analysis expressions language, which is similar to the Excel formula language, making it easy for Cel user to understand. We always use the sum and average in our Cel set. The same same thing we'll be using here. Also, measure can be created for use in reports and added to values field in visualization. When we create calculated column like here, it will create a column and it will be added to our model. Whenever we load our data, it will be loaded and it will consume memory. And it will slow down the process because it will consume memory, text, the process to create it every time we load our data. Okay, but that's not the case with major, major is created. There won't be any column created in our dataset or model, but it will be just like a field. We can use that into calculations in reports. We can put Mejor into the value field while creating reports. Whenever you use the reps and will be done and equal the result. That is the advantage of using major while calculated column. You will understand majors can also be used to create calculated columns, which are columns that are created based on formula expression. Unlike majors, calculated columns are computed during data loading. Calculated columns are computed data loading and are stored in the data model. Like I said, this calculated column is now part of the data model. It will consume memory. This means that they can be used in any visualization report without the need to recalculate the formula each time. What will happen in case of calculated column? It is calculated once stored in the model. We'll need not to create it or calculate it again and again, right? But in case of major, it will be calculated again and again. Whenever we use that but calculated column will take memory, it will consume memory. It will slow down the process, but major will not do that. It will be calculated whenever it needs, right? It will not consume any memory. However, calculated columns can increase the size of data model because a new column is being added here, right? Increase the size of the data model and slow down the performance if not used carefully. If you add multiple calculated columns, suppose in your data model, then it will unnecessarily consume the memory. It will slow down the process, and ultimately, it will reduce the performance of your report or task or whatever system you're using. We have to use the calculated column very carefully when and whatever needed only. We need to do that. It is important to understand the differences between the majors and calculated columns and choose the appropriate one for your needs. It is very much required to understand the difference. Okay, now we have understood the difference between the major and calculated column. Let's create a, Let's create major. We'll select our data. We'll go to the Table Tools on here, menu file. Okay. Then we have the Home Help and here, Table Tools. In Table Tools, we have the new major option. We have the new column, also here with the new column. We have created this. Now we'll use the new major. Use the new major to create a major. Let's click on that. See here, New Measure. Here, what I want to calculate. I want to calculate total sales, okay? I'll give the major name measure and here I'll use the sum function. Here I'll amount for sales amount. I'll use the sum function to give the total of the sales amount. It will sum up everything, and it will give us the Regula Sales. Let's commit. This sum function will go to each and every row. It will sum up all the sales amount and it will give us the total sales. Let's run this, see here. Now, the total sales a major had treat symbol is like a calculator symbol. But in the name underscore category, that is calculated column, there is a function symbol, right? That is the difference. See here, the product category, that is the calculated column. There is a column created here. But for total sales, there is no column in our data model. See here, There is nothing called major here. Total sales. Total sales has not been created. It will not congee any memory. It will not be added to our data model when you go to the model. Also here also, you will not see any one thing is while creating reports in the model view, report view, we will be using the major, we have created this total cells measure, but there won't be any extra column added. Okay, that is the difference between the calculated column and major. In the next lecture, we'll these two into the report view. Try to create some visualization, some report and try to understand the difference between calculated column and major in more detail. See inside the next lecture. 41. Using Calculated Field and Measure in Reports: Welcome back. In this lecture, we are going to use the calculated column and measure to build some reports. Let me just go back quickly and tell you that we have created a calculated column here, that is product name, underscore category. We have created a major here that is called Total Sales. Okay, To get you how to create, we need to go to the Table tools. And here you can find the New Major option, where you can click and create a new major. And there is another option of quick measure that you can choose a list of common calculations and add the result to the selected table set if you want to add quickly. If you click on here, you can see you can find the calculation, average per category, variance per category, maximum minimum per category, weighted average per category, filtered value. All those things are the year on year, year to date, total quarter to date, total month to date, total year over year change, quarter over quarter change, month over month change, rolling change. And then we have the total category running, total total for category filter applied and total for category filter applied with the filter. And this will be without a filter. Then we have the mathematical operations, addition, subjects and multiplication, division, percentage, difference, correlation coefficient. These are the very quick thing. If you want to, you can just select them and it will be done for you. Okay, that is the quick major thing. Then we have this new column that we have already, it writer expression, that creates a new column in the selected table. And calculated values for each row that is a major difference between the calculated column and major will be used only for reports. It will not be added into your table or model, but in the calculated column it will be created a new column in your data model calculated. It will calculate the values for each table, each row, sorry. See a product name under that calculated column has been calculated for each row. Now what we do, we'll move to the Report view View. We will click on the blank and we can select any of the chart type. Okay, so here you can see there is option of visualizations and sulzation. We have two options, Add data to your visuals and there is a format report page. We'll see all these, okay. In the build visuals, we have various chart options like line chart, column chart, clustered bar chart, clustered column chart, 100/bar chart. Then we have the 100% column chart. Then we have line chart, the area chart, then stack area chart. All these options are there. We have the pie chart, we have the donut chart, we have the tree map, we have the map, then we have the field map, we have the Azure map, we have the gauge, we have the card here. You can use the script and Python visual, also Python code. Also, you can write all the options we have. We'll start with the simple thing because we want to see how the calculated column, okay, and major also. What I'll take, I'll take the clustered bar chart here. When you click on here, it will come here. But see here, nothing is visual here, it's a blank chart. This is done. We need to go to the data here. We need to select here, x, y axis, legend, small, multiple. Various options are there, right? We need to drag and drop the column which we want to use here, y, x. I'll use the product name underscore category. For each category I want to know the quantity, I put on Xs, the quantity. See here now we have the product category and how much total quantity it has been sold. Now you can see here Semi formal has many quantity gain. Levi casual were has this much. Okay. Now all these things that chart name and all we can modify that will later we can come here into the format visual and we can do that. Okay, Now we are concentrating more on creating the reports. Now we have used this calculated column right now if you want to change the chart, you can just click on here. Here, now it is clustered column chart. If you want to put like this, whatever chart you want to select, you can select and it will be done accordingly. You can select the line chart, you can select the area chart, you can select all those things. You can select the pie chart. And here for each category, product name, underscore category, it is swing quantity in percentage. Also, you can put donut chart. And this will be done for you is a simple how we in a normal condition the same thing, but we can create our own column with the calculated column. Now what I want to do, I want to select another report here, another chart. And I want to use major here. To use major, I need to select the major here and I need to put it to the Xs. Then I want to take product category, okay, the calculated column here. I'm using the calculated column and major together, see here. Now, for each calculated column, product name under Scott category, we are getting the total sales. If I convert this into getting here, for each product name underst category column, we are getting the total quantity. And here we are getting, with the help of major, we are getting the total sales, okay? So if we convert into the pie chart, can see here, here we are getting the total quantity and here we are getting the total sales, See here. Okay. So this is how we can use both of these into the reports. We'll try to do more hands on in the coming lectures. See inside the next lecture. 42. Importance of Measure: Hello and welcome back. We have understood how to create a calculated column and a major. If you go to the table view here, we have created a calculated column here. And then we have created the total cells as a total cells as a major. Right, now we understood the difference between the calculated column. It will be created inside the database. Okay. It will consume memory, right? It will slow down the process. If you so many calculated columns, it will be added as a column in your database, but the major will not be added into your database as a column, but it will be just sales amount. It is basically the total of the sales amount. This is the formula we are using, but this is the difference between the calculated column and major. I'll show you another thing. I'll just copy this copy, this schedule, copy, and past this visual. Now I have tablicated this now instead of total sales, the major which we are using here for this visual, I'll use the Total, The major here. I'll remove the total sales and I'll add this sales amount that is already there in our table. Sales amount is a column inoitablemot is a column initablee. There is no difference between these two. We're getting the same thing, right? This is total sales and this is sum up sales amount. But everything, every data is same, there is no difference, right? Then why we have created this total sales measure? That the question you may be asked in the interview as well, right? The sum, total sales amount is giving us the same result we have created that sales amount on, right? Why to create a new measure? That is a very good question, right? To answer that. See if I change this to bar charts. See here everything is same. The total amount is 1 million something. This is also same. Everything is coming same. Why you need to create a measure? This is very important and important question in job interviews as well. To answer this, we have to understand why we need measure. There is another utility of measure. Here we are directly using the reference to our model, our database here. Sales Amount, right? In future, you are this report. Suppose you have the multiple pages and multiple dashboard you have created. Suppose ten print reports you have created. In those reports, you are directly referring to the sales amount, this column, okay? Suppose in future this column name, somebody changes to some total sales amount column name has been changed, right? But in your reports, you are using this sales amount. It will always refer to the amount column name. And it will not find error, right, because the column name has been changed. So in that case, what do you need to do? You need to go to every individual report and you have to remove this and you have to add the new column. Okay? Then your report will show properly, right? But if you're here we are using total sales. Which major? If you're reaching a major. Now you'll say that in total sales also. In major also, we are referring to the sales amount only and sales amount has been changed. This will show error, right? Absolutely. This will also show error wherever you are a major total sales, that report also will show the error. The report the dashboard will be showing error only. But in case of your directly deferring to the column name, you need to go to every report and change it manually. But if you're using the total sales here as a major, you just need to change in the formula where you have created the major here, we need to change the sales amount to the sales amount to the new column. You need to change the name here. And that's it in all the reports, it will automatically change because we are using the formula from here, the tax formula. We will change the column name and it will be done only one place you need to change and all other reports will automatically get updated. That is the one difference between calculated why we create in our power BI reports, that is the main difference, okay? The major will give you advantage. If something is getting changes change, you need to just change into the formula and all other reports will automatically get updated. In case of directly referring to the sales amount column, you need to go manually and change everywhere. That is the very good utility of measure. Even though the column name is changing, you just need to change, change the formula of measure in the expression. And that's it done. That is the main advantage of using the x. If somebody asking you in an interview why we need to create mejor and putting you in such a situation from the table and the same column, you're using the major also, there is no difference then why you're creating major. Then you can give this reference, you can say this thing and you can answer. I hope you understood the utility of major and how it is getting utilized in our reports and how it can be beneficial, directly referring to the column name. I hope you understood inside the next lecture. 43. Profit Calculations using DAX: Hello and welcome back. In the previous lecture, we have seen the difference between the calculated column and the measure. We have created the calculated column, that is a product name. Let me show you this calculated column we have created and we have also created a total, is a major, right. We have seen how we can use them into the report. We also understood that the difference between the product calculated column and major right, calculated column, will be created in the table and it will conjue memory and it will be created for each row. Whereas major will be just for calculation we use in the report. Then it will be used, this report where we have the major and calculated column, both and we have seen the difference. Okay, now let me take you through the Excel seat. This is vocal seat here. I'll show you the how we can have a calculated column and major. First thing we have the cost price. Here, let me put sale price. Okay, Cost price and sale price. Suppose I'll give 12085850. This should be 850. Then we have 900. Suppose 900, then 850. The 1,200 put 12,001,150,900.700 Now we have another column, that is self price and cost price. Now if I want to calculate a profit here, I can create profit here. I'll use the self price minus cost price and Agata profit for each row here. This formula we have used, self price minus cash price. This is called calculated column. This is like a calculated column because it is being created for each and every row and another column, profit we have to create, right? This is creating a new column and calculating for each and every row. This is what we have done here, right here. For each column, it will be created The similar thing we can do in Excel also. Now, what will be the? Suppose I want to total profit. Suppose I want to calculate total profit. Suppose I want to calculate total profit. For total profit, I can use a formula called sum, right? I can select all these and this will give us the total profit. Now see here, total profit is not a column here. It is just the sum of all the, right. This is like major when you want to find profit for each and every sale calculated column. And when you want to get the total profit for your business, you can go with the major. Major is like a formula. This is the tax formula, right? I hope with this it is pretty clear what is calculated column and what is, uh, major. Suppose if you want to calculate average price, average cost price. So here we can use average, sorry. See here. Now with this formula, we can use the average function and we can get the average of cost, price 775 is the average price. If we have such criteria, we can go with the major. Similarly, you can calculate the average price also here we can modify this average price. We can copy this formula here. And here we will be using the. We'll be using 211. Okay? Some error. Okay, This packet. See here, average cost price is 775, average self price is 950. Right now with this, we can also calculate average profit, average profit. We can use the same formula here. We can change the column just by selecting the profit column. Average profit is 175 page. If you have such criteria, such requirement, you can go with the major. Right? The similar thing we can do in the power as well. Let me create a new page here. First thing, we have created total sales. Now I want to create, in our table, I want to create another calculated column. That will be the profit. Okay, for that, let me click on our data here, select new column. Here I'll give profit calculated, our calculated column, okay? Here the profit will be the sum, profit will be, see amount minus cost. Amount, right? This will be the profit. Let me go to the table view and see here. Now we have a profit calculated column that is giving us the profit for each. This is similar to this one, this column that we have created in Excel, the similar thing we have created for our dataset here. And this is giving us the profit for each items sold. Now we can create a measure. Also, let me select this data, and here we need to create the new measure. Here I'll put profit, a total profit. The profit will be the sum, total sell amount, right? Sale amount minus sum of cost amount, right? So total sale amount. Total cost amount will give us the profit. Total profit, okay? When it too close this year, now we have the total profit here. But when you look at the table, we have only the profit column here. But total profit that we have created. That is not visual here. Now we can go and create a chart here. In this I, I want to analyze the profit for support and total profit. See here. Now we are getting the total profit per category. Casual wear is giving this much profit for semi formal accessories. Now here, this total profit is a calculated column, right? Let me copy this sule. Now here I'll change this to total profit two profit per column here. Now we are getting the similar data. This way we can use the major and calculated column. We'll see more hands on in the next lecture. 44. Comparing Previous Year Sales with This Year using DAX: Hello, welcome back. In this lecture, we are going to visualize the sales for this year and the previous year. We are actually going to analyze. Suppose we have the sales, we have the sales for multiple years also, right? I want to analyze for this business, what is the growth or what is the sales pattern going on for each month of this year and the previous year? This could be done only with the major. We cannot do that with the calculated column. Let me start doing this. Okay, We have created this product name category, calculated column major, and the total sales and total profit as a major. Now I want to create a major. Let me select this data here. I want to create a new major. This major, I'll give name as previous. I want to compare the previous year sales with this year, okay? Previous year sales, okay, Versus this year, okay? I'm giving big name just to, uh, make you understand. You can put this, uh, in a shorter form. Sales versus this year sales, okay? This year sales, okay? You can give any name you want, okay? I want to compare, I'm giving for this, I'll use the calculate function here available with the tax. For the what I want to do, I want to get the sum for all the sales amount. I'll use the sales amount here. I'll get the sum of total sales. Okay? I'll close this packet here and here. After this, I'll use another function called Same period last year. So I'll use another function inside this calculate function. Same period last year. And what this will do, this will give us the sales amount, total sales amount for the same date on the previous year, okay? So that is the internal functionality of this function here, Sale date. I'll use the sale date. I'll use the sale date date. Okay, I'll close this. Now I have created a measure which will give us the total sale amount for a particular date and the same period in the previous year. Okay? Suppose 24th of today, 28 October 28 of October this year 2020. 3.28 of October, 2022. It will compare and will give. Okay. Now we have the major ready. Let's go to the report view. See here, there won't be anything created in this table because it's a major. Now we will visualize this. Okay, I'll use the table here. So I'll start with the table and move to the other vigulation here. I'll and in the sale date. I don't want the day, I want the month. And I want to compare the month wise for this month, last year, this month, this year, this month, and last year, this month, okay? And for this, I'll use this one. Previous year, sales, okay? And I'll also use the sale amount here for this year. Let me put this, see here. Now we are getting the table which is giving us the value for 2019 and for 2020 as well. See here. For 2020, January, this is the sale amount for 2019, this was the sale amount for January, this is for February and this is for February last year and this is for 2019 2018 data. We are not available it's not available in our this thing. That's why it's not only 2,029.22 2020 data we have. That's why we are not getting the previous year data for this row, right? But for 2020 we're getting the 2029 data. Okay. Now, let me make this as a clustered column and I'll just go to the next hierarchy level and see here now we are getting pretty good comparison of this year and last year. See here. This is the lighter blue is the sum up sales amount for this year and dark blues for the last year. See here. Now with the, any business can get to know how sales going. The sales in January 2019, it was this much and now it has increased. It is increasing. And see here now in June, it almost July, August, almost after that it's remaining same only. But here it is Inca. This we pretty much analyze the data. This is the utility of major, we can compare the sales for this month and this month of the previous year. This is pretty cool if you want to analyze for your company, your client's data, for giving them the insights, how is the sale going on compared to the last year. This is a great utility of major. In this, we have created a simple major, that is previous year sales this year we have given it a name. We have used the calculate calculate function. Here we have used the sum of sale amount that will give us the total sales and the same period last year. A function we'll use to get the data for the previous year. Here we will pass the sale date. This way we can use the major to create this visualization and getting the insights from the data. I hope you understood how to use the calculated column and major in your power BI reports inside the next lecture. 45. Contexts in POWER BI: Hello and welcome back. In the previous lectures, we have understood about how to create calculated column and how to create measures. And we have created few visualizations as well. We have seen the applications of measure and calculated column through visualizations as well. We have come to know that when we create a calculated column like profit column or product name, underscore category, it is being applied at the level when we are creating major, not getting applied to the role label or it is not getting created into the table. Why is it so why we are using the major major is getting through the report creation. Other than that we are not seeing that into the table view, right? To understand that, we need to understand one more concept that is called text in power, BI. Let me take you through this beautiful concept called context in power. This is very important to understand because when you understand this, you will be able to use the tax data analysis, expressions, formulas, the calculated column, major, everything in a proper way. You'll be knowing that where you want to use all these things, okay? Because availability is not important. If you have lots of formula and you don't know where to apply, how to apply, when to apply, then it's of no use. For that reason, we need to understand the concept behind. Let's understand the context in power. In power BI, there are two primary types of context. The first one is context and the other one is filter context. Understanding this context is crucial for creating effective and efficient power reports. Understanding the two context context and filter context is very, very important for creating effective and efficient power report. Let's understand one by one row context. As the name suggests, it is the row it is being applied at the level. Row context refers to the context in which calculation is being performed at the individual level. And we have also seen this created this calculated column profit. It has been applied to the level, right? So let's go back here. It is applied when power evaluates calculations for each individual row in a table. When you create a calculated columns or majors in power by row, context is essential to consider. For example, if you want to perform calculations on each row. If you want to perform calculations on each row independently, you need to understand how context works, right Row context refers to the context in which calculations are being performed on the individual row level. It is very important to understand when to use calculated column and when to huge Mejor, then we can utilize the things. Okay, example of row context. Suppose you have a table that contains sales data with, with columns such as product quantity, price. Now, if you want to create a calculated column for total sales that we have created, right in each row, you need to consider the row context here. You need to understand very clearly if you want to create a calculated column for total sales, that is quantity into price, right, in each row. For each row. If you want to create, you need to consider context. The formula might look like this. Total sales is equal to sales quantity into sales. Price quantity into price will give you the total. In this case, the context is crucial for calculation because it operates at each individual row level, right? Because here, total sales you want to create for each and every row for that context is very important. And that's why the formula here, quantity for each row into sales price for a particular row, we are taking the quantity, how much that product has been sold, and how much, what is the unit price? Quantity into price will give you the total sales. In this case, context is crucial for calculation operates at the individual level. Now understand the filter context. The second one is filter filter context on the other hand, refers to the filters applied to the data or visualization in power, BI filter context, It is applied to the data or the visualizations in power. Most of the time when we create visualization, then we'll use the filter context. It determines how the data is filtered and affect the results of calculations. It determines the filter context will determine how the data is getting filtered, affect the result of calculations. Aggregations or visualization affect all these three things. Calculations, it, it will affect the aggregations and it will also affect the visualization. Understanding the filter context is very much essential for creating dynamic and interactive power reports. If you want to create a dynamic and interactive power reports, you need to understand the filter context. Because fill make the reports very dynamic, it will make the dashboard look dynamic. Because when you apply filters, you'll get more insights. Getting insights, more insights and beautiful insights. And the useful insights from the data you need to understand the filter context very well. Example of filter context, suppose you have a sales report with various visualizations such as bar chart showing sales by region. If you apply the filter to show the data for a specific region, the filter context will be applied. Suppose you have a sales data for every state in India, and you want to apply the filter to look at the data for a particular state. Suppose Punjab here, the filter context will come into the picture and you need to understand the filter context very well. If you apply a filter to show data for a specific region, the filter context will be applied. The visualizations will display data only for that particular region. When you apply a filter data only for Punjab, if you select the region, Panjabam, it will show you the result. For the Assam, The filter contexts affect the data displayed individuals, any calculation performed based on that filter. Suppose you are looking at the sales data for entire country and when you apply the filter context for support Nata, if you select the Taska, then it will show you the visualizations for specific to Nata, the sales amount it will so for Aka, it will show you the profits for Natica. All the visualization calculations, segregation, everything will be limited to region Natica. Because we have applied the filter Ajanatica, the filter context affect the data displayed individuals, All the charts will get changed and all the calculation performed based on the filter that will also get change. It is crucial to differentiate between the row context and filter context to ensure that accurate calculations and visualizations in power happens accurately. Okay, to fully grasp the cont, concept of row and filter context, it is important to practice and experiment the different scenarios in power. When you work more on power, the data. When you do lots of hands on, you'll create lots of visualizations. You'll apply filters. You'll create calculated columns and measures. When you create lots of visuals, reports, charts and dashboards, you will come to know and you'll be understanding in a much better way. I hope this theory part has given you the differ understanding of what we have understood through this visualizations in the previous lectures. Now since we have done the hands on also and we have understood the theory part also, now we are going to move with much better confidence for the further exercises. That's it for this lecture. 46. Dashboard for Total Sales: Hello and welcome back. In the previous lecture, we have understood about the context in power, understood about context, and filter context. Now we will try to use that concept and create some visuals for our project. Okay, here I'm going to create a dashboard now, which will be including what type of visuals we have created in past, in the past few lectures, we'll try to create a dashboard where we can see the total sales for this right style. Okay, Our agenda is to get the total sales and apply some filters so that we can quickly analyze how this business is working, how this business is going as per the total sales. We'll try to analyze by category. We will try to get the total sales by location. And we will try to get the sales by the year as well as well. Let's start for that, we need to look at the data here. Data, we have the 2,019.20 20 data we have. And we have the product category, cost per unit, sale per unit, total sales. We have, we have created a total sales here, right? The total formula we have used is sum sales amount. Total sale amount is give us the total sales, right? Some of the total sale amount will give us the total sales, right? That measure we have created. Now we need to get the total sales data and create a dashboard and we will apply some filter context on that. The first thing before I create this dashboard here and few reports will be there in the dashboard, I want to put things. I want to get the total sales for entire business working years, all year. Sum total sales till date including all the years, The business for walking for that. Whenever you want some numeric values, we can go with the cards here. There are various options right here. We have the card option, create a card, visual click on card. Now when you click card, you'll get this blank here. I want to see what is the total sales till date. For that, simply click on Card from here and then go to the data fields and select the total sales. Select the total, Eli. As soon as you select you'll get the total sales. It is coming as a 5 million. Okay. Now we have the total sales card ready which is giving us the idea, which is giving us the figure of total sales totals 5 million. Now with the card option, you can get the concrete information, numerical information, which will quickly give you the total up support you want to enlig total profit. So you can create another card here in that you can select the total profit. See here, total sales is 5 million and out of 5 million, we have the total profit of 1 million. Total profit measure also we have created that is total sale amount minus amount that will co profit. Total profit. Now we have the sales and total profit here. If you want to edit this total profit so that it looks good, we can go to the Mat visual and we can go to the General. We can go to the title. Okay? Okay, let me see. You can come here and you can put the total profit on top. And you can select the heading as two. And you can make it bold. And you can align it in a center, Okay? If you want to put background, you can put here, put it from here. Total Profit, okay? Subtitle, if you want to put, you can put from here. Okay, we can remove this total profit category level. We can remove, okay, from here you can remove this Total Profit, this category level. And from here you can put the title. Okay, now that is the thing for the total sales. Also we will do the same thing to the general. We'll switch on the title, and here we put the total sales here. We will make it bold, and the background color will take the similar one. Okay? And then we'll align it in a center. That's it. Now see here, our report will look a little better, right? Okay. So now we have the total sales and total profit, these two cards created. Next thing is I want to analyze the total sales by product category. For that, I'll select the clustered bar chart here. I want to analyze the total sales by product category. So I'll select the product category, we have the product category here, and then I'll select the total sales. Now we have the total sales by product category right here. Also, you can customize your options by going to the format visual and go to general. Okay, that is good. And we can central aligned it and we can give the background color same. We can make it, if you want to increase the front size, you can do that. Okay, now it's good sales product category we have, okay, now we have the total sales product category. I'll make it like this. Okay. Now see our task board. We have the total sales product category as well. Here, right? Casual wear, semi formal, formal. And for everything we have the total sales. Make it like this, so that it will look a little aligned. Okay, Now, next thing I want to analyze the total sales by location. For location, I want to apply some filters. And to get the filter thing in VI. In reports we have the very important toll that is slicer. But before slicer, I want to add a matrix here. With the matrix, I want to analyze the total sales with the sale date, date. And also I want to apply the location. From here, I'll remove months letter, put the location into the row, and sell into the column. And now we have the matrix with location wise total sales for every year for Bangalore 2019, this Must Sales 2020. This must Sales and Total Right now, this is another thing we have got ready. If you want to customize this, you can go to the Format. Vitual can go to General, you can switch on the title, and here you can put the Total Sales By and okay, And you can make it bold. You can select the background, same as above, and Central. All now this thing is reading. Okay. Next thing I wanted to add a slicer, if you want to pick it like this. Okay, Next, go to the Slicer, Where is it? Somewhere here. You'll find, this is the slicer. Okay, I want the slicer for location. Location here. See here. Now we have a location filter thing. Right? I'll add here another slicer. I want another slicer. I want for order type. I'll select the order type here. Now we have the two slices here, another one here. Now, now we have added the total sales and we have added these slicers. Right? If I select Bangalore here, see the dashboard. Whole dashboard will change. And it will show the data related to Bangalore only. Total sales for Bangalore is 2 million and total profit is 348 K for each product category for Bangalore location. The total sales are here for casual wear, semi formal, formal and accessories. Here also you can see the same thing for Bangalore 2019 dismal Sales, 2020 dismal Sales. And totally, if you want to put luck now, it will come like this, okay? Accordingly, it will get changed if you select the order type for order on orders online it, when you apply this, you'll come to know that most of the sales are through offline store sales. For online it is pretty low compared to the on this. We can come to know that for this business, offline sales are much more than the online sales for that region. We can concentrate more on encouraging the sales on online. We can select that and you can accordingly act on that if you want to encourage it, you can focus on the offline sales. If you want to increase the online sales, you can work on your digital marketing team and you can work on caging online sales with the slicers. We can analyze the business pretty well. You can see on and Mumbai, you'll get the details on Lucknow, you can get Bangalore, you can get the details. Similarly for online and Bangalore, you'll get the details online and Mumbai online and low. Similarly, if you select casual where you can see for casual ware and Bangalore, you can get the details, casual ware and lock. Now you can get the details. Similarly here when you click on this, act like a filter only. And it will give you the particular details for each product category. For particular location casual were Bangalore casual, were low, Lucknow semiformal. If you want to see the Bangalore, Mumbai, it will. So you this way we can create filters and we can work on our dashboard and we can get the insights from the data data. We'll do more hands on and create more dashboard like this for this business inside the next lecture. 47. Understanding Difference between SUM and SUMX function: Hello and welcome back. We have understood the slicers and we have seen how we can apply these filters using slicers. And even through the also, we have any total sales. Now you can see here I have another card. This is to profit that we have created earlier. Here also, I created total profit RC. What is this total profit RC? Actually, this is total profit. I have given a wrong name here. It should be Profit Profit RC. Okay, let me change this now. It is changed why? I have created another major here, actually total profit RC. For this, we need to understand this thing, context that we have understood in the last, last lecture. There are two contexts level and the filter level here. When you look at the total profit that we have created, this is a major that we have created. We have created using some function. What it is doing, it is taking the, what does it do? It is taking the sale amount. It is calculating the sale amount and then subtracting the cost amount and then summing it up. But when you look at the total profit RC measure that I have created just now, I'm using here, I'm giving the table name from where I want to get the data. The data as sales amount minus cost amount. When you look at the difference, when you look at the difference, not giving this, this data right here directly. Some of difference of this two right here. When I'm using some x function, I'm providing this table reference as well. How this and some are different section what it will do. It will calculate the profit for each row. It will rate through the table. It will calculate the profit for each row wise and then it will aggregate. Whereas the total profit here, using some, it will just calculate the total sales amount. What it will do, it will calculate the sum, total sum of total sale amount for entire column and then sum of the column cost amount. And then it will subtract and give you the total profit. It will sum of this column and sum up this column. It will give the difference between sale amount minus cost amount. The RC context, okay? Here X function. What it will do, it will it through the table. It, it will calculate the profit. It will calculate this thing. Sales amount minus cost amount for each and every row. Like here, you can see here cost amount, sales amount, right? It will, for each row level, okay? It will, it will sum all those things, okay? When you come here for the, it will ititrate through the table. It will calculate for each row and itrate and then sum it up. Okay, let me give you a better idea. We'll go to the microsp community, and here we have the difference here. Power A comprises two basic calculation engine, aggregator engine, and iterator. Engine sum function belongs to the aggregator engine. A sum function belongs to aggregator engine. It adds all the values in a single column to return the result. Some considers a single column as a whole and returns a result. Some and other aggregated functions are capable, not capable of performing row wise calculations. What it will do, sum from column. It will take the column and sum all the values and give you the result. It will not work on a level, right? When you look at total profit here, see what it is. Some function is applying on the sales amount. It is taking the sum of all the sales amount, It is also taking the sum of. All the cost amount column. Then for total profit, we are subtracting from this to this and getting the profit. It is not working on a row level because for a business, we get the profit right for each item, we'll get the profit right. For that, we need to go through the wise. But this will work on the column. If you want to get the Wi profit, then some function will not work. Because it works on the column, it is not capable of performing evaluations, okay? It takes the column Asa input, right? Sum should be used whenever, it is just a simple calculation across single column and row. Education is not required wherever wage calculation or education is not required there, we can use the sum function. Hence, if your data structure in a way that it contains only a single column of value, then you can use sum to add up to the values. The sum function operates over a single column. Hence, there is no need for an iterator is where you are simply trying to calculate the sum of a column data, right? Total units is equal to sales table units, sum of units. It will take the column and it will sum all the value and give you the result. The a sum function considers a single column of data to add all the data in that column, the sum function will add every single value in unit column of the sales table to return the total number of units. Now comes the S function. X function is an iterator function that takes a different approach. Unlike some function s, x is capable of performing row by row calculation. And iterate through each row of a specified table to complete the calculation. You understood s x is an iterator function. An iterator function, sum is the aggregator function. This is very, very important question. In interview, if you go for a power, they will simply tell the difference between the sum and s x. Why we need s x function when we have the sum function, you should be aware of that sum function is an aggregator function. And it works on the column level, right, where Sx is an iterator function. It is a totally different approach than the sum function. The sum function works on the row label, and sx function is capable of performing row by row calculations. And it it trates through each row specified table to complete the calculation. Some function then adds all the row wise results for every row. It will get the profit in our case. Then it will it through all the results and it will give you the, the result. Here you can see the syntax for function. Function is taking the table name as input and then expression, Okay? Now here if you come here and see total profit context, see function here. I'm giving the table name as data and then amount minus cost. Here I'm giving the expression that Sax function need to go to the data table then it can evaluate this expression. That is amount minus cost amount. Then what it will do, it will go to each row. It will take the sales amount and cost amount, it will get the difference. It will store somewhere. After getting all this sales amount minus cost amount for each row, it will rate through the table for this entire data and it will sum it up and give you the result, okay? Then it will give you the result first. It will do this calculation. It will evaluate this expression for each row, for the result that is getting for each row. It will give you the final result after summing it up. Okay? You can use sum function whenever there is a need for the Bro calculation. When you have column wise, you can go with the sum. When you have the Roy calculations needed, you can go with the sum function. Hence, if your data is structured in a way that you will need, you will necessarily need to multiply values from two columns, one row at a time. In order to get the get results, you simply use the power function. That is the major difference between the x function and some function. This is also very important and this is very useful in understanding the row context. Here we are getting the same propit, but in some cases the two might not be same. I hope you understood this thing. And difference between the x function and some function x function will work on the row level. It first take the data input as a table name. Then the expression will evaluate the expression for each row and then give you the result. Whereas the sum function, it will, it will get the sum, entire column, entire column for the another, and then evaluate the expression, okay? Sum function for column level and some x function for row by row calculator, definited. You can go with the s x function. I hope you understood this context and the difference between the sum function and some function. Sum function, I'll repeat again. Some function is aggregator function, whereas some function is the iterator function in power. I hope I explained in detail and you understood each practice. And when you practice more, you'll get very efficient in applying the functions. And when you work on projects and keep practicing, you'll be in a very good state of mind to apply the functions whenever they're needed. And you'll understand where to apply what function. Okay, see you inside the next lecture. 48. Working with Internal Filters: Hello and welcome back. We have understood the context right now. I'll tell you another thing here. First thing, I want to change this total profit. I've used the total profit measure, which is using the sum function. I have created function RC, sum here for this card. I'll change this to total function RC. Okay? I'll delete this. Okay? Now we have the total profit for this business. And we have created many filters as well in slicer, right? We have understood the context that we can do with some function, right? Now, I'll tell you another thing. See here, whenever we are applying any filter here, everything is getting change, right? Whatever filter, we're applying that to all the things. But what if I want to create something, I want to put something here that will not get change. When I'll change some parameter that should not get change as per the filter. How we can do that, we can use the calculate function. Let me create a major here, a new major. Click on new major and I'll create a new major as support luck now for luck now. Okay? Okay. I'll change to For Lucknow. Okay. Here I want to create this measure which will be applicable for luck. This measure will not change other filters. For that, I can calculate function. In calculate function, I can use the sum of same thing, sales amount minus sales amount. Sum up sales amount, right, will give us the sales total sales. So I'll sales amount, the sum up sales amount will give us the total sales right here. I'll apply a filter here. I'll put data here. I'll put the location location. I'll give here as understood, Understood what I'm doing here. I'm applying the filter here itself. In this measure, what this calculate function will do, it will calculate the sales amount, total sales. It will apply the filter for Lucknow. It will calculate the total sales for Lucknow only. It will not calculate the total sales for all others. Here I'm a filter. I'm telling this calculated function to get the sum for Lucknow only wherever I'll use this measure it. This is called internal filter. Whatever filter we are applying through slicer and all that is called external filter here. While creating a major, I'm giving the internal filter that you should calculate the total sales for low location commit This now we have the major Lucknow sales. Okay? To use that, I'll use a card here. This total sales for Lucknow. Now I'm getting the sales for Lucknow as 2 million apply filter for Mumbai. I'll change the location. Suppose Mumbai. All other things are getting changed, but the sales for Lucknow is not getting changed. For luck now for Bangalore, it is not getting changed. Right. Okay. It went wrong. Something went wrong here. Okay. Here we had this thing, product category. Product category, and then we had the total sales, right. This is okay. Something went wrong. So this is not total sales here. So this is not total sales here. I have created sales for luck now. I applied here. Sales for Lucknow. Okay. If I chose Bangalore, I choose luck now. Okay. So now see. But when I'm applying this location, things that is not getting changed. Right, right. But when I'm applying this order, so it is getting change for online and offline. Because while creating the sales for luck now major, I have applied the internal filter only for luck as location. Right. If you select product category, it will change because I have not given any instruction for product category, I've given instructions when I change the location as Mumbai location. If I change, it will not get changed, right? But if I change the product category, it will change. If I change the pro product category change, right? You don't want major or your card or whatever visuals you want to make. If you don't want it to get changed, you can apply the internal internal filter like this. This internal filter will restrict based on whatever filter you want to apply. Suppose you don't want to be product category, you can apply for the product category also. If you want to specify that you want to calculate this sales only for offline, you can put another filter here and you can give order type name is equal to on, so it will give you the cells happen offline cells. These filters are called internal filters. The filters which we're creating here, external filters. I hope this is pretty clear for you, right? We will see the calculate function in detail in few other lectures. We'll do some hands on. But calculate function is very much important for internal filters. Similarly, you can create a few more cards for Lucknow, Mumbai as well. And you can see it will not change. Okay. One more thing. Suppose here, if I put this where it is, sales for Lucknow. Here. Here. See. Now, let me rearrange this. Now, if you look at here, we have the total sales and sales for Lucknow. See here, whatever you will select. Let me remove this. Suppose if you're selecting this for luck, now for luck. Now, total sales sales for luck. Now it's coming same, right? Both these two columns are coming at the same, right? But when you select Mumbai, see here the sales for Lucknow is different, right? If I remove this here for Bangalore for Lucknow, this is 1043050. It is getting repeated for all other location, Bangalore, Mumbai also, because the sale for luck now is not getting affected by the location, it is always getting calculated for the Lucknow itself. Because this major we have given data as location as luck. Now, if you use a column matrix like this, this column will get repeated the same value for every location. Because whether it is Bangalore or not, this column will be calculated for Lucknow only because we have given hard coded here as low, that is the case with the internal filters. You have to be careful if you want to use table like this, you have to be careful that it will get repeated the same amount based on this filter for every other location as well. That may create problem, right? Because here we are giving the sale luck. No, but it is unnecessarily getting repeated for mumbaan, all these things. If you remove this, it will be right. You have to be careful about this. Okay, thanks for watching. 49. Comparing Total Sales for Each Location Across Product Categories: And welcome back. We have understood in the previous lecture how the internal filters works. Now we will move further and we'll try to analyze this business in much better way for this in right style for which we have this data. Now this business wants to analyze a few things. I have written down two questions like this. Business owner wants to analyze the total sales with the main branch sales across category. Actually, he wants to compare, compare the total sales with main branch sales. You want to see how its main branch is performing at the sales. And the second question we need to answer is percentage sales contribution. Each, each location. This is location. Okay? These two questions we need to answer through report. Let's we go to the BI, see here we have created this product category, Sales Total Sales by product category, right? So here what we can do, we can support Lack now is the main branch. For that, we need to create the sales. We need to calculate the total sales for the lack now and each product category, we need to compare how much it is selling for Casual were total sales is 3 million. How much casual were sales is there in low branch? That's what he wants us to do. Compare the total sales with the main branch support now is the main branch, okay. Now to do this, we need to create a measure which will give us the total sales for Lucknow location that we have done in the previous lecture. What we have done, we have created this measure called Sales for Lucknow, where we have used the calculate function. Apply the internal filter at location as luck. Now this will always give us the total sales for luck. Now since we already have this filter major with the filter as luck now, now what we can do, support know is the main location if Lucknow is not main location, some other is the main location, just copy this. We can go here and create a new major, right? That's not a problem. You can go here and you can copy this and create a new major, right? Just go here. Click on New Major. Suppose here I want to make it for Mumbai simply, you need to change it to Mumbai. Change this Mumbai. Now we have the major sales for Mumbai. Suppose Mumbai is the main location here. In this report that we have already created, we just need to simply sales for Mumbai here. Now, as soon as I put the sales for Bi here, we have the data right here. Now we can see the total sales and total sales for mobile here. When you over it, you can see the casual way, the total sales is much mobile location, this is the sale. You can see the comparison, right? If you want to put the lo as well, you can put the O here. You can see this way. You can create a graph, create chart where you can sew the sales for each location compared with the total sales. This way you can analyze. This is for Mumbai, this is for Lucknow. Okay? We have successfully answered the first question. Next one is second question, which is percentage sales contribution by each location. For this, what we need to do, we see here we have this table, what we have done. We have the total sales for each location, right, For Bangor, for Luck now, and for Mumbai. So we have this total. Here, how we get the percentage? We need to divide total for a particular location. Total, right? Overall total sales, right? For that, we need to create a new major first. To put the new major first, right click here, Create a new major that we will say a sales, How we get the all location sales by using the calculate function. I'll just remove this. I'll use the calculate function here. I'll remove this, I'll put here as all. Now we have this calculated function, Will calculate the sales amount. It will sum up all the sales amount for all the location. Let's commit this. I'll select this table and I'll put the all locations in this. Now you can see here, total sales for luck now. And here I'm getting the total sales for entire business for all the locations. Now we're getting, now next thing is percentage for each location. For that, again, we need to create a new major. You can either right click here and create a new major. Or you can come here and click on here. New Major. Here, I'll put the Percentage Sales part location. You can write whatever you want, okay here, Percentage Sale. How we get, we need to divide the total sales. For that, I'll use the total sales divided by all locations site. What was the name? All location Sales. Yeah, this one. I'll just commit this. Okay, here, let me select this table and put this as well. See here now we are getting the percentage sales for each location as well. But this is not coming in a percentage. So we need to go to this major percentage sale here. We need to change this two percentage select percentage. Okay, Let me change this. Okay. Now you can see here now we have for Bangalode location, this is the total sales. This is overall total sales including all locations. And this is the percentage see here. Mumbai is contributing 34% Laco is contributing 32% and Bangalodes contributing 33% of the total sales. We have answered this question as well, contribution by each location by creating new Mejor. Here we have created two measures. One is all location sales and the percent per location. With this, we can conclude, okay, now we have this thing available. If you want to change it to some other chart, you can do it. Okay? If you can put the donor chart, much better way will be this matrix level, okay? This way we can answer these two questions asked by the bags. Compare the total sales for each branch with the total sales across all categories. Calculate the percentage sale contribution by each location. With the two, we have answered these two business questions as well. Okay? You can also practice more with such questions you can think of and you can try to answer with creating reports in this as passport. Okay? See inside the next lecture. 50. Calculate Function in Filter Context: Hello and welcome back. In the previous lecture, we have seen how we can compare the location wise sales. Here we have compared the total sales for Mumbai and social sales for luck now by gene, by filtering with the product category. Here you can see this is the sales for Casual were. This is the total sales for Casual were Mumbai and this is for luck. Now this is for semipermal. This will be the total cells including all the location. And this is for Mumbai. And now now why we need to create this measure, for getting this analysis? Because when you look at the power, BI power is a columnar data base, right? It's stored data in the columnar fashion, right? In that case, these measures can be put into the only one axis, right? But we want to analyze each location. We needed to create a location specific measure that we have created here cells for luck now where we have used the calculate function, and this is the importance of calculate function in this scenario. This calculate function, what it does is take the sum of all the cells amount cells. Then it filters for that particular location with the filter data location called luck. Now it will give it will total cells for the luck. Now, only this way we can get the total sales for luck now. Right? Similarly, we have done that for Mumbai. That is why we need calculate function and that is why we need to create a measure to get this kind of analysis. Okay. Now we have also created a measure for all location cells. And here also we have given a filter, all data location. Here also we are taking all locations as a filter. Right here, we are considering not only a total sales, even though we can put total sales, but we kept here a filter filter. Why this filter is important and what will happen if I don't put this filter? That is what we are going to see in this lecture. Okay, see here. Now we have created this filter. This filter will be supposed create online order. See these. All location is, it is changing online and offline soft order. If I select Mubi, this will change, right? It is changing for each and every location, right? Each and every filter, all the filters, external filters that we are applying. This is changing all locations, changing based on those external filters. Because here we have used all location as a filter, right? So, and center sales also, we're reaching the total sales dividary or location sales. Okay? Why this formula will work here? Because when we apply filter here for Mumbai, so that will become only for Mumbai. While calculating the filter right here, total sales will be 469. Total sales for Mumbai will be 160 double zero. Right now what I'll do, I'll just copy this, a formula. What I'll do, I'll try to create another major. I'll create a new major that will be a little different from all location says. I'll give it a name. All location stays one, okay? Here. Instead of putting all data location, I'll remove this location filter here. I'll just put all data here. What I'm giving as a filter is all data, okay? Same calculate function calculating the total sales. But here instead of giving all data location, I'm just giving all data. Let's commit this. Now we have two major. One is all location sales, where we are getting using the all as a filter. And we're considering the location as well. But in all location sales one, we are giving all data. Here, we are not specifying the location. I, I'll use this one. Okay? Here. I'll include all location sales as well. Now see here we have the location, all location sales and all location sales one. Now everything is same, all location sales and all location sales one. Both the values are same. But as soon as I apply the filter support on see here, now, all location sales is repeating here. But all location sales and all location sales one, both are different values. See here, 46, double 9,850.44, 126. Now this on filter is getting applied on this one, but it is not getting applied on cells one. That is because on all location cells one, we have not given any filter. We are considering all the data. Any filter will not affect this. All location cells you apply, whatever filter, external filter we apply, it will not change. It will be remaining same. All location cells will change. But this will, this is how we can use the calculate function and we can play around with the filter while applying this way. If you want total sales to be repeated here, you can use like this. Okay. I hope you got the clarity, how to use these filters and how to create these measures. If you have any doubt, you can comment in the class discussion area and I'll be happy to answer your questions. Thank you. 51. SUMX function: Hello and welcome back. In this lecture, we are going to learn about how we can differentiate between some and some x. In a little more detail. We have already seen how to use some x here. I'll tell you the difference with this small data that I have. I'll explain you the theory behind it and then we'll see the hands on in power I. Okay? If you look at this small data here, we have the receipt number, right? Then we have the sale date, status, order, type, token number, product name, product category, quantity, unit price, and sale amount. Okay. This sale amount, I'm calculating. Okay. Here, for each receipt number, the sale date, status of that sale, and then what type of it is online, online online sale or offline sale. And the name and product category, then the quantity and the unit price. So you can see there's one quantity for this receipt number and unit price is nine. Sale amount will be unit price into quantity. That is the formula. Unit price quantity will give us the sale amount, right, for this receipt number. For bill 11, 11. Bill 11. Bill 11, this receipt number, we have sold the amount for 900. Similarly, for this bill amount, bill 12, there are two quantities sold and the unit price is 900. It could be 700. Well, we can see that. Okay, 700 into two will give us the 1,400 Similarly, this three quantity for this bill number and unit price is 500. 1,500 Okay? 500 into three. This way we calculate the sale amount. Now if we use the sum function and create a major total quantity, what it will come, it will come as, this quantity will come as 12. When we put sum of unit price, it will come as 4650. Now to get the total total sale amount, what one will do? Just multiply the total quantity sold into unit price and this will give us this sale amount. But this is quite wrong because we have not sold for 55,800 right? But when we use the sum function to get the total quantity and total unit price, and when we multiply, we get this very high number, 55,000 which is high compared to the real sale amount. In this case, what we need to do, we need to perform the row operations for each bill number. For that particular row, we need to consider the quantity and unit price to get the sale amount. Similarly, for each bill number, we need to do that, we need to perform the row wise operation here. When we do that, we get this 900 160030040014001501000. When we sum this up, we get the total sale amount, each coming up as 8,200 only. Whereas if you do use some function for total quantity and total unit price and then total sale amount, then what will happen? You will get the wrong sale amount in such criteria, such situations where you need to perform the row wage operations and then sum it up, then you have to use the S x function. You have to remember that whenever you need to perform operations like quantity into unit price, we'll give you the sale amount for each bill. And if you want to find the total sale amount, you need to first perform this row operations and then up the amount to get the total sale amount. Okay? I hope this is clear so you have to remember this. Okay, So now let's move to the power I and do this in practical here. I'll create, I'll remove these filters so we have the total sale amount total sell as 5 million. Let me create a major. I'll right click here and click on Major here. I'll put Total Sales, Okay, here. I'll use the Six Sun here. I'll keep the data here. I need to keep the data cost per unit. And then into what is the other thing? The cost, cost per unit. Cost per unit into cost per unit into unit price, okay? So here we need to take the cost amount. Sale amount. Sale amount, okay? So this will keep us the total sales. Here I'm using some function. What it will do first, it will go and multiply this cost per unit into sales amount, okay? Unit unit price, sorry. Here we need to take into account has selling per unit, okay? And when we do this, okay, total sales is already your total sales. Let me now what I'll do. I'll create a card here for this card. I'll use this. Total sales, Total sales amount, okay? So here we have done cost per unit into selling per unit, right? So we need to put the cost per unit, okay? Here I have done wrong this cost per unit. We need to use the quantity here. Quantity into cost per unit, okay? See here. Now we are getting the total sales amount as 4 million. This way you can play around and see how you can use the sum, x and sum function inside the next lecture. 52. Creating Age Bracket Column using DAX: Hello and welcome back. We have seen some pretty good examples of tax, and we have created the major, and we have also created a few calculated columns. Now it's time to move further and use tax for some other advanced things, for a real logic or something we can do out of the box. For that what I'm going to do, I'm going to open a new, I'll open, okay, I'll move from here, I'll import a new seat where I have kept the. Okay. I'll move from here, I'll import a new seat where the customer data and there we will try to do something with the K that will be very useful. First thing I'll get the data. I'll get the data from the Excel workbook. Here I have downloaded customer Excel file and in that Excel file we'll see what is the data that is customer Sp data. Click on here and see here we have the Customer detail here. Okay, let me load this before loading. If you look at this data, there are a few columns which are load this before loading. If you look at this data, there are a few columns which are having no values, right? See here up to age, we have the values for column 11, 12, 13. That is null, That are the empty columns that is there in the data you want. You can click on Transform and transform. It delete those columns. Click on the transform, it will take you to the power A, where you can work on transforming your data. See here, now we have the null values. So we can go here on the remote column and click on remote columns. So see here as expression for remove column is table remove columns. And then you have to keep the file name, customer, underscore, seat and column name. When you commit this, that table will be, that column will be removed. We still have some columns. So now what I do, I'll close and apply and let the data load. Okay, there are few errors. Let me see. Okay, for somehow that thing has, so let me import it again. Let me expand it here a little bit. So these are the columns that I don't want, right? So I'll just select all those, 11-36 and then we can see if you find any option to. Delete or something, see here. Now when you write Click, you can find the option, Delete from Models. We'll try that. Yes, I want to delete. Okay, so deleting one by one only. I delete one by one. Going to hamper anything that we are going to do. But it is good to remove unnecessary columns and roles that are not of use because they're going to unnecessary At sometimes performance of our visilation get affected. Because of these unnecessary things, it is better to remove them before proceeding further. Customer name, website, country description, and we have the which year that company have been founded, detail then the industry, which industry operates and the number of employees that company has. And then the age of the founder, 3052. Like this, we have the age. Now what I want to do, I want to categorize. I want to put the people into age bracket. Suppose if you are 45, I want to categorize as a 45 to 54. If you are greater than 35, I want to put you in the category of 34, 35 to 44. If you're greater than 25, I want to put it into the 25 to 34. If you are less than 25, I want to put into the category of 18234. Similarly, if you greater than 25, I want to put it into the 25 to 34. If you are less than 25, I want to put into the category of 18234. Similarly, if you are greater than 55, I want to put into the 55 plus. In this way we can get better understanding of what is the age group in our organization or in our data. That's what I want to do For that. I want to do this by creating a calculated column. And for that we're going to use the tax to create a calculated column. Select the data. And here you select column. Just click on the new column. Select new column. Just click on the new column. Here I want to give bracket as a new column name. Here what I want to do, I want to write the tax formula. I'll select, if I'll select customers to put as 5052, sorry, 55 plus okay. So this will be one condition, sorry I shouldn't have put okay. And then you put Sienta and then I'll just copy this. And here I'll paste. And I'll put another condition. If it is greater than or equal to 45, I want to put that as a 5052. Sorry, 45 to 54. 45 to 54 Septa here. The next condition, I want to put greater than 35. Far greater than 35, I want to put into the category of 35 to 44. Then set copy, paste. And here I want to put the next category. A greater than 25. Greater than equal to 25. And it will go inside the bracket, 25 to 34, right? Then I'll put Septa, I'll give the last. As 18 to 34, right? And then I'll close all the brackets. I'll cost close this. Then one more bracket. Now I want to create a new column that I'll give the name as age bracket. Then I'll put condition here if customer age, then I'll put condition here. If customer is 55 greater than or equality 55, I want to put all those customers age bracket of 55 plus then if customer is greater than equal to 45, I want to put all those customers greater than age 45 or above, greater than 45 or equal to 45. I want to put them into the age bracket of 45 to 54. The next age bracket will be 35 and above. I'll put that in a 35 to 44. Then the next category will be greater than equal to 425, and I'll put them into 25 to 34. And then the last category will be 18 to 34, 18 to 24. 24, 18 to 24, better than I equal to 425 and I'll put them into 25 to 34. And then the last category will be 18 to 34. 18 to 24. 24. 18 to 24 will be the last category. Now I'll commit this. See now we have a age bracket here. When you go into the data model here, you can see the 35 has been kept into the age bracket. 35 to 45, okay? And then 45, 45 to 54, 65. See the age brackettach clearly identified as 55 plus 47 belongs to 405-25-0407. Belongs to 45 to 54. 33 is 25 to 304-20-9205 to 34. 30 is 25 to 34. Similarly 59 is pity five plus 52 is 45 to 54, 55 is pity five plus how Easily we have created a new column where with simple formula, tax formula, we have categorize our customer base into the age bracket. It has done in a second, right? As soon as you define the formula, your new column has been created. This will be quite huge pol in analyzing your customer base. Which customer base? Which age bracket is buying your things? Which age bracket? Huge in analyzing your customer base. Customer base. Which age bracket is buying your things? Which age bracket is your customer? Which age bracket is buying, more or less? This will be very useful this way you can use to create age bracket. If you have some requirement like this, you can create a new column by using the x function and putting the logic into it here condition. You can use some other conditions as well inside the next lecture and putting the logic into it like here we are using if condition. You can use some other conditions as well. So inside the next lecture. Inside the next lecture. 53. Extracting Month Year from Date using DAX: Hello and welcome back. In this lecture we are going to learn a new thing and that is like we have here sale date, right? But I want to create another column where I want to separate month year from this sale date. That is quite a good exercise. Many a time in when we do a real world project data visualization, there may be a requirement where you need to work on the month year. Extracting the month year from the sale date column. See, this is fourth January 2020. I want to extract only January 20. I want to know the month and year so that I can do further analysis on that. In those scenarios, what we need to do, we need to create a calculated column and we need to extract the month year from this date column or whatever you want. You will extract the day, right, day, month, year. All those things we can do. Let's get started and try to create another calculated column where we can get the month and year from this 40 we need to go. We can go anywhere. Okay. We'll go to the Data and right click and create a new column. Otherwise, you can come here and click on a new column. To create a calculated columns, click on New Column and we need to write the expression here. I want to give it a name, month here, because I want to extract month and hear from the date filled. You need to put month here equal to the shift Enter. And here you need to use the format function. Then inside the format function, what we need to write F, we need to take the date from where we want to pick the date. Here we have the sale date column, right, which is having the date. Now we have the sale date. So now we are telling the X expression or format function to take the date from this saldate filed and then Sept enter. And here what you want to extract? I want to extract month in a M, extract month in a MM permat. Then you can put as Y, Y, Y, Y. What this will do, this will extract the month in MM format and here in Y, Y, Y Y format. Okay, So you just close the bracket. See here we are creating a calculated column called month, year. We are using format function here, I'm taking the column from our data model, date from sale date. I'm extracting, we are taking only month in MM format and year in Y YYY format. Okay, This way we can extract. Let's click when you click on Commit. See here now we have a calculated column called Month Year, and see the sale. Or January 2027, March 2020. Like that, right? But here we are getting only January 2020, March 2020, 02020 or 03 hyphen 20200 to 2020. Like this, we have successfully done what we wanted to do here. If you want to put, if you want to make M M, M M, then what it will do, it will give you in the format of 2020. Now, in this format, you can put MM M, okay. If you put four times MM, it will be January 2020. Okay. Full whichever format you want or whatever requirement is that thing you can do here. I will do with the MM YY. Whenever you're writing a tax expression or any programming language, or any scripting language, you just try to experiment with the syntax. See here, If I put MM YY, what will be the output? Either it can throw an error or it can give you some learning, right? Just commit and see what. To see here. It will give you the year in 2020 for January 20. Like this. Whatever you want to do, you just if I put three y, then what it will do, it will give you like 204. Here it is like this. When I three see the result, it is something 204. How 204 is coming that we need to understand. It is 2020. But when we are putting Y Y Y what it is taking 20 from this and I think the date, year 2067, How this 2067 is coming. Which field is this? That is giving us quite a wrong thing right now. With this, you can understand that this should be in Y, Y, Y, T. Okay? Here you can put DD, MM, YY, and it will give you for January 2020, right? If you put DDD, then it will give you the date, the day on that particular Saturday, January 2020. This way you can see here, this is first fourth January 2020 and here, fourth January was Saturday, Saturday, January 2020. If you put three DD, it will give you day, month, year. If you put four DD, then it will give you Saturday, right? If you want only the date, you can put DD, MM, Y, Y. Okay? If you want day on that particular date, what was the date that you can put like this? If you want to experiment further, you can put D and see what will be the max. It will throw an error right here now, DDD phon, DD phon, MM. Hyponyys giving us day, date, month and year. Now it is quite good thing right now we can know the date also, day also, and month, year as well. This way you can experiment with the syntax and get useful information. My purpose was to introduce you, uh, the format function with that per function. With that permit function, I wanted to extract the day and date, month and year from the sale date. We ended up getting the day date, month and year as well. I hope you understood the syntax, how to get most opt the syntax, okay, see inside the next lecture. 54. Using Switch function in DAX: And welcome back. In this lecture, we are going to use the switch. We'll see how we can use the switch, like you know how to use the lobes and all here we're going to use the switch for that. I have added a column in our data model that is called territory. Territory will have options like east, west, north, south, like that for each and every entry. Okay, Then each territory, for each entry, we have the territory. And then total sales. For this, it is belonging to territory and total sales is 45. There is another territory which is having 1010. Okay, like that. I have added few data here. Then I want to create another column, which we'll see that the entry belongs to region volume I want to create. See here, I have created a column, region volume. What this region volume will do, it will create a column and it will give you information that this cell, total cell 45, is a medium volume region. Belongs to medium volume region. How I have categorized this? This is the calculated column that I have created. It was not there. This is what I want to do. To do that. First thing, what I'll do, I'll just delete this thing. Okay. Now, when you look at the table, it is having the territory column and total sales that is newly added. I have added this too. Okay, This sales territory. Now I want to create a column where I can see that based on the number of sales, I can categorize these entries, this territory. This usually belongs to a territory and having total sales as 45. I want to categorize that as low volume sales region or high volume sales region. Okay, for that I want to create a column. So this belong to south territory and has done total sales of 12. I want to categorize that as a low volume or average volume sales region. How I can do that? User belong to South and 78 high volume, right? To do that, what I can do, I can simply, we can go here, customer and creator, new column. After that, here we need to give the column name. I'll give region volume here. After that I'll enter. And here I switch and switch, true. After that switch true, what I want to give, I want to put, then I want to put conditions like customer total sales. I'll take the column total sales equal to 50, I as a 60 high volume region. And then I'll put another condition. If it is 50 greater than 50, then it is a medium volume region. And if it is 40 average volume region, I'll put it a 40. I'll put this as 20. Okay? And then I'll put one more, sorry, I'll put another here. This will be ten. And sorry I need to put control enter. Okay. So here I'm giving a greater than 60, high volume greater than 50, this medium volume greater than 40, average volume greater than 30, low volume. You can put greater than 50 high volume, whatever you want. You can decide based on your customer requirement. Right? I'll put this low volume region ten, I'll put below low, then I'll commit this. Okay? I need to put here, not here. See now we are getting four total cells. 45, we're getting medium volume region. For 25 low volume, 24 low volume, 12 ways below volume. Below low volume. So I need to correct this below low volume region. Okay, just commit this here. With this, we have cell or every cells, persons as a medium volume region or a low volume region. This is on a very high level. If you want to go further, you can take how many total cells are there in east region, west region like that. You can create another column and then based on that you can categorize them. Right? To give you understanding I have done like this. Okay. If you have any requirement like this, you can use the switch true and then you can put your conditions. Okay. So this way also you can do, you can see Dax is having so many expressions, so many options like any other programming language, you can do all sorts of, all sorts of things here, right? So I hope you got to know something new see inside the next lecture. 55. Project 3 Introduction and Establishing Relationships: Low. And welcome back. In this lecture we are going to start doing a new project where we are going to analyze a cookie business data. We have a data of a cookie business where this sell cookies. We are going to analyze this data using power BI. We have three Excel file, one is cookie types and another one is customer data and the main table is order table. Okay, here, order table, then customer table, and then the cookies type. These are the three tables provided by the customer. And we need to get the insights from the data. Okay, we need to keep some visualization. We need to use all the power way knowledge that we have gained so far. And by columns major, applying formulas, expressions, everything we need to get the useful insights For this bill, let's head over to the power I. First thing is we need to get the Celorkbook, get data. You can go to get data. Acebooker can actually click here, path here. First put this one order table. Now order table is being updated imported. So I'll select the orders and see the table data is looking good. No need to do any transformation. Click Load Order Tables will be loaded in our RPI 700 S, see here. Now we have the order table. You can go to the Model Table view. Also, you can see the data has been imported. Next, we need to get the other two tables as well. I'll take the customer's table now, we'll import that also here. Also we need to select the customers. Now need to select the table one. Now we have the customer table also imported. You can see the Tableviews. Well, next thing is importing the last table is cookies. Type open that file and it will be imported into power BI. Now click the cookie type and load it. Now we have the cookies type as well, imported into our power BI. Now we'll see the data see here. First we'll see the order table, which is the main table. Here you can see the customer ID, order ID, product units sold, number of units sold for a particular order and a customer, then date, then revenue, and then the cost, okay? And then we have the other table that is customer table via the customer ID and customer name, phone number, address, city, state, chip, country, and some nodes. Okay, so these are the two tables and the third average cookie type here, cookie type being sold, chocolate chip, fortune cookie, hot pill regime, sneak udall sugar and white chocolate cadena. Okay, here also you can see cookie type and units sold and revenue per cookie and cost per cookie. So there are three tables. Order table is the main table for this bigness. And these two tables, customers and cookie type, we look up tables which are supporting this order table. Okay, Now let's understand this bainess see here in the order table. The first thing is they are selling the cookie and they have the six type up cookies being sold. Their cost per cookie is given here, and revenue per cookie is also given here. Next table, customer table with normal customer ID and customer detail name, number, address. All those things has been added. And then the main table is order table, where customer ID, which customer has ordered that order ID product, which type of cookies, and what product he has ordered, the units sold for that customer, order date, revenue, and the cost. These are three tables that we have got from the customer. Now we need to use our power via analytics and find some useful information by looking at the what are the things you can find out? The very first thing should be how many total. Here you can see. Each customer ID, there are so many units sold for this chocolate chocolate chip. Then for meal also customer ID. Three. First thing which comes into our minus total, how many units sold? Then we can find the total revenue and we can find the total cost. If you make a revenue minus total cost, we'll get the profit. All those things we need to do this for this BainessIt, I want you to look at this relationship. See here, when you look at the table here, there is a relationship between, let me rearrange this, okay? It will not go, Yeah. Okay. And here there are three tables, right? But when you come to the model, you can see here, there is a relationship being established by power itself automatically between the orders tables and the customer table. Why it has been done automatically because order table has a customer ID and the customers also having customer ID. Right? When you click on here, you can see both the customer ID has been highlighted for this and this. There is relationship between these two tables and based on the customer ID, and this is 12, meaning because one customer can have multiple orders, there is 12. Many relationship has been power has been established by power, by automatically now. But this cookie table has no relationship between the table established so far. We need to find the relationship tables, right? Any of these tables should be related to this cookie type. Otherwise, we'll not able to get the relationship and then we'll not able to how many cookies sold for which order, right? We need to establish a relationship between these tables. This is done by the power automatically when we imported it found the similar name column and it has established customer ID to customer ID one too many. Now we have to see cookie type cost per cookie, revenue per cookie unit sold. Here, we cannot relationse between these two. But there is one thing that we can relate this product. If you go to the table, you can see the product is chocolate chip and fortune cookie oatmeal gene and Seneca Doodle and sugar. Right when you look at the cookie type, cookie type is also the same thing on the name has changed here in order table, product, in the cookies type table, it is cookie type. With this, we, I've come to know that these two product and cookie type both are same thing on the name has been changed in these two tables. What we can do, we can select this product. We establish a connection between product of this order table to the cookie per type. Because both are same, you just select this product and drop it on the cookie type and the relationship will be established. Just leave it here. See here. Now we have established connection here. This is also one too many because one cookie type can have multiple product, okay? Right? See here in this cookie type table, on sorry, the cookie type, this entry is only one time, right? But in the order table, it is being repeated for multiple orders, right? Many order Ds have the same product. That's why he power has given a relationship one too many. One cookie type will be related to many products. One too many. Here also one too many. This way we have established the connection or relationship between these three tables. Now what I can do, I can just put these customers here. Now you can easily see the relationship between these three tables right here. Customer ID is related to, customer ID here, cookie type is related to product. Okay, Now we have established the relationship. Next thing is we need to start analyzing and finding, finding something useful. Okay, In the next video, we will try to create a major, major, where we will be finding the total unit sold. 56. Using Fill Up and Down: Hello and welcome back. This is your teacher for this class. I'm back with update for this class. It's been two months, more than two months actually. Abbott updated this class. Before that, I was updating this class regularly. But last few months I was beginning some other work. I couldn't get time to update this class. Now I'm here to update this class with some of the very important uh, centric that you can use while working on a power project, data analysis or whatever project you're working. These tips and tricks that I'm going to cover in few lectures will be very useful when you're working on a real time project. They are quite handy. You cannot find it anywhere on the night. Like these are the tips and tricks we use and we come to know when we keep using the power on a regular basis. These are very useful things for that. I have created a simple data set here which is having the Indian state and cities for that state and population. Okay, So it is Kanaka State in India. Astra capital is Mumbai, Bangaloity of India, and Silicon Valley of India. Now we have this set, we are going to learn our first states this set. Okay? Here, uh, I have collected this data from Wikipedia for the population data for the cities in Atkins Strats. Okay. Now, the first thing before we proceed, we need to take this set to the RBI. Let's go to the RPA here, we'll go to get data and cell workbook. Or you can simply click on L workbook. Or you can simply click here on the import data from L. Whichever you want, you can. Okay, I haven't downloaded it, so first thing we need to download this from the Microsoft, sorry, Google, click on download, and now it will be downloaded here. Now I'll select this. Okay. Now the data will be as uploaded here. I'll select this here, I'll simply load it. I'm not go into the transformation or some other things. I'll simply load this. The set is being loaded. 70 rods has. Now when you look at the see now, we can see a master come down, right? Okay, let me go to the transform see. Now, when you look at this data here for this Canara, all these cities are in Natalia, right? But only for the first Canta and rest of the planks for those columns, it has null. Now if you want to, for all the null you want to fill cana mata, for all these you want to fill mast how we can do with a single click. That is the clip for this lecture. For this, if you want to do, you cannot manually go and write right. For that what we can do, we can go to the sell corner. Transform fill. And here you can put down fill, click on down and see here. Now, for all the states belonging to Arata, it has come aka T Mumbai, it has filled the Mumbai. Automatically, you need not to tell Parvay these are the states from Mast because we have started from here. It will intelligently understand that oil cities belongs to the Mastro fill mast, right? You understood in the same way if suppose if you want to up, if you have given the master down here and all these are plank and can here, all level plank you can go and fill up and it will be filled. This is the simple trick that you can apply to fill the null values. If you want to put the same beta, you can fill up and fill down and it will be automatically done for you, right, close and apply. See here now, all the, all the columns have been Conductor and Marastraversr. If you want to do things like you want to city population comparison, you can do here now is the population comparison for different cities, Mumbai, Bang, Pena, or A those like. Whatever you want to select, you can select connect with Bra data. If you want to put straight and you can put, you see the blue color are from light blue from mast and dark from light blue from land and dark. This was a simple to fill the values. See. In the next lecture we'll learn about some other, we'll learn about some other Tes. 57. Decomposition Tree in Power BI: Hello and welcome back. In this lecture, we are going to learn one more tab centric about power BI that is very useful when you walk on real time data, real time data analytics, data analysis project. In this lecture, I'm going to talk about power I, how to use I in power. Okay, so for that we need to go to the insert. And then here you can see a few options, you can see that were not available earlier. One is decomposition tree, we'll see what is that. Then another one is the narrative. Okay, now we'll see first what is narrative see Here we have this report, right? Suppose if we have created a dashboard in this report. So like you will go and say that, okay, some of the population 2001 by that, right? You'll get some analysis by analyzing that manually. But when you click on Narrative, what it will do, it will add an auto generated written summary of your data to this report. Based on this report which you have created, it will give you the autogenerated summary for your data so that you can easily share with you management. Let's click on that narrative and see here. As soon as I clicked on the narrative, I got this total sum of population 2001, 2001 was higher Maastra than Aka. It is saying that in this report, with this report, we can see that total population of Mat in 2001, way greater than aka. This is one analysis that we are getting from the AI. Okay, then the second one, what is saying Mumbai in the state Marastra made up 26.72% of summer population. That means total population of Maastra, Mumbai population was 26.72% of the total population of Mast state. That's why a biggest state, biggest city Maastra, it is 26.70% of the total population of Maststrand thing, average sum population much higher Patra than now. Here it was, the average population as more in mast. This is the thing we got from this narrative thing we'll see in detail in some other lecture. Okay, when we have some better dashboard, then we'll use the narrative also. Now the next thing I want to tell you about the decomposition tree. When you click on the decomposition tree here, you will get this, minimize this, and maximize this. Okay? So you will see this decomposition three. Let me first tell you what is decomposition as for the Microsoft website decomposition three. In power decomposition three, visual in Power Wales, you visualize data across multiple dimensions. What it will do, it will allow you to analyze the data in multiple dimensions. It automatically aggregates data and enables drilling down into your dimensions in any order. It also an artificial intelligence I visualization, so you can ask it to find next dimension to read down into. Based on the criteria, certain criteria that you can get. The tool is valuable for ad hoc exploration and conducting root code analysis. See here, it will take you step by step to the new dimension and you can analyze your data. It can be used into scenarios like supply chain scenario that largely the percentage of product a company has on back out of stock. A sales scenario that breaks into video game sales by numerous factors like game genre and publisher. These are the scenarios you can use This thing when k this thing. Composition tree, you will see. Or two options. Analyze and explain why. Based on those two things, we will get our answer. Let's go to the power now to come to the decomposition tree. We need to come to the insert and then we need to take on the decomposition tree. And this thing you will get. I'm here. You can see the analyze and explain by explained by what I'll do, Analyze by population and explain why. See here now we are getting the sum of population in 1991. Is this When you click, you can see here plus sign here. When you click on the plus sign, it will show you the options high value, low value. And when you click on CT, you can see here, it will give you the data, CT wise, home by population n. Okay? You can select this also, and all the reports will get filtered based on that. Okay. This way you can build down further. Next thing when you click here. When you click on High value, the lowest, the highest population, then Bangor. Likewise the data. Next thing you can put City, We have low value. It will show you the lowest population first and then take you to the highest. This is the huge decomposition tree. We can put state as well. When I put state as another diamond sun, the plus sign appeared here. When we click again, we'll get a high value, low value and state on high value here it is suing Maasai belong to Mas. Okay, so this way we can analyze. Now we are seeing built on the state. Now we can see the summer population in Maastakanatka. Then when you click on At, it will show you the details for the cities intra select Kanaka. It will show you cities in A. This is the power of AI that we can get this decomposition of data and we can analyze faster if you want to root cause analysis, you can do it very easily in the next lecture. In the next lecture. Next lecture, we'll keep on looking at the typsentric. We'll try to explore more hidden tipsric when we walk, walk quicker and faster and smarter inside the next lecture. 58. Quick Use of Narrative AI Feature : Hello and welcome back. I'm back with another exercise. In this, we are going to simply, we'll create some visuals and we'll try to visualize data. I have already taken a data sample data from the Cagle website. That is the sample sales data. What I'll do, I'll just import it. Actually, I have already imported it. This is the data I'll show you. This is the sample data that I took from the Agon. Here we have the various columns for sales data for a particular company. And the columns are order number, quantity, ordered, price for each order, and then the lead in number, and then sales and the order date status. This is quarter I, the month ID. Then we have the year ID. And then we have the product line for which line that product has been sold. Like this is the data for the car sales. Car sales. So we have the product line like classic cars, trucks and buses. Vintage cars. Okay. All kind of cars here. Then MSRP and then the manufact, that is. And then the product code, then customer name, phone number, address, line 12, and then city, state, postal code, country territory. And then we have the contact, last name and the first name. And then the deal size here. This column is very important because it will tell you what kind of daily deal size is. Medium, large, small. Most of the things you can see here, we have large, medium is small. But most of the deals are mid size, small also there. Then we have all three categories, deal, size are there. When you look here, see small, medium, large. All of this is the data that I got from Cagle and how we are going to visualize and analyze this sample data for this. Okay, this is the old visualzation that we have created. I'll create a new page here. I'm continuing in the same. Okay, so here we have the data source, Sales sample, sales data, underscore sample. And here we want to visualize, if you go and see the relationship, there is no relationship established. For now. We are not going to deep into establishing the relationship and all. We'll just try to visualize the data. Suppose you get the data from your client and you want to just tell them a few of the important things. They want to just visualize the data and analyze the things. In that case, we'll not deep drive into establishing the lency between the different tables or we're not going to transform the data, we're not going to remove the null values that are looking at the data. We'll just try to quickly analyze the data and give the input to the client so that they can start working on their marketing or whatever they want to do. Okay, understood. This is the basic agenda for this lecture. Let's get started here. What I'll do, I'll try to first create some utilization. I simply select the stack column chart. Then I want to analyze the sales by. Suppose I want to analyze the sales by city. I'll select City and then I'll select the Sales. See here. As soon as I selected these two, I am getting the stack bar chart here. And you can see for Madrid, this is the sale and all this, okay? This is one visualization I get. I'll tell you why I'm creating all this. This is next visualization. What I'll do, I'll create a pie chart. Here, select the pie chart. And on this chart, what I want to do, I want to analyze the what we can do. I'll select the, let me select product ID, where it is, product code and sales. Instead of product core. Instead of product Core, I'll select the Country. Okay, this looks good. And Country, and Sales. Okay, so now we have these two. Let's create one slicer here also. I'll select a slicer here. For slicer, I want to suppose slicer, I want to put the country or city. City here. When we select city, it will give us the city specific data. Okay, now we have created the three visualizations here. Now I'll go to the insert. And here we, I'll just click on the narrative and see what are the things we are getting from the I see here. As soon as we clicked on the narrative, it is giving us the summary for our visualization that we have created. The Fs in AI is telling us that at this matter has the highest sum of sales. Now, when you look at this chart, you come to note that okay, Madrid has the highest sale, right? The same thing I is also giving us matters but with some more information. Like it was 3,000% higher than the Hall Roy which had the lowest sales. See here now we are getting the quick analysis that from here we came to Not Madrid. Madrid had the highest sales, but we didn't know which was the lowest. Here, when you see you will come to not okay. This city has the lowest and this city has the highest sales. But here what he is doing is quickly, it is telling you that the lowest and the highest has this much difference, around 3,000% differences there between the highest and lowest. Then the next is Madrid accounted 10.79% of the sales. Okay. And then across 73 cities, sum of sales range from 33,442 Tail attitude 551. These are the information we're getting quickly from the I just by clicking a narrative button, right? Just click on the narrative and you'll get all this information. Whenever your client is coming up with some quick uh, sets and they want you to analyze, you can just create random things. Not random, but whatever you understood from that data, Just create few visualization, few charts and then click on narrative and quickly get the information that you can within second or mine. You can forward it to your client and he will be so happy. This is one act that you can use when you have less time and you want to provide more information to your client. So I hope you got to know how to do this. If you want more information from AI, you can create more number of charts and first thing is you need to understand the data first, you understand the data and then only you will be able to create the correct charts. When you create correct charts, you can visualize more and you can get more from even AI, right? I hope you are destroyed. And we'll meet in the next lecture. 59. Class Project Submission Guide: Hello and welcome to this lecture. And I hope you have completed this class and you have understood how to create Power BI dashboards and charts. So in this lecture, I'll explain how you can quickly, could heat your project and see it on whatever project you are creating that you can say it with me in, through the class. When you come to your class, you can see your project and resources. And here I have given the clear guidelines how you can eat our project. So we need to use the customer analysis dot XLSX file. And we have to import this file into the PowerBI and create the customer analysis report. This report, you have to wait on the Excel file. And once this report has been, a dashboard has been created. You just take a screenshot of this dashboard, save it as a JPEG file, a PNG file, and then come back to the class. Here you find create, project ops and you just click on that. And it will take you to the kidney IT project six. And here you just need to provide the project title like here, the project title would be customer analysis. Analysis. Suppose your name, my name is Sunil, and here you just provide a short description and then upload that image that you have taken as a screenshot. This is the screenshot. So you just upload this click Open and this will be uploaded. So you just click on submit. And that's where the image will be uploaded here. After that, you want to add more images you can put, okay. Or if we want to make up to just upload that image and click on Publish and your project will be submitted. Okay? So this way you can getting into class project and some mature NASPA image with us so that I can go through it and share feedback with you. Thank you. 60. Class Project and Conclusion : Now we have completed this class and we have learned how to create interactive dashboards, how to add this defaults to our dashboard, how to make it interactive. So now at the end of the class, I will give you a project to do. And that is a pretty simple project because in this, I'll give you this Excel file, which will have the customer analysis on this. And they've done that, you will have to create a report similar to this. You can create your own report and dashboard, but I'm giving a jump on that. That customer analysis reports would have the top five customers stopped by product by sale, hotel customer total quantity, total order, total profit, total sales by month and year. Total profit and sales by store and new customers, repeat customer and lost customer by ear. So all these reports you have to put into the customer analysis dashboard and report. And you have to submit that project to the class projects. So that is the simple class project I'll give to you. I'll give you the Excel file for the project that we have done into the class and for the practice and project also, I'll give you a customer analysis dot Excel file that you can use to create the project. So I hope you'll create this dashboard and submit to the class project and share with us so that we can go through it, try to make it more interactive, more dynamic and more colorful. So thanks for watching this class. I hope you learned a lot from this class. Thank you.