Hello and welcome to Lean Excel Solutions! Today we will learn to create
this sales dashboard in Power BI. In the earlier video, we created the
same dashboard in Microsoft Excel. Now, using the same database we
will design it in the Power BI. Some of the features are
explained there in detail. Like, as an overview of dashboard visuals,
formulae, linkage, background design, etc. So, if you wish to watch it for better learning,
the link is provided in the description. Also at the end of this
video. Or you can click here. Alright, we are going to design this
dashboard with the following steps. First, import the data to Power BI. Second, edit it in Power Query Editor
to prepare the primary database. Third, create columns and
measures for data analysis. Fourth, create visuals. Fifth, format the dashboard background. And the last, format visuals. You can directly go to any steps by clicking
on the timeline provided in the description. So let's start with the first steps. These are the sheets that we are going
to use for creating the dashboard. Let's open one blank Power BI
report and import the files to it. Here we can see the imported data. Click on edit query, it will open a
new window for the power query editor. Let's add some basic columns, like - Day, Year,
and Month, with respect to the Date column. Extract only the first three
characters of the month. Now, using Merge Query, let's add
the columns from the master data. We already have the Product
ID column in this table. So, extract the remaining columns. And now, let's close and apply the changes. Now, let's add new columns for Total
Buying Values and Total Selling Value. Create new measures for Profit and profit %. Alright, the data preparation is completed now.
Let's go back to the Report and add visuals. First slicers, for Year, Month,
Sales Type, and Mode of Payment. Use list slicer for Year & Month. And
drop-down for Sales Type & Payment Mode. Add Total Selling Value &
use the 'Card' visual for it. Copy it, and paste it 6 times, so that we
can use the same format for other visuals. Like Profit, Profit %, Top product, and category. Change the data linkage according to visuals. Now, to extract data for the Top product and
Top category, we will use the filters here. let's add a stacked column
chart for the Monthly visual. Sort it by Month, instead of Selling Value. Here we can see, that months are sorted as per
alphabetic order and not as per month order. To sort it as per month order, let's go-to Data, select the Month Name column, and sort it as per
the Month column, which has the month number. Now, we can see visuals are corrected accordingly. On hover over the column, it
shows the month and value. Let's add Profit % too. To do so, just drag and drop Profit % to
tooltips. We can see, that it is added here. Let's add the remaining visuals. Let's do some formatting. Like, remove category,
title, and background color from visuals. Now, all visuals including slicers
are linked with each other. But, we don't want to link this
month's slicer with the Monthly visual, and also the Sales Type and Payment Mode.
So let's go to the Format tab, and click on the Edit Interactions.
As you can see, these Filters and 'None' symbols will start appearing on
all visuals except the one we selected. So let's click on the None symbol to de-link
the monthly visual from the month slicer. Let's do the same for the
Sales Type and Payment Mode. Here we can see, that the monthly visual is not
changing now, on selection in the month slicer. Now, for background design,
let's go to PowerPoint. These are the backgrounds we have designed in
the earlier tutorial for the Excel dashboard. We will use this one for Power BI. It is copied from the above slide and changed some
color combinations and added title & icons to it. To add it as a background, in Excel, we just
selected all copied and pasted in Excel. But for Power BI, we have to
save it as an image first. To do that, just select all, right-click,
and select save as picture option. But, if you are using an older version
of Excel, this option will not be there. So after copying, you can open Paint
paste it, and save it as an image. Or you can use snipping tools to
capture the image and save it. You can find many more options online to do that. These are the color palettes we are going to
use for this dashboard, which we will see later. Alright, let's go back to Power BI.
Under Page Background, click on Add Image, and select the one which we have saved.
Change transparency to zero, and select fit here. Here is the background uploaded for our dashboard.
Let's remove the slicer background Now, instead of changing the
colors one by one for visuals, let's do the changes in Theme colors directly. Go to Customized Current Theme. Since our dashboard background theme is
dark, change text colors first to white let's align the visuals first Now, as we have seen earlier, these
are the colors we will use here. Let's change it quickly. Let's complete the remaining formatting now. So our dashboard is ready now.
If you like the video, give it a thumbs-up, and subscribe to the
channel, if you have not yet subscribed. Thank you for watching
