 Hello and welcome to this course on Power BI Beginner Class. For those who don't have any knowledge on BI or Power BI, you are absolutely on the right track because we are going to do step by step starting from scratch. We are going to build a Power BI report using data extracted from Excel file. Our source file looks like this. Nice and simple, and from time to time, we will add in new data points because we are going to learn starting from the basic. Once we have loaded this source into Power BI, we will create some simple visuals to begin with. And the goal for us as a business analyst or BI developer is to create insights for our clients and to understand their customer processing behavior so that our clients are able to leverage the insight generated from the visuals and to make a wise decisions or a quick move before the business starts to get more and more declined. First of all, we are going to learn how to load in our data source into Power BI. Then we will look into how to create visuals. Next, we are going to load into Power Query to do some of the transformation required to get through some of the requirements in the report. And we are going to load into Power BI Data Model to set up the data linkage between tables. And at the end of the course, we will come out with a dashboard that looks like this. So, don't get intimidated by these fancy visuals here because we are going to build these together and we are going to have a high level understanding on how Power BI works and we will go into details by details on each of the module. So, don't get too worried because we are going to guide you step by step in each of the module. And yeah, I hope you enjoy this course with me and let's begin our journey. Alright guys, today we are going through the content of the course. First of all, let's kickstart the course by introducing the fundamental of BI. Next, we will get to know how to download Power BI and we will have a brief introduction to BI. We will continue with the rest in this list moving on to the following module. We will be learning how to load data into Power BI and transform the data loaded using Power Query. Dan, we will begin to build our first dashboard and get to know some of the common visuals that are used in Power BI. After creating our first dashboard, we will get to learn how to create new measures. Next, we will learn how to perform sorting in Power BI. Slice and dice is an important feature in BI environment and this will be covered right after we learn how to perform sorting in Power BI. Not to forget that a calendar table is very important as well to give us the flexibility on using time intelligent DAX expressions on our Power BI report. We will also be exposed on how to create buttons for navigation and lastly, we will get to know the usage of bookmarks in Power BI. I hope you find this content interesting and I'll see you in the next module. Thanks and bye bye. Alright guys, welcome back to this learning module. So, the complete terminology of BI is actually business intelligence. What is business intelligence all about? First of all, we are dealing with business. Any kind of business either it is profitable or non-profitable organizations. Next, how do we relate intelligence with business? First of all, we are going to collect all kinds of data that is available in the business and we analyze the data provided to showcase some of the possible actions or decision making to improve the performance or revenue of the business. And yep, this is the general idea of what business intelligence is all about. So, our job is to extract our client's data either from database or other structured file format tables. In this course, we will be using Microsoft Excel as our data files. Once we have extracted all the related data files we need to transform this table into a generalized table format so that we can analyze the data and power BI visual objects. Some client might have their requirements on the dashboard but if your client is new towards BI you might need to recommend some of the visual objects that can showcase insects or visual objects that can help your client in making decisions to improve their revenue. That's all on the introduction of BI. Thanks for watching this video and I can't wait to see you in the next module. Thanks and bye-bye. Hello and welcome back to this learning module. So today we're going to talk about how do we install our Power BI Desktop. So to get started with Power BI click on the link in this slide here which will be attached in the downloadable section. So once you have entered this page here click on products then we select Power BI Desktop and once we are in click on the yellow download free button here and a window will pop out. Select open with Microsoft Store Once Microsoft Store is open click on the download button So for me it's showing launch because I have installed Power BI Desktop in my PC then your download should begin shortly and that's all on the installation of Power BI and I'll see you in the next module. Thanks and bye-bye. Alright guys, welcome back to this learning module on how to load data with Power BI. So in the top panel let's go to home tab let's select on gate data alright let's click on this gate data drop-down here and we gonna select on Excel here alright because our data source is in Excel file alright let's click on the Excel file here and let's go to the file path where you have stored the data source alright now let's click on open so now we can see there is two sheets here let's load in sheet tool here alright click on sheet tool and we're going to click on the load button alright now we can see at this preview window here our haters has been labored as the table columns okay so this is very good this is what we want and this looks perfect alright so let's load our data click on the load button here alright once we hit on the load button we are back to our Power BI desktop layout alright now let's hit on the refresh again to load in our data because just now it's just loading a connection now we're going to load in the data into Power BI alright so we can check the data that we have loaded in by clicking on this data at the left panel here okay let's go to sheet tool here and yup this is all the table that has been loaded into Power BI right here we can see there is duplicates here let's go to the table and right mouse click on it and we delete the whole table here alright let's just remove the whole table here and we can see our table that has been loaded in now for this calendar table here don't worry about this we are going to guide you step by step on how to create calendar table and we are going to learn about what is the usage of a calendar table alright so that's all for today thanks for watching this video and I can't wait to see you in the next module thanks and bye bye alright guys and we will come back to this learning module on how to create our first dashboard today we are going to cover 3 kinds of visuals in Power BI dashboard okay we are going to talk about how to create slices how to create cards and also how to create a donut chart alright let's begin by creating a slicer alright let's go to this visualization tab and let's go to slicer let's click on it and we can see there is a visual that has been created at our report here alright let's go to the fields at the very right toolbox navigator let's go to sheet and we are going to check on the product and we can see that our slicer has been added into our page here alright so let's resize our slicer a little smaller now the next visual we are going to create is a card visual okay so now in the visualization toolbox here go to the card object and click on it okay sorry let's undo this by selecting control and z button we are going to click on the page first okay because right now we are actually selecting on the slicer option here okay and if we click on the card visual this slicer will be replaced with the card visual okay so let's click on the page here so that we are now referencing on the page okay now let's select on the card let's click on it and a card visual has been added into our page alright now we are going to drag in our sum of total value into our card visual let's check on the value box and we can see our card is filled with the selected measure let's add in some formatting to this KPI box here let's go to the format tab click on the format card here and let's go to format okay next let's place a title our KPI box let's turn on the title and there's a drop down here let's click on it now we can add in the title text right here let's add in total sales alright next let's move our text title to the center okay let's align it to the center alright let's collapse the title drop down and let's go to our background now we can also change the background color let's select blue color alright so our background color has been changed we can add in the border color as well okay let's switch our border on and let's go to the drop down here for the color let's select dark blue color alright now let's click on the page here and we can see it has been added into the KPI box okay now for this KPI box here our title needs to be a little larger because this is the grand total for our sales alright so let's have this KPI box a little bit more bigger let's click on this KPI box here and let's go to formatting let's go to our title and we are going to increase our title with a little more extra font size okay so this looks good here we don't need to measure label here okay because we already have a title so we can turn off our category label here alright let's turn it off and yup this looks good alright now for our donut chart we can add in our products here and then we can go by our sum of value okay so this we'll be reading some of our sales by product okay let's place in right beside the total sales card box let's make this a little bit bigger let's move this over okay for the KPI box we also can make it bigger but we don't need to make it too big we can get a view that is comfortable for our eyes let's just give this a little bit more space on top okay let's give this a little bit margin here alright and yup this looks great let's give the donut chart a little bit of margin alright so now we can see if we didn't select on our products and this will actually give us the sum of our total sales by all of our products and yup that's all for the lesson for today thanks for watching this video and I can't wait to see you in the next module thanks and bye bye alright guys welcome back to this learning module today we're gonna learn how do we create a table object and a bar visual okay let's go to the visualization panel and we click on this table object alright now we can see a table has been added into our page here now let's expand this a little larger okay let's make this a little larger here okay just like this let's go to sheet tool here let's hit on the drop down here okay now let's check on the product here to add the product as a dimension into our table visual let's check on this next let's check on our value check box here alright let's check on this and yup we have the table created on our page alright we can also add the date column into the table let's check on this and we are going to move the date to the very top as the first dimension alright so yup this is our table alright next we can format our table and we are going to add in a title for our table alright let's enable the title and let's expand the options and we are going to enter our title text here alright let's add in table details here alright and yup our title is up alright now if we don't want to have the total at the bottom of the table we don't want the value within the table to be aggregated together what we can do is we can go to the fields here and we click on this drop down here alright let's select don't summarize alright this will actually return a table without the total at the bottom of the table alright so this is our table next we are going to create our bar chart alright let's create a cluster column chart alright let's click on it I'm going to add in our dates into our visual and we are going to add in the sum of value as the measure to our visual alright so this will give us the total sales by date alright so let's explain this a little bit now we can see our bar visual with order month and year within the visual alright let's name our visual with a proper title alright let's call this as our sales trend alright and yeah we have created our first dashboard alright and yeah that's all for the lesson today thanks for watching this video and I can't wait to see you in the next module thanks and bye bye alright guys before moving on to the next topic let's test ourselves with what we have learnt until now alright so in this discount sheet here we have a new column called as discount here alright try to load this new column which is the discount column and try to display the new column into table visuals like this okay so this is a very straight forward challenge and I'm sure you can do it so try it out before moving on to the solution alright alright guys let's begin our challenge alright let's start off by clicking on the transform data here alright so go to the home tab here and go to transform data alright let's move to the source and we're going to locate our discount sheet alright so let's go to navigation and this source here let's change it to discount alright now let's enter and now we have our discount column here alright next let's check on the third step here alright okay this looks good here because our haters has been actually promoted from the first row to the column names alright so this is good here let's close and apply let's try to check if our discount column has been added into the sheet table alright let's click on this expand here and we can see that our discount column has actually added into our table so what we do next is we're going to click on this table here and we can see there's a check box here check and this indicates that which of the columns has been actually added into the table alright now let's check on discount column here and yeah our discount column has been added into the table alright so yeah that's all about the challenge today and challenge complete hello and welcome back to this learning module today we're going to introduce you how to create measures in Power BI so what are measures measures are used when we are computing an aggregated value either it is a sum it is an average or a divide and so on in the next demo we're going to show you the difference between sum and sum X functions so as we can see in this table both measures are returning different values if we look carefully on the values in each of the rows they are actually returning the same values just that when it is aggregating to calculate the grand total for the table the sum X function seems to return a value that is more logical if we compare with the sum function this is interesting right just a little difference in the function and the result are way different so let's find out the answer in the next demo alright guys welcome back to this learning module in this demo we're going to demonstrate the difference between the sum and sum X function let's investigate the difference in Excel basically using a sum function we'll sum up all the values in the column then multiply with the sum of the total discount alright let's replicate this logic and we're going to add up which will give us 3411 alright next let's multiply with our sum of the discount column alright now let's add in 1 minus here because we are interested in the percentage after deducting the discount given to our customer alright let's do this and the result return in a negative value let's replicate the same logic using the sum X function for the sum X function this will iterate for every single line so that the measure will capture the value that has been multiplied with the balance of the discount in percentage for each of the line let's try it out we're going to get the value and multiply with 1 minus the discount column alright let's hit on enter and we're going to drag this downwards alright lastly the sum X logic will sum up all these values here after the iteration has completed to compute the grand total in the column so using the sum X function is what we want to iterate the sum logic for every single line so that our grand total is returning the correct value and yep that's all for the logic explanation and we're going to replicate this logic in Power BI that stops measures thanks for watching this video and I can't wait to see you in the next module thanks and bye bye alright guys welcome back to this learning module now let's continue to create our sum and sum X measures before we begin the demo let's click on our sheet tool table here and let's create a new measure let's try to sum our discount column let's call this total discount here sum of our discount column let's try a simple one and see if it work alright now let's drag this total discount into our table let's click on the table and we click on our total discount column alright, something is not right let's check on the error alright let's click on see details and it says the sum the function sum not work with values of type string alright, let's try to remove our total discount column and we notice that the value of the discount column is actually placed on the left alright, this is something weird take a look at the value for this column value here is actually placed on the right so for the column which has value placed on the right is actually as a numbers column alright, so our discount column is written as a tax data tag so we need to update our data type for our discount column here alright so let's click on transform data and we go to our discount column here let's right click on the header and we click on change type here let's change it to decimal number alright so we can see here this is actually in decimal and for our value column this is actually in number formatting for our product is a tax column alright and our date is in date format let's click on close and apply and we notice that our discount column has been placed to the right in the table now let's try to drag our total discount column into the table and yup, it works alright, so yup, so today we have actually learned that for column that is placed on the left is actually a tax column and the column that is placed on the right is actually a numbers format column alright and we did not take note of all this little details here because if we did not take note of this when we are creating our measures we will run into similar issue alright so that's all for today and in the next module we are going straight into creating the sum and sum x measures alright, thanks and bye bye alright guys we'll come back to this learning module so let's start off by removing our total this column alright so we are not going to require this total discount here just make sure that we have already click on the table here and we uncheck this total discount here alright, let's create a new measure let's start off by creating our sum value measure i'm going to put an equal sign here and we're going to call our sum function out and we put in value and then we close our brackets and we add in a multiply we're going to multiply with our sum of discount alright, let's close our parenthesis here and remember that we actually want to get the percentage after deducting our discount so we're going to add in our 1 minus infront of the sum of discount now let's hit on enter and we place our sum of value inside our table alright let's just check on this and we get our sum of value here alright now we're going to we're going to create a new measure and we call this as the sumx value now we're going to use this sumx function here, just type in the word sum, then the sumx function will be shown now we just type in the word sumx and then we can see the axis is referring to a table so the first parameter is a table we're going to put in our table let's put in sheet 2 and then the expression that we want to evaluate is our sheet 2 value sheet 2 value and multiply by our discount so for the discount column here and then make sure we close our parenthesis well and remember to add in our 1 minus in front of our discount column hit on enter and now we're going to bring in our sumx value into our table let's make this bar chart a little bit smaller so that we can have a complete view of our measures inside our table everything looks similar except for the total here so this is the difference between using the sumx and the sum function here if you want to iterate the sum for each of the line please remember to use sumx here refers to iteration and that's all for today thanks and bye bye hello and welcome to this learning module today we're going to learn about how to perform sorting in power BI sorting in power BI is very straightforward just click on the 3 dots on the top right corner of the table and we click on the sorting order that we want easy right however what if we want to have a custom sort for our table let's assume we want to sort our products column in a specific order we want to have B as the product to be shown in the table followed by A B and lastly C alright this will be a little tricky but if you follow along with this module you will get it right okay? step 1 we need to create a table to store our sort order alright so this is our new table our B as the first priority in the sort order followed by A and then and lastly C alright and then we will name this table as the product sort here step 2 we go to the data model viewer and we create link from this product table to our sheet to table we will drag the product column in the product sort table to the product column in sheet to table once we are done let's change the filter direction by clicking on this arrow button here just double click on this arrow button and we will see this edit relationship window open up we will change the cross filter to both alright? step 3 let's go back to our report under fields to box here let's go to sheet 12 and we create right mouse click and we create a new column and we name it as product sort alright? next we going to insert the related function into the column this is to create the column with all the source values from the product sort table alright? step 4 let's go to sheet 2 and we click on the product column alright? at the top here click on sort by column and then we select the product sort that we have created using the related function alright? and the final step is we go to the sales details table and we click on the 3 dots on the top right corner by product and yup we get our table sorted in the specific order alright? that's all for now let's hop into the demo hey guys welcome back to this learning module today we gonna demonstrate on how to perform sorting based on the sorting priority or based on a custom sort with a specific sorting order alright? for now we can see the product column is glowing this is because our table is sorted according to the product column let's go to the 3 dots button at the top right corner let's click on it and now we can see there is options to sort in ascending order or to sort in descending order this sorting is based on product column let's click on product to remove the sorting let's click on this 3 dots again and the table is not sorted with any of the columns here alright? let's click on product again go back to the 3 dots button and let's go to the sort by and we can see our product is in bold which means the table is currently sorted by the product column alright? let's go to sort by ascending here now we can see the order has been changed to ascending order let's go ahead and click on sort by descending now we can see our order has been changed from the ascending order to the descending alphabetical order alright? now we can see the change from the ascending order to the descending alphabetical order alright? this is how we can sort our table in a very straightforward manner if you want to have a specific sort order step 1 we need to create a custom sort table let's go to our format here and let's click on enter data now let's rename our table name as product sort next we going to name the first column as product and the second column as sorting for A we want to sort it as the second in order and for B this will be the first priority to be sorted in the table and let's enter into a new line by hitting the down arrow keyboard button and we will have a new line right after the line before so for product C it will be the last to be sorted let's put in 4 because we only have 4 products in this sample data and product D will be 3 let's click on the load button now we can see our product sort table has been loaded into power bi report next let's go to data model viewer go to the left panel and the last one will be the model let's click on it and now if you don't have this linkage let me show you step by step on how to get this linkage alright let's remove this linkage i will click on delete and if you want to have a proper linkage just drag our product and drop it on top of our sheet 2 product column here drop it right on top and our linkage has been built now to change our filtering direction let's click on this arrow button here let's double click on it and we can see the edit relationship table has been shown so for this cross filter direction let's change it to both direction let's click on ok now we can see the arrow has been changed to multi direction filtering method let's go back to our report we are going to add in our product sort column into our sheet 2 table here so let's right mouse click on the sheet 2 table and we are going to create a new column here let's create a new column and we are going to name this column as product sorry, this should be product sort let's add in the function related ok and this will be our product sorting let's enter and close this parenthesis hit on enter and we have created our product sort column in our sheet 2 table here next we click on this product and we go to our columns tool click on sort by column and we are going to sort by our product sort column let's click on this 3 dots button and let's sort by ascending now we can see the first in the sorting priority is B then followed by A followed by D and lastly product C if we want to have a reverse order we can just click on our sort by descending here the order of the sorting will be reverse and if we go by product C D and then followed by A and lastly product B and yep that's all about sorting in Power BI thanks for watching this video and I can't wait to see you in the next module bye bye hello and welcome back to this learning module today we're going to know why do we need a calendar table in our Power BI report a calendar table enables us to extend our report into periodic categorization such as the year quarter calendar table is very important when we have measures that requires to compute the two date formulas such as the month to date quarter to date and year to date to create a calendar table let's go to the table tools at the top panel and we select new table next we name the table as calendar and we insert the calendar auto function this function here will help us to create the calendar table automatically which will provide us the flexibility to extend our report into year and month hierarchy okay do note that in order to apply time intelligence DAX functions we need to have a calendar table the DAX functions which is the data analysis expressions is a very powerful library to build our measure that best fit our requirements we will go into DAX right after this demo about creating a new calendar table that's all for today thanks and bye bye alright guys welcome back to this demo today we gonna show you level range DAX to create measures that best fit our requirements alright let's try to create 2 measures one will be the month to date measure and the other will be the year to date measure alright so before we begin the demo today let's go to our data model to check if our linkage has been properly connected so in order to use DAX time intelligence functions we need to have a calendar table and the calendar table has to be connected with our fact table alright so this is well connected if your tables are not connected please drag the calendar date column on top of the sheet to date column alright drag it on top and it will be link together now let's double click on this arrow and we change this cross filter direction from single to both let's click on ok and we will go back to our Power BI report alright now let's try to create a slicer click on the slicer at the visualization panel and let's go to our calendar table and click on the date drop down again let's go to our month and we select on our month column here so we will have our month slicer here now let's resize this a little so that we can have our month slicer in our screen alright let's resize the donut chart a little smaller let's have this KPI cut a little smaller and let's drag the month slicer on top right beside product slicer alright this looks good we are cool here now let's go to our sales details table and we go to the sum value measure let's deselect this measure because in this tutorial we are not going to use the sum and sum x value and also our discount column let's deselect our discount column alright next we are going to put on our sheet table here and let's select on new measure alright now first of all we are going to create our sales month to date measure so we are going to insert the total month to date function and this is a dex expression let's take a look at the expression here we are required to insert an expression here let's go to sum value okay next we are required to fill in a date column alright let's add in our calendar date here and let's close our parenthesis here let's hit on enter and our measure has been created now we are going to add this measure into our table let's check on this sales month to date box and we will have our sales month to date measure in our table alright now let's try to select on October and let's see the result of the measure alright now it's returning the current month of October sales right let's create one more measure to showcase the difference between month to date and year to date logic alright let's click on new measure now we are going to add in the sales year to date we are going to add in the dex total year to date function let's add in sum of value and let's close the parenthesis insert the comma and we fill in the calendar date column alright let's close our parenthesis again let's hit on enter and now let's hit on our sales detail table let's click on the sales year to date checkbox and yup basically a month to date will show us the current month sales and our year to date will be an accumulation starting from January until October let's click on September and the results will be accumulated until September for our year to date sales alright for month to date the table will show us the month to date sales on September alright so dex is a very powerful expression builder with this simple dex function we have created two different set of periodic logical expressions alright this is very cool right and yup that's all for the lesson today thanks for watching this video and i can't wait to see you in the next module thanks and bye bye hello and welcome back to this learning module now let's talk about the chart interactions in Power BI and by default all slices is set to be able to filter across all visuals within a page so for example let's select on B and all our charts will be filtered to the results only for product B alright however what if we want a specific visual to not react regardless of which value that is selected for example for this card visual we do not want the month slicer or this product slicer to affect the value in the card because we want this to be the grand total of sales throughout the whole year right so we need to set our slicer to not react with this card visual alright now let's click on the slicer and let's go to formats alright now we can see there's an edit interactions under formatting here let's click on it and now we can see there's a cancer like icon appears at each of the charts so let's click on the icon for the card visual let's click on this and now we can see that even if we have selected the slicer for product C our card visual is not affected alright let's try again for our month slicer here let's click on October this card is still reflecting the value for October alright let's click on this this none icon here and we can see now this card is not affected by any of the slicer here alright so this is a very cool feature we don't want the visual to actually have any reaction when we click on the slicer alright so this is an example that we quite often use when we create a KPI card when we don't want our slicer to actually affect our results in the KPI card alright and that's all for today thanks and bye bye hello and welcome back to this learning module today we are going to learn how do we create a button in this landing page here so we are gonna create a button then we have to navigate from this landing page to our main page okay so let's begin so on this insert tab here we can see on the elements there is a buttons here alright let's click on this buttons and we select on right arrow okay let's check on this let's select this and now let's expand this a little bit bigger now let's enable this button text here so let's click on this button text arrow button here and we can insert our button text here let's insert main page here so if we want our button text to actually align to the left click on this horizontal alignment we can change this horizontal alignment to left now our buttons is overlapping with our button text so what we going to do is we going to go to the icon here okay let's click on right yep this looks good so if for our main our button text here let's give it an extra font here oops this looks too big let's give it 20 maybe 22? ya 22 looks nice right so now what we going to do is we going to add in our action to our button let's click on action here alright now let's click on this drop down here and we going to select page navigation alright and for this destination we going to select main page alright let's make this a bit smaller ya this looks great here and now let's click on control and click and let's see we can go to our main page and ya after clicking on the button it leads us to the main page so after we publish this report here we doesn't need to actually use this control and click we can straight away click on this button here to bring us to the next page so the reason why we need to use control and click here because when we click on this this actually opens up the properties within the buttons alright so if we want to navigate to the next page and we want the action to actually works we need to click on control and click on the button alright and ya that's all for today so thanks and bye bye alright guys welcome to this learning module today we going to learn how to create bookmarks bookmarks are very useful when we want to save all current selections that we have selected so that we can return to this current view with all the selections selected subsequently we can use bookmarks to clear all selections to return to the original view of the page let's go to the show pane at the top and then we click on bookmarks step 2 we can see the bookmarks 2 box has been added into our write 2 bar step 3 we going to remove all the selections in the dashboard so let's remove the selection for month let's remove the selection for products alright let's create a bookmarks 2 bar let's select on add now let's name our newly created bookmarks as clear all now for the last step let's create a button alright so let's go to insert and then we select on buttons let's select bookmarks alright so now we can see button has been added into our page let's move it downwards to here let's give this a bigger size let's go to format button and then our button text here let's turn this on and we going to put in clear all let's close this button text here and let's go to action and this already has been chosen to bookmarks so this is good here and for our bookmark we going to select clear all here alright now let's try to select some of the slices here okay now assume that we have a lot of selections on our page let's say we have more than 5 selections and it's going to be a very tedious job to actually deselect all of the selections alright with clear all button here we can actually click on this button here and everything goes back to our original page alright so this clear all is actually a very important button for user to easily go back to the normal view of the page alright that's all for today alright guys we have reached the end of our course here so congratulations you have did a very wonderful job there for being patient and reach the end of the course with me together alright so before we go apart i would like to thank you for enrolling into this course and i really appreciate for you to be together with me in this course so hope you can apply some of the techniques that i have showed you throughout the course and also how to create bookmarks and buttons i hope you learn something about this course and with your outstanding creativity i am sure you can build a more fascinating power BI report than me lastly please excuse my pronunciation i know it's a bit off and i will do better from time to time alright and yeah so thanks again for enrolling into this course and i hope to see you in the next courses very soon okay so yeah thanks and all the best to all of you