Hello and welcome to Lean Excel Solutions! Today we will learn to create this
fully interactive sales distribution dashboard in Power BI.
In addition to this, we will learn to change the complete theme
of the dashboard with just one click. Like here I have provided options
to select the dark or light theme. we are going to do it in the following steps- First, import the data to
Power BI, that is datasheets. Second, edit it in the power query
editor to prepare the primary database. Third, create visuals.
Fourth, format the visuals and dashboard background. And the last and most important, change
the dashboard theme with one click. You can directly go to any steps by clicking
on the chapters provided in the description. A similar dashboard with the same input
sheets is designed in Microsoft excel too. If you are interested to learn
it in excel, you can click here or the link is provided in the description. So let's start with the first step. These are the input sheets.
First, input data, which has sales data. Second, the target sheet, which
has a monthly sales target. And the last, customer sheet,
which has customers location. So let's open a new Power BI report
and import the input sheets to it. Here are the sheets imported
to Power Query Editor. Now let's add a few columns in input data. First, Actual column, which is sales value, which
is the multiplication of unit price and quantity. Now add week number and month
with respect to the date column. let's extract the first 3 characters of the month. Change the Actual column type to
integer and click close and apply. So the data is uploaded.
Let's check the model. Alright, it has already detected the relationship
automatically, we need not do anything here. Let's go back to the report
tab to add the visuals. The first slicer for the month.
Change it to horizontal. Next, card for the total sales. Add a slicer for the region. Add line and stack column chart
for monthly sales and target. Area chart for the weekly sales trend. Let's add a multi-row card
for the top 3 customers. Use filters for selecting the top three
customers based on total sales value. Now, clustered bar chart for product-wise sales. Then donut chart for region-wise sales.
And last, map for the country. So all visuals are added, let's
format the visuals and background. I will do this formatting portion a little
quick with faster speed to save time. If you wish to watch it slowly you can
adjust the playback speed in the setting. Let's format this one. Use the format painter to apply
the same changes to others. Change the titles. Now let's organize these visuals
properly and do some basic formatting, like fonts type, size, colors, etc. Let's sort it as per month order. But
before that, we have to sort in the data. First, select the month name, and
sort it as per the month column. Now we can sort it here. Let's sort this slicer too, as per month order. Ok, this slicer is linked with
the input data table and we have not sorted it in data as per month order.
So let's repeat the sorting step here too. Now it is as per month order.
Let's complete the header portion, like title, background, icons, etc., Add rectangle shape and send it back. We will discuss later that
why we have added this shape. Insert icon and logo For the time being, remove
the fill color of this shape. Alright, the dashboard is ready now, let's go
for the last step that is formatting the themes. First, let's create one table for
the theme within the power BI only. So the table is added here let's add the
theme column to the slicer and format it Now let's create a few measures for theme color. Like, these colors or color codes, we will
use for formatting the dark and light theme. Let's put it in the formula like this. So the measure is added here.
Copy the formula for the next measure add a new measure, paste it, and
change the name and color codes in it. So in a similar way, these measures
are added here as per the list. Let's keep the single selection
on for the theme slicer. Now let's apply the conditional formatting for the
title of this visual using the measure we created. Go to the title, change the font color
to white, and for the background, select this function sign here.
It will open a new window. Under the format style, select Field Value, and
then choose appropriate measure and click OK. Here we can see the title
background changes to this color. Let's change a few other titles quickly. So, here we can see the demo that, how title color
is changing based on the theme selection slicer. Let's complete the remaining formatting for
the background as well as for the fonts. Here again I will do it at
faster speed to save time. Ok, so at some places, you will not find the
option to select the conditional formatting, like an item color of the slicer here.
In that case, we will choose one common color which will be suitable for
both dark and light themes. for the monthly slicer also,
we will apply common color. Let's change the heading font to white. Here, for page background also, we don't have
the option to conditional formatting selection. So first add the shape as a background
and then apply formatting to it. So, this way we can change the complete
theme by selecting a theme slicer. But the heading color is not
looking good with the light theme, so let's apply formatting to
the shape that we added earlier. And at last, let's apply color
formatting for the bars of monthly sales. Like above the target green
color, and below target red. Create one measure for it and apply
it to the bar and for total sales too. Let's complete the remaining formatting. now let's edit interaction to break
the linkage between a few visuals. And the dashboard is ready.
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!
