Transcripts
1. Introduction to the Course: Hello everyone, this is sad, and I welcome you
to the training of the most essential and
powerful tool nowadays, and that is Power BI. So in this training we will consider a practical
project with the help of which we will create an amazing
dashboard like this. We will see how you can
handle the large amount of data and convert it into
something meaningful. And also we will see how you can make
relationship between two Excel files in a
single Power BI dashboard. So by the end of the course, not only you will
learn the Power BI, but you can add this
skill in your resume as a project completed
in Power BI set up. With this said, let's get
started with this course.
2. Downloading and Installing Power BI: To get started with the course, we need to download the
Microsoft Power BI. For that, I'll go to Google and right here, Power Desktop. Download here, if you
see the first link is Download Desktop
from Official. You can click on any link. Basically, you can download it directly from here or you can
go to the product center. I'll download it
directly from here. And here it says
Power Desktop set up. Just check which Windows
version are you using. If it is 64 bit, just click on this second
option and click on Download. Choose the location
and click on Save. It will take some
time to download. Just sit back and relax. And by the way, before
downloading the software, just make sure that
you've downloaded the correct version that is
32 bit or 64 bit checking. That is very easy. Just go to the Start menu. I'm using Windows 11 here, right here, PC and
click on View. Your PC name here
in the system type, it says 64 bit of operating system. Let
me just highlight this. You can check yours
depending on your Windows. If you're using some other Windows version
like Windows ten, you will have the
option of this PC. You can write this PC in the
Start Menu and you will find that option just right click on that and
click on Properties. And you will be able
to come on the screen. Right now, this is
for Windows 11, but you can check for relevant
version of your Windows. Most of the PCs use
64 bit nowadays, so check your relevant version. Now we are waiting for it to download and then we
will move forward. Now after the
completion of download, I'll click on this show in folder option and just double click to start the
set up. Click on Run. Now the installation
is pretty simple. It will ask you to
select the language, select English, click next, click yes, and click next. I accept and click next. And it will ask you
for the location, Just keep it default. Click next, and finally,
click on Install. Now again, it will
take some time. I'll just skip the
part where it installs software and I'll show you the end part when the
installation is complete. Now the installation is
complete and it says Launch Microsoft Power I. I can keep it checked and
click on Finish. And here is the main screen. You can see this window
on the starting. You can just close this, and then we have this
clean interface. And from the next video, we will learn more about
Microsoft Power PI.
3. Introduction of Power BI Interface : Once you have successfully
downloaded and installed the Power BA in
your laptop or in your PC, Once you close everything, whenever you want
to re, access it, you will have this desktop
shortcut for the Power BA. Let's run this
double click to run. You will see this
welcome screen. You can see a couple of options here and a
couple of guides that you can check out. Otherwise, just close this and you will have
this blank canvas. The Power BI is used to build some amazing visuals or
dashboards that can snapshot accompany performance
in one single page based on a long, a lengthy data. You can convert it into amazing visuals which
can be easily readable. It all depends on
the data you import in the power a and then you
can work on the visuals. On the right hand side, you can see all the powerful
visual options that we have. In the power, you can
build different bar chart, clustered charts, line charts, pie chart, donor charts. We can also insert some maps, we insert some
performance chart, we can add some slicers. And more amazing functions are here that we will
explore in the starting. It looks like Axil, but it has much more
advanced functions. When it comes to the reports
or charts on the center, it says you can add
data to your report. How you can add data? I can import data from Il, and that is the most
popular option. In this particular course, we will do the same. I will provide you with this all files that we will
use for our project. We will use all of this data, which has a lot of lines, and convert it into
amazing visuals. Will also see how
to connect both of these files and use it
in our power BA setup. On the right hand side,
we have three views here. One is report view, where
the final reports comes. For example, you have inserted some different charts and you've customized that beautifully
on a single dashboard, that is the report view. The table view is whenever
you import the data, all the data will appear
in Excel style table. In the table view,
basically this is the data which the report
view or charts will use. We will also see the Model View, where if you have
different Excel sheets in a single file or even if you have different
Excel files, you can make a relationship
just like Microsoft Access. If you have used
Microsoft Access before, you already know
what I mean to say. And you can just build
that relationship with the common columns
that both of them has. If you understand
the three views, that is great, but don't worry. Once we import the data and
get started with the working, you will get to know about
all of these options. It's pretty self explanatory
when we start the training. Another important part in the interface is
about the queries. As you can see, the section name here on the desktop screen is here we will see
the refreshed data. If you have added
some more data in your main il file
and you want to refresh your data
or your charts, you can use this refresh option. If you want to perform
some operations or work on your data in detail, then you can use this
option of transformed data, basically to work on your data. But we will learn about
these ones later, basically. For now, just understand
that to process data, we will use the Uri
options later on. This is just a
brief introduction about the interface of power BA. In the next video, I'll explain to you the
project and we will get started with our
power PA project.
4. How to Import Data in Power BI : Now before starting
this project, let me give you a
brief introduction about what the customer wants. So this is a project which is handed over to us
by Madewell Store. And the owner of
Madeviell Store wants us to create a dashboard, can track and analyze
their online sales. They want to track the
reports on Power BI. Let's analyze their data and what sheets they have
provided on Excel to us. These are two sheets
provided by the customer. The store is based in US. And against every order ID
that customer orders online, there's a certain order date and it's ordered by
these customers. They live in these
states and these cities. Now if we close this sheet
and open the other one here, we have the amount of the order against that order ID and
the profit that we will gain from that product sales and the quantity in that particular order
which we're selling, the category of that product, the subcategory and
the payment mode which is made by the customer. Either it's cash on delivery and EMI
credit card, whatever. Here in certain areas you
will see a loss as well. Maybe this is a clearance sale. Some products are
not selling good, so they are just trying
to clear that stock. So they're selling
below the cost price. So that's why the
values are in minus, this is all of our import
data on which we will import the data in power PA and we will
create some reports. So let's go to power
to import the data. I can see import
data from Cel here, but we have a CSV file, so I'll click on Gait data
and select the CSV format. Just go to the location, and here is our files. I'll import it one by one. I'll select Orders
First, click Open. These are all the columns. This is just showing
a preview of it. Let's load the data. And
it will take some time. Depending on the data you have, it will load the
data and you will instantly see this
message change to build visuals with your data. Now that we have data, you can build some charts on it. Where is the data
on the report view? We can only see charts that
we will learn how to insert. But where is the data? Data will be shown
in the table view. Let's click on Table View. And here is the all
data that we import it. Also, if you see on the
right hand side before the data field or
data pane was empty, now it's loaded with orders. That is the sheet name
on that Excel file. If we browse it through, these are the column
names that we have taken the data from Excel. Now again, let's go back to the CSV and import the other
files. And that is details. It will again take some time. Show you the preview
and let's load the data after confirmation
that the data is just fine. And if we go to the table view, we can see these two data. On the right hand side, we have the details and
we have the orders. So now the data is loaded. In the next step, we will see
how to transform your data.
5. Transform Data in Power BI: Let's go to our next step, and there is transformed Data. On the Home tab, you can see this option of transformed data. We will simply click on
this option, click here, and it will load in a new
window called the Power Editor. Here you will see almost the
same interface as of Xcel. You can basically
customize the columns. You can make any
changes to the data. For example, if
you want to change the format settings or you
want to add a new column, you want to add new column, you want to remove the columns, you want to sort it, or you want to enter
some new data. You can do all of
these things here. Whatever changes we make
in the data will appear on the right hand side
in the applied steps. Whatever you do, even if
you change the formatting, you delete a row or you
insert a new column. All of these steps will be
shown in the applied steps. Now let's say if I want to make a specific changes to
a particular column, let's say I have a column, but it's a number and it's accidentally showing
in the date format. Right now it's already
a date format, but let's say that it is a number and it's showing
in the wrong format. I can just right click and change the type of the
data to a decimal number, to a fixed decimal number, to a percentage to time, date, duration, text, whatever. Similarly, we have many
more options here. You can rename the column title. You can move it to the left, to the right, to the beginning. You can just drill down pivot
columns on pivot columns. All of these options are here. We can remove the duplicate,
remove some errors. Let's practically see
one example of it. Let's say if I want to create a duplicate of this
particular column, I'll just right click and
click on Duplicate Column. Now whatever we do on this Uri will be shown in
the applied step that we have
duplicated the column. And whatever column we have
duplicated will appear on the last of the data,
in the last column. Right now, this is
just an example, we don't want this column.
Let's remove this. It will again show it in the applied steps that
the column is removed. Whenever we delete something
from the applied steps, it will be reapplied again. This is how it works. Now here we have two sheets right now. One is orders, one is Details. As soon as I click on Details, I'll go on my other sheet here. Let's see if I want to
insert a new column. I'll just select one of the columns and
click on Add Column. Let's click on this custom
column. Click on that. Now I need to specify
the column name. Now let's, let's name
it as Test Column. Now in the column, you can specify a
formula which can be related to the
available columns. For example, if you want
to create a column that is a product of amount
multiply quantity, that means price multiply by quantity equal to total sales, Let's say I can specify
this formula here, and it will automatically create a new column based
on that calculation. Let's write amount here. I'll choose the amount column, multiply by quantity, and this should show us the
new calculation. Let's click Okay. And it will be inserted
as a test column, which is showing the product
of both the columns. This is how you can edit or create some new columns
in the power Q editor. Now again, there are two methods if you want to
delete this column. One method is I'll
just simply right lick and select remove from
here or otherwise. It's also showing in
the applied steps that a new column has been added. Let's untick this, and this
column will be removed. And similarly, just like Cel, if you want to sort a particular data,
let's say this column. Let's check the options
of this column. The filter is already applied, and I can sort it in ascending, descending order to
remove empty spieces. There are some text
filters because this column is
considered as a text. Why? Because it contains both
the combination of numbers, special characters,
and the alphabets. This is basically
considered as a text. All the text filters
are shown here, and if I want to select a
particular order number, we have all the
options to do that. Just like Excel, this
options are here. Let's click Okay. Now
in the Power Curie, we use the feature of
group by many times. Let me just quickly
explain you that as well. I'll just select one column
and go to the Home tab. And let's click on Group By
over here. Let's click that. Now there are two
methods to group. One is basic, one is advanced. First of all, let's
try the basic one. It will ask you to specify the column to group
by in desired output. We can group by anything, order, ID, amount, quantity. But make sure that the group
items should be repeated. That's why it's called a group. It shouldn't be unique
for all the roles. Otherwise it will create a unique group for
each single line. Right now, the category is repeating itself
again and again. That is the perfect
candidate for the group, group by category. What will be the
new column name? Let's say category group. Now the operation I
want to perform is, let's say I want to sum it, and I want for, let's say the amount. Now it will show
for each category, the sum of the totals for
each category of sales. Let's click Okay. And we can see some awesome
report here that electronics the total of
the sales is this amount. The furniture category
sold this amount, and the clothing
has this amount of. So don't want this if you just checked the report and now you want to get rid of this. In the applied steps, I can see this group just
uncheck that or cancel that. You will get back
to our main sheet. Let's now uncheck, Let's select any random sell or just
select nothing at all. Let's go to Group
and Group By again. And this time I'll select
Advanced option here. We can actually group
multiple columns and we can perform multiple
operations on that groups. Now let's say I want to
group it according to this category and I want to group the
subcategories as well. Now we can perform multiple
operations on that. We can count, we can sum, let's say I want the
totals of sales. Let's say we can
give it any name. I want to sum all of these. Let's sum the amount. We can add the aggregation. Now let's this time name it, average count, or average
quantity, whatever. This time I want to
average the quantity sold. This is almost like the basic function that
we've just seen, but the advanced level gives you more options
that you can select. Multiple categories or groups, and we can perform multiple operations on that single data.
Let's click Okay. This time I can see the
category and the subcategory, wise totals and the
average quantity. It's giving the total of the category and
under this category, electronic games
makes the total of this sale and the average
quantity sold is this. Same thing goes for furniture under the furniture category. Cheers, totals are under
the furniture category. Bookcases sales are these. These are very useful reports when you're analyzing your data. Now again, this was a sample
data and I don't want this. Now let's get rid
of this by pressing this cancel sign on the applied steps and we are
back on our original data. Now just remember
that if I want to move back to my power BA, set up and exit from
this power editor And let's say we have made some changes and we
want to save this. Just remember before closing, I'll just not simply
close from here. Instead I'll go to Home Tab. Go to Home Tab and click
on Close and Apply. And actually see the drop
down I can close and apply. That means it will
close the file and apply all the changes
that we've made so far. That means it will transfer all the changes applied to
the power BA set up as well. Simply means that
it will exit from the power editor and go
back to the power BA setup, but it will not save
all the changes. Make sure before exiting
you hit close and apply, or apply before you exit, especially if you have
made many changes. I'll click on Close and Apply. Now you can see that
the data is saved and loaded and we have two
tables on our right. This is basically all about the power Q editor and transform data function
in the power BI.
6. DAX - Data View: Now let's go to our next tab, and that is the data view. To view the data, I'll change from report view
to the table view. On the right hand
side, I can see the imported sheets
here in the data pane, I can just drill down to see all the columns that we have
in that particular sheet. And same thing goes on
for the other sheet. Now if you notice above, we only have the table
tools showing right here. But whenever I
select any column, it will show you some
additional tools and that is related to column, but this will only show when
you make any selection. Even if you don't select
the whole column and you just click on
a particular cell, it will still select
the column and show you the column tools from here. Also, you can make many
changes in the formatting. For example, if I want to
select the quantity and want to view the format style
of that particular data, we can see that as soon as
we selected this column, it will show you under
the column tools that the format is whole number and you can make any
changes if you want to, then you can exit. Same thing goes, if
I go to order ID, it's shown as a text. If I go to amount, it's
shown as a whole number. Let's say if I want it to
be converted in a currency, I can convert it like this, and this will show dollars
with that currency. Now let's try to insert a new
column directly from here, not from the Power Curie editor. Let's say if I want to
insert a new column, I'll just select one column. You don't even need
to select a column. You can just directly go to the Column tools and
click on the new column. It will take some time
and insert a new column. Right now it's showing an error, but it is asking you
to define the column, first of all in the column, if this has some new name here, I'll change the column name. Let's say to new column
Here I'll define the same formula as we
did for the power Uri, and that is a mount, multiply by quantity
and hit Enter, and you will see the results. And you can also see on the right hand side that a
new column is created here. Similarly like this, we can use multiple formulas in order to get some awesome
results in the columns. And that is called Ax functions, which we will not
cover in much detail. For this training, you can
take the support of Google or Chat PT or Youtube For
that additional tips, you can also check out my
profile for the other courses that might have been updated by the time
you're seeing this video. Refer to that one
for the Dex queries. Now similarly, if I want
to get rid of this column, I can right click here
and click on Delete. And click Yes, just
like we do in Il. Now that we have seen some of the features of
the table view. Finally we will go on to our last view, and
that is model view.
7. Data Modeling in Power BI: Now this is one of the most
important things in power BI, and that is building the
relationship in the model view. So this will help you to relate between two sheets that
have different data, but we can interrelate
them with a common key. In this case, it's
the order ID that is present in both of the fields and that is common
in both of the sheets. If we just hover
over to this line, we can see that it
automatically built the relationship and identified the common columns
between the two sheets. Anytime you feel that the
automatic relationship building is not correct and you
want to reset that, you can just right L to
delete that relation or even you can go to the
properties and adjust that. It will show you
the demonstration in a preview of
the relationship. Select the tables and
columns that are related. The first sheet name is mentioned here, details. The other sheet mentioned
here is orders. And this is some of the data preview here
is the cardinality, how it's relating to each other. So most of the time
it will automatically select and make
the relationship. But if it didn't
identify it correctly, you can manually make
the relationship, especially if the heading
names are changed, but it means the same thing. In that case, you can make
the manual relationship and maybe power or power B. I will
not be able to read that. As we can see that the
already the relation is made. And it's highlighted in the
gray color that these are the relationship
order ID in this and order ID in the
other sheet as well. But let's say I want to make a relationship between
different columns. Let's say category, I want to relate it
to a customer name. I'll select both of them
from the cardinality. I'll make that
relationship many to 11 to 11 too many and
many too many. Once I highlighted
both of these, it's showing this message that the relationship has
cardinality many too many. This should only be used
if it's expected that neither column
contains unique values and that is significant. This is a message, as you can see, that the
new relation is made, but the proper
relationship which has some common things between
both of the sheets. In this case, order ID is identified correctly by power BI as a default relationship. Now, what is this many? 1111, too many? Many, too many. What is this cardinality? Let me give you a brief overview just for your understanding. Let's say we have two tables and the order
ID is used repeatedly. The same ID is repeatedly
used 23 times in this table and 23 times in the other sheet table
as well. In that case, we will make the
relationship as many to many because the same order ID or category is used
repeated times. Now let's say that first table
has many duplicate values, but the second table has that particular category
names or let's say order ID, but that is only unique. In that case, you will use this first option, many to one. Now exactly opposite of
this is the first table has only the unique values and the second table has
that exact values, but it is repeated
multiple times. In that case, it will
be one too many. Similarly, if both of that has unique values in both
of the tables every time, that will be one to one. In this case, I'll
keep it many to one. By default, whatever
is suggested, once it is all active, we can make this
relationship act. If it's not checked, we can make it checked. And click okay. Let's click Okay. And
this is how you can make the relationship
between different tables or different sheets. Now finally, the most important
step is coming forward. And that is how we can
use and apply all of this data and relationships in the report view and make
some amazing dashboards. From the next video, we
will start working on that.
8. Power BI Dashboard Creation: To build the dashboards, I need to come on this report view to
create this dashboard. Let's start on the first hand, I want to give a
dashboard heading. How we can give some
headings in the dashboard? To insert the heading,
I'll go to the Home tab. And under the Home Tab, I'll select this text box
here, just like Excel. I'll select here and we can
simply write the text here. And here I'll write
the business name. And that is, let's
make it large. I've written here
the business name and the E Commerce Sales. The size of this box, I can bold reduce the size. I can do that. Similarly, I can change the writing styles
here, many options. Let's say I want
to make it aerial, but for now, the
previous one was good. If you want to move it anywhere, you cannot move it
from the center. You need to hover
over this text box. It will show you the three dots. You can click on
that and remove, and spotlight as well. But this is also used to
move the data around. So I can just hold this, the data towards right or left. Similarly, if you want
to add something, let's say I forgot to
mention the dashboard here. So I need to expand
the text box again. See the complete text. Now I need to reduce
the size as well. Let's make it smaller like this. Now the next question
you might ask is, how will we create charts on this dashboard so it's
very easy to create. You will see this
visualization stab here and it has all the
amazing features of the charts that
we want to make. Now depending on the situations
and the data you have, you can select the suitable
chart relevant to that data. First of all, I'll use this stack column chart to create a chart of my
profits in the business. Let's click on that and it will immediately be
inserted in the dashboard, but that will be a blank chart. I can select and drag the fields directly into that chart
to populate the visual. Now let's say I
want to generate or populate the month wise profit. In the first data I
have the details only, but in the second one
we have the order date. We don't have month here. What it will do if I drag and drop directly
into the chart, it will show you something. But it will show you like this. It will show nothing actually. Because we need a second
parameter as well, that order date is
considered for order date, what do you want to see? Actually, I want
to see the profit, so I'll just drag and drop the profit in the
main chart as well. But that will create
something like this. Even if I see the x axis
and the y axis here, even if I try to move it around, it's not showing anything good. It's only showing 2023. But if I cancel the
year from here, because we only have one
year for the whole data, so we need to cancel
this year field. Now you can see the
visualization is showing the axis of
that particular data. Some of profits are
showing on the Y axis and the order deeds are
showing in the X axis. And it's showing
according to the year, quarter, month, day. I need to specify from where
I don't want to see this, I don't want to see the years. So it will convert
it in the quarters. This looks nice, but if I want to further revise it
according to months, I'll cancel the quarter as well, and now it's showing
the month wise profit. And this is looking nice. Now sometimes it might
not analyze it as a deed and that's why it will not show you this month,
day, year options. If you face this problem, just go to the table view and
select the relevant table. In the details, we
don't have the dates in the orders, we
have the date, right. Just click on that date and make sure that the format
is selected to date. You can make it any date. Like if you want to see
the DDM and why format, you can do that as well. Then it will analyze it by dates and it will give you
all of these options to configure it
by month, by day, by quarters, so whenever
you are unable to filter it through and present the month wise report with
just the order dates. If you're unable to
convert it to the months, just go to the table
view and check the format of that date column. Similar thing can be
seen here as well. If we drill down this, it will show you
the date hierarchy. And you can easily choose
between these that if I want to convert back to quarter now I cannot see the
quarter from here. So once you get rid of this quarter from
the visualizations, you might get confused. How will I add it back if I
want the quarter wise report? So it's very simple. I'll go to the data pane. It might be hidden like this. You need to expand that
under the order date. I can see the date hierarchy. Just drill down again. Let's say if I want
the quarter report, I'll just check this
and uncheck both of the others and this
is the updated result. If I want to make
some more space, I can just hide
the filters area. And then we have the dashboard. You can resize the
dashboard however you want. Now, if I don't want the
sum of profit by quarter, I want the count or average
of it in the y axis. Under the data visualization, you can see the sum of
profit is selected. But when you click
on this dropdown, you can change it to
anything you want. You can identify the
average minimum, maximum count,
whatever you want. This is basically all of it, just looks like Axil. Now, if we change it to months, let's say I want to convert this chart
to any other chart. I don't want the bar charts
or stacked cluster chart, instead I want the line chart. So it's very easy
to do in power Va, just select the chart
and make the changes and it will immediately convert
it to the other chart. And similarly, let's
say I want area, it will turn it like this. You don't need to
change anything. It will immediately
convert it to another type of chart as you select the
chart and make the changes. But in this situation,
stack graph is perfect and I
want the same thing. Let's turn it back on. Now we have created a chart, and it's looking better, but it still doesn't
give an appealing look. For that, we can add a
background to this white space. And how to do that, I'll go to the visualizations and
formate your report page. I'll click on this option. I can make some canvas settings here it says canvas background, let's make it blue. It will turn all the
dashboard in blue, but the transparency
is 100% right now. That's why it's
not showing blue. When I reduce the transparency, it will show the blue screen. But in this case, I don't
want the default colors. Instead, I'll just
insert the image, which gives a good
gradient look. And that is also attached
in the project files. I want this gradient wallpaper. Let's select this and
click open again. You can see the
background image, but it's quite blurry. First of all, make it white. Now let's remove the
transparency altogether. Image fit should not be normal. It should fit. Now it
should show like this. And this is looking good. But I just need to get rid of this white spaces
from the boxes. Now I can make all of these adjustments now
to make it look better. First of all, we will match the charts and the
captions with the background. Right now, it's not matching
with the background, but we'll do it later on. First, we will make
some major adjustments. That is, since I already see that this
is profit by months, I don't need this
additional caption here. I can easily get rid of this. I can just select the chart and go to Data visualizations. Since it is on the x axis, I'll just select
the X axis option. I'll just turn off
the title from here. Same thing goes
for Sum of Profit. I'll just close
this and go to Y X. And again, turn off the titles. It will remove this Sum
of profit and it will create some additional
space for our chart. Similarly, if I want to change the color
of January, March, it's showing in a very
light gray color. I want to make it solid
white or solid black. Let's say I can go to the x axis and change the color
of the text to black. As you can see that it
will make it darker. Now to match the chart
with the background, I can make some changes. I'll go to the General tab. Now here is the General tab. Just click on that. Here
I have many options. I can go to the Effects
under the background. I will change it from color white to let's
say this color, it's not really matching. Let's try, let's say
either color gray or even darker gray,
still not matching. But I'll just increase the transparency and we'll
show you some great results. I just wanted to differentiate a little bit from
the background, that's why I put the gray color increase
a little bit like this. And now it's matching
our background. Now we can also make
this text white. Let's go to the x axis. The color should
be white and bold. Let's say as I increase
the size straight. Months. Instead of this tilted, If I try to reduce
the change to, let's actually increase the size now to make it
more professional. You can also select
some border colors. Let's select some border
colors to color the border. I'll just select the chart again and go to the
Format, Your visuals. And under the Format
Your visuals, click on the General tab. Let's go back to
the effects here. I'll turn on this visual border. Just enable that by default inserted in some
black color or something, I can make the changes and make it something which
matches the background. Let's say it has applied
this background. Let's try another one. Let's say this one
matches our background. Now, one more adjustment in the current graph that I can
see these dotted lines here, and that is called grid lines. If I don't want that, I'll
just select the chart again and go to the
formatiovisuals. And go to grid lines and turn
that off whenever you are finding it hard to go to that particular options
but you know the area, You can just use this
search right here grid and it will show
you that options. You can turn that off.
Now one more thing. That the heading is now
appearing in black, so that's why I'm
unable to read that. And also, the
heading is not very professional that it is showing the sum of
profit by month. I just want to say
profit by month. I'll change the caption
and the text color as well to select the chart and
go to the formato visuals. And after that, I'll go to the general tab under the title. Title is enabled, but text
says sum of profit by month. I'll change it to profit by
month or monthly profits. Let's change it to white color. Now it's showing here I can
centralize this as well. These are the alignment
options at the bottom. Let's actually write
it as profit by month. Profit by month. Enter again. On the y axis, the value is not properly shown up because
of the text color. Again, I'll select the chart and I go to Format You visuals. And under the y axis, I'll go to the values and
change the color to a lighter. Now it's showing the values. Now whenever you feel
that the graph is not showing properly the
results as you want, just go to the home tab
and try to refresh it. And then it will make
the required changes. Now let's try to make
some of the profits in negative so that we can apply some conditional
formatting on it. So in this situation, I will reduce the profits for April and let's
say I'll reduce it for August and for December. Let's open the details file. Let's say I will make
this one as 2750. And let me just press
control S to save this and to update
it on the Power VA. I'll go to the Home tab
and click on Refresh. Now the April is in loss, so it's going down. That's exactly
what we want here. We will expand it a little bit. Similarly, what we can do is I can go to the table view and just check the orders first according to a particular date. 259-02-5950 is for December. So let's go to 259-502-5950
This one is the order here. Let's say 25653 on
13 December 2023. Let's change this one to
negative 6,400 Press control S, and then go to the
charts and refresh. Now it's also showing loss and
let's make one for August. Let me just consider 25967
on 15th, August 2023. So I'll go to the Xl file
and let's make it something like 4,500 minus press
control S and Save. And then go back to the report
view and click Refresh. So now it's also showing. Now what I want to do is I want to show all the
profits in blue color. While all the losses
should be in red color. All the bars in negative
should automatically be converted in red color
via conditional formatting. What I'll do is I'll just select the chart and I'll go
to Format your visuals. And I'll go to the
visuals and the columns. Now I will define the conditional formatting
and I'll change it to rules. And here I will take
the profit area, because I've made my
chart based on profits. I'll simply define here that if the values
are in positive, then highlight in blue color and otherwise highlight
in red color. So I can choose the
red color from here, like this and click Okay. It will highlight all of
the profits in blue color, while the losses will be
highlighted in red color. This is how you can
either make the rules for the data formatting or if you
want to change it manually, all the colors manually, then you can do that as well. Our first chart is now ready, Let me just resize it. And this chart name
is Profit by month. Now one thing you can notice is this chart has sharp corners. I want to convert it into
something like soft corners. What I'll do is just
select this chart and then under formate your
visuals go to general. And you can also write here
borders visual border. If you want to turn it
into rounded corners, you can just drag it
forward to make it round. Now also if you want
to change the color, let me just shift it to a
darker color visual border. Let's say this dark
red, that's good. Now one more thing, that if you have customized
all the chart, you have made some
configurations like borders and deciding the colors, schemes, and et cetera. And you want to create more
or less the same chart. You don't need to create
it over and over again, you can just simply
duplicate it. Press control C and press
control V. There you have the copy of that same chart with the same configuration. Now I can just change
the parameters. For example, if I want
the sales by month, I can just remove profit from
here and drag sales here. Let me just actually show this. So let's cancel this
y axis sum of profit. And let's actually drag from details the amount
field to the y axis. And now we have a new report with the same configuration
and the formatting. For now, we don't need this. This is just an example. Let's delete this.
Now. The next thing I want is the subcategory y graph. For that, I need this
stack bar chart. Let's click on it. And just make sure that before
clicking any chart, no chart should be
selected like this. Why? Because if you click
on a particular chart and you change it to
stack bar chart, it will actually convert that particular chart
into a stack bar chart. Instead, just click anywhere in the blank space and then
click on the stack bar chart. It will create a blank chart. Now I'll just dragon drop the subcategory into
the chart directly. You can also drag it in
the y axis or x axis, but it's more convenient for me to drag directly into the chart. It depends on what
is suitable for you. You can do whatever you want, but I want to see the
subcategory wise, what monetary amount I
want to see the profits. Let's drag and drop the
profits here as well. Now, one more thing
that if we drag down through all
the subcategories, we can see a lot of them here. But let's say I want to
monitor the performance for the top four or top five. What I can do is just
expand the filter pane. Right now it's hidden.
Let's click on it. And make sure the
chart is clicked only then you can apply
the filters on it. Now here you can see the subcategory
filter is set to all. Once we click on it, it can
show you the basic filtering, Either you want to do
advanced filtering, basic filtering, or
top number of items. I'll click on top top,
how many numbers? Top five, by what characteristic
you want it to filter. You will drag the value here. I want the top
categories according to the profit in which I made the
most of the profit I want. According to that, I just drag and drop the
sum of profit here. Now let's just click
on Apply Filter to see our refined results. Now you can see that only
five values are showing here. That's basically how you can apply the filters
to your charts. Now one more thing that if I want to form it, this chart now. It's showing the
wide background, it's showing the sharp edges. It has the subcategory and
extra captions showing here. It will take a lot of time to make it similar
to this chart. So if you want to follow
the same chart formatting throughout all of your charts
and you want to save time, you don't want to spend a lot of time in formatting
each separate chart. After creating the chart, what you can do is just
select the earlier chart and under the Home tab you will find this option, Format Painter. Now this is the same
option that you will find in all the
Microsoft products, including the
Microsoft Powerpoint, and Word, and Excel. Just click on that, it
will copy the formatting. And whenever you want
to paste it somewhere, you can see this brush sign. As soon as I click
on that chart, it will convert it into
the same formatting. As you can see that extra
captions are also removed. The background is also removed. Only what I need
to adjust now is the caption on the top
or the title on the top. Also, if I click anywhere
on the blank space, I can see the corners are
soft and the borders are red. All of the formatting
is copied here. Now, for example, if I want
to further customize it, I want to define each of the
line in separate colors. I can do it in the
further adjustments. Let's click on the chart. I'll go to the visuals
and under the bars. I'll select Show All. I'll change the colors
of each bar one by one. Let's say I want to apply
this colorful chart. I've individually changed
the color for each bar. Now as you can see, the values are showing
in a very small size. If I want to increase that, I'll go to format visual. Since it is on the y axis, I can just turn the Y axis on. Then I can increase the size. Actually it's changing this Xs, these names are changing, these name sizes are changing. If you want to increase this, same thing goes on
for this X Xsow. The next chart I want
to apply here is chart. Now I have the option of pie
chart and donut chart. More or less, both
of them are same, but donut chart looks
more attractive. Let's click anywhere on the blank space and then
insert our donut chart. Now I want to see here category wise orders in how
many categories I receive, how many count of orders. What I'll do is I'll just drag the category here
in the details. I will drag Drop
the quantity here, let's dragon drop the quantity. And there is our chart which is showing the sum
of quantity by category. Let's make the same formatting
for this one as well. Let's copy the format
from the first chart. We'll click Format Inter
and paste it here. Now it looks better, but I need some further customization
because the caption, I cannot see the
caption properly and also will make some
more adjustments. Now first of all, I'll change
the color to a lighter one, especially for this blue one. Whenever you want to customize
that, select the chart. Go to the formato visual, and I'll click on the slices. And under the slice, this one, let's change it to,
let's say green. And let's make the orange
instead of orange. I'll turn it to,
this looks good. Now I'll go to the
detail labels. Here I'll see two options, one is options, one is values. I'll go to the values over here. I can see this caption
is showing in dark gray, but it's not showing clearly. I'll make it white so that
I can easily see that. Or even you can turn
it to light gray, will look more professional, and also it will be readable. Now let's go to options
under the detail labels, you can set the position
of it either we want this details to appear outside the chart or
inside the chart. If you turn it
inside the chart or prefers it will
change how it looks. Let's keep it
outside by default, but the label contents I want is not the data value
and the percentage. Instead I want category
and percentage of total. This is more reasonable, it will make more sense that furniture is 16% of
the total sales. Electronics is 20.55%
and 62% is clothing. Now if I want to round it to the nearest decimal,
how can I do that? Now I'll close this option
area and let's go back to the values under
the display units or actually under the
percentage decimal places. I'll just set it to zero. Now I can see no decimal places in the percentage.
That's how you do it. Now one more thing, that
on the right hand side, you can see this
extra categories that is showing the
color coding for each. But this chart is pretty
self explanatory. I don't need this
explanation or captions. What I can do is I'll just
go to format your visuals. Now this option is
called Legends. Under the visual you
will see this legend. Just turn this off
and you will see your chart will
change to full size. Move the extra
category description. Now again, I'll copy this chart
and paste it on my chart. And now I'll create it
for the payment modes. Now I don't want the
quantity sold by category, instead now I want it to be according to
the payment mode. So I'll just remove
the category from here and just dragon drop
the payment mode. So immediately you can see that the payment mode
chart is created. And it's clearly showing that how much we sold
from credit card, how much is debit card EMI's, and cash on delivery. So this will give
us a better idea on what payment mode
customer prefers. Our four charts is ready. Let's reduce the size of each chart and I'll
create some cards here. Let's actually, I'll just
fast forward the process. Now I have adjusted the size of all the charts so that I can
see this extra space here. You can make the
same adjustments. Now I will add some cards here. I'll go to Data
Visualizations Here you will select this option
which says one to three. These are cards. I'll click on it and make sure
no chart is selected. Now I want to see
the Total Sales, which is customizable
by each category. You will automatically see the purpose of the cards once we Filter the charts
but right now I want the card for
the total seals. Let's drag and drop
the amount here. It's showing that 440 K
is the sum of amount. Let's make the formatting
seam format painter and change the color
of the values. Call out value will be white or let's say dark
or let's say light gray. Now similarly I'll go
to the category value. I'll go to the category label. And let's change the category
label color as well. Let's make it light gray. I'll create the same cards. I'll just adjust the size of it. It will take some
time to adjust. You will make these kind
of adjustments here. After the adjustments are made, just page it where you want to. Now let's make a copy of it. Control C and control
V. This is the copy. Now here I will just close the sum of amount and
drag the profit here. Now let's see if
you want to change this sum of amount
to total sales. Just click on this call out. Let's go in the fields. In the fields, I'll select this option. Let's click on it. And I'll just click on this, rename for this visual. Let's click here. And let's
write sales Revenue here. Hit Enter. Now you can see that the call out
name has changed. Same thing goes on for profit. Let's actually make this second
one the sum of quantity. Let's dragon drop the sum
of quantity in the field. Similarly, I'll just copy and paste this, make the adjustment, and then I'll change sum
of quantity to profit. Now in the fourth card I want
the average order value. Now this field is not
available in the details, neither it's available
in the orders. What I'll do is I'll can make that adjustment and create that new field in
the table view. I'll just go through
the table view. Just click on any column, then you can see
the Column tools and then you can
insert a new column. It will insert the new column. Now the name of
that field column. It will be AOV means
Average Order value. Now I need to divide the amount
to the number of orders. I'll write here amount
in the details and I will divide it
by the quantity. Now I'll just press Enter
or click anywhere outside, it will show you this
average order value. If you see some decimal
places with it, you can just convert
the formatting to the whole numbers from here
and from here as well. So it was asking you this option that you show you want to
change the stereotype. So I continued with it. Now, average order
value is simply what is the average of all the
orders received, for example. Now let's go to the
report view again. And I can see this
new field here. And I'll just drag and
drop the AOV here. And this is my AOV. Now I also want two more charts. That is top customers
and let's say top sales. So let's quickly create
that ones as well. So almost all of
the steps are same, so I'll not explain
to you in detail. I'll just make the copy of this particular
chart, create a copy. Control C and control
V. Let's paste it here. Now I'll just remove the fields, make it blank, drag and
drop the state here. The amount I want
is in the x axis. Now I want the filters, these are multiple states. I can go to the
filters, actually, we will cross the filter and then we can see
all the values. Now I want the state filter, I'll just drop down the state
under the basic filtering, I'll select top and top five
according to the value. I want it according to amount. Actually, let's make it three. Let's apply the filter. This is the result for it. If you want to manually
change the bar colors, you can do that as well. Actually, let's
make it four now. The sales you can notice is almost the same for
all of the states. That by showing
the straight bars, you can formit the bars to whatever color you
want for each state. Now again, for the next chart
we want the top customers. What you can do is
you can just make the copy of that
particular chart, any chart you want, or you
can do it from scratch. Just click anywhere
on the blank screen. And let's make it this
stack column chart I want, just just make it bigger. Now I want the customer
wise top sales, so I'll just dragon
drop the customer name. Now I want the amount here, so just dragon drop the amount. So these are all the
customers that are buying from us on our website. So this is a long list, but I just want
to focus on that. Ten or five top customers. So maybe we will offer them some discounts or,
you know, we can re, target these on our website so that we can generate
more sales from them. So after that, since we can see that these are long
customer names. I will just filter this. Go to the filters and go
to the customer names. Now I want this to, let's say five customers according to the
amount of seals. Let's track the amount
here. Let's click Okay. These are the top
customers and their seals. If I want to format it, copy the format format
paint like this, now it looks better. You can change the first
part color to green. If you want to just
select this and go to the format of visuals
and under the columns, I'll just select this first
has green. This is good. Now I want to
change the color of this Madewell E
Commerce seals as well. Let's just select any call out. Just copy and piece
the format of that. If it doesn't show you
the correct formatting, the color manually the
text color to white. Now if you notice here, I can make some effects in it. I can make this transparent, little blue background
so that it can stand out to make
these adjustments. I'll, I'll select the call
out under the general, I'll select the effects. And let's change this to blue and let's reduce
the transparency. So this is how you
can format this. Let's apply tunes as well. Now this dashboard
is almost ready. One more thing I want
here is filters. So I'll just click anywhere
on the blank screen here, I'll apply this slicer. What this will do is
this will filter all of the charts according to a specific criteria that
I will mention here. So let's say I want the
filter for quarterly basis, so I'll just go to the orders
and under the order date, I'll go to the date hierarchy. And I want the quarters here. Just drag and drop the quarters. It's not looking very nice. So let's just
customize this more. Let's make some
more adjustments. I'll just go to the format
under the slicer settings, I'll change from
verticals to tile. Right now it's
showing like this. But if we expand this and
reduce the size here, it will turn it
into this option. I can turn off the
slicer header also. This will change it to this. Now it's looking very raw. I can quickly format it. Just select this one, Go to the Format Pinter,
and click here. It will change the format. Now it looks better. Now I can reduce the size. Here, this is our filter. Now I've made some
small changes in the formatting so that
it will look more nice. It totally depends
on your format, style, and design ideas. Whatever you want to do now, I want to apply a
Streetwise filter as well. Let's just copy this and
just duplicate this here. I'll change it to state, I'll just remove the
order date from here. First of all, I need to change this style of the
slicer to a dropdown, but I need a category for that. Let's actually remove this
and do it once again. Copy and paste, and firstly change the slicer
settings to a dropdown. Now I'll go to the values. You can also write here values and change it
to the white color. As you can see, it's too dark. Let's make it light now, instead of order dates, I'll go to the Add
your data visual. Remove the fields of date from here and dragon drop
the state here. Now I can filter it
by any state I want. Now, I cannot see this properly. If you drop down and can't
see the names properly, what I'll do is just
select the chart and go to the format
under the values. I'll just move down
under the background, I'll change the background
to black. Let's try. And now I can see this. You can also change
it to blue or any, um, you know, matching color. Actually, let's try
any other color, you can change it to this color. And then you can set it
anywhere you want, like this. So now, whenever you want to filter it for a
particular state, you will see that all the charts change according to that state. So this is pretty awesome. And whenever you
want to see for all, just uncheck that
particular filter and you will see
the whole result. Similarly, four quarters you can see the quarter
wise results. And whenever you want to reset, just click it again and then
you will see the totals. Whenever I want to
filter it according to a particular state or according
to a particular category, let's say I can do that as well. Here in power BI, just click on that
particular chart and it will adjust it for all
the other items. You can see this call
out change as well. Just try it by
yourself and you will explore the power
of this analysis. Whenever you want to reset that, just click that chart again. This is our final dashboard. I'll just save it and attach
with the link of this video. So you can just check that out. Also, you can add this skill
in your resume so you can write all of this
in your project learnings in your resume. I'll attach all of this text
with the link of this video. So you can just copy
and paste that as well, or you can make
some small changes in that, whatever you like. So I hope this course will
be very helpful for you. And if it did help you
to learn something new, I will really appreciate if you post a detailed
review about it. And also if you want to
learn more about it on the same topic or
some other topic, you can message me
for that as well. You can post that
suggestions as well. So with the said,
this is start signing off from today's
video. Take care.
Saad Nadeem, Software Trainer
