Task 6 Data Science 365 Power BI

Muskanjain

9 min readDec 3, 2021

Sales Dashboard

Objectives

· Users can get a better understanding of their sales

· See the month-on-month performance

· See the figures in one currency and how it compares to other currencies

Tasks:

Import all five tables from the Excel File called “Section Six Data Extract” into Power Query Mode

2. Create “Username” column inside dim_Customer table from the email address column. We are only looking for the text before the “@” symbol. The column should also be labeled “Username”

Go to Query Mode, select the dim_Customer table and select Split column by delimiter i.e, ‘@’

After splitting the column with the delimiter @, the two columns with strings before and after @ are formed, we need only before @

Columns like Title, Middle Name and many more have many Null values and can be deleted.

3. Scan the dim_Customer table and look out for columns with lots of Null values and remove them from the table

Few more Pre-processing and Tranformations

Dates

Click on the column that contains the date and click on Add Column- Extract- Range

Looking at the number in OrderDateKey column, the integer is YYYYMMDD so to extract the month, we start at digit number 4 and we need digits 4 and 5, thus 2 digits

4. Import the tables into the model and check the connections between tables, make sure that every table is connected

All tables are connected to the Internet Sales table

5. Create #Measures table using DAX

Click on Modelling and go to the option shown in the picture.

GenerateSeries returns a single column forming an arithmetic progression. Takes (starting number, ending number, common difference)

6. Create Date table using “fact_InternetSales[ShipDate]” column and create the following columns “Year”, “Month”, “Month Name”, “Day of Week”, “Quarter”, “Year/Quarter”. Make sure that the Month column is sorted correctly

The above DAX formula helps with forming a date table. Here the FORMAT function us used to return the name of the month, day and quarter in the desired text format as shown in the row of the table.

7. Dynamic measure for selected currency, based on the selected currency the measure should return the sales amount in the selected currency, if no currency has been selected the measure should say “No currency selected”.

For creating a new measure, choose the measures table you created by clicking once on it.

Click on the ellipses on #Measures and select New Measure

For returning the sales amount for a selected currency, we use the DAX formula shown above. The first ‘var’ will give the Total sales amount for any currency. Next, the CALCULATE will sum up values based on a filter, here filters are currency names. Finally the SELECTEDVALUE function will return the specified sums obtained for each currency from the Calculate function

To test out this new measure do the following

Select the CurrencyAlternateKey from the Currency Table. It will form a table as shown above.

Click on the Slicer from Visualizations to form a checklist of the currencies. Alternatively, you can also click on the little drop down circled above inside the Slicer table to create a Drop Down of the currencies instead of a checklist.

You can select the Slicer Box, and got to formatting to keep the Border and Shadow on so that the little box stands out

Select the measure we just created Sales_in_selected_currency. Choose the Card Visualization. Since we have not selected any currency from the dropdown yet, ‘No Currency Selected’ would show as coded in the IF statement above. Format the Card with shadow, border and any font size/type changes.

On selected CAD, sales amount equal to 1.81 M is displayed on the Card.

Sales Amount changes as we select FrenchFranc.

8. Dynamic measure for title that should say “Sales Amount in [Selected Currency] vs All Currencies“, alternatively it should say “Please Select Currency from the Dropdown Menu”

We need to create a Title Bar which would be the at the top of the screen

The DAX formula above would work by first checking if any filter is applied on the CurrencyAlternateKey column, if say you choose USD then it will retrieve USD, and then return USD and the sentence mentioned after the Return Statement.

This is how the Title Bar would look when no country is selected from the CurrencyAlternateKey dropdown. Make sure to select the Card Visualization for the text to be displayed. We can now format this Card Visual.

We can change the Font size/style add border, shadow and switch Category as off to remove the ‘TitleBar’ from showing in the box

This is what the Title Bar would display on selecting CAD from the Dropdown

We can also show the % share of sales as per the currency

Create a new measure showing the total sales amount

Create another measure calculating the total sales amount for all currencies

the %Sales bar graph changes according to the currency chosen in the slicer

9. Create measures for Previous One Month, Previous Three Months and Previous Six Months

On sorting the Ship Date column in ascending order the first date is 1/5/11 and last date is 2/4/2014

Since sales amount for last 6 months, 3 months and 1 month is wanted we can create a table from Aug 2013-Jan 2014 ie last 6 months. Since Feb has just 4 days of sale, we can keep it as the current month.

Join the Last_few_months table with the Internet Sales table via the Date →Ship Date columns

To create measures pertaining time, we have to connect the Date table that we created with other tables.

Connected the Date table with the Internet Sales table via Date and Ship_Date columns respectively

We can calculate TotalSalesAmount and filter the result using the Datesbetween function which would return dates between 1st Jan 2014 and 31st Jan 2014, the last month.

Similarly, sales 3 months prior and 6 months prior can be calculated.

Last 3 months Sales, Nov-Jan

Last 6 months Sales, Aug-Jan

We can change the units of display of currency for each field as shown in the highlighted portions above.

10. Measure that will tell us the time the report was last opened, text should say “Executed: Date and Time”

We can show the exact date and time on which the visuals are being created

11. Header (background color optional) with line color #e8d400

To make the Header of the Dashboard, Go to Insert →Shapes →Rectangles

Stretch the rectangle to full length. We can increase the Outline weight to make it thicker and choose the turquoise color as the custom for the dashboard.

Fill the rectangle with a light grey shade

12. Insert logo inside the header and resize it accordingly

Insert a picture of your company logo using the insert picture option

Insert a Blank Button to go on the header

13. Create buttons for “Clear Filters” , “Users” , “Currency”, “Sales Territory”. Buttons need to be positioned inside the header

The Button can be formatted with fill and text

14. Executed Measure to be placed inside the header on the right using Card Visual and resized accordingly, text should be on one line.

Bring forward the company Logo and put the Executed measure in the Header

15. Create dropdown menus using bookmarks for Users, Currency and Sales Territory. The dropdown menus should have outline custom color an arrow pointing up to close the menu Each of the dropdowns should contain the option to search inside the filter.

Copy the Header and paste it, resize it to be a box under Users button. Select the Username column, put a slicer and fit it in the box under Username.

Select the box of Usernames checklist and click Ctrl+F to insert a searchbox. Then insert the picture of the black up arrow inside the box, and select Bring to the Front on it.

Insert Bookmarks. Name the first one Clear Filters and hide the Slicer, Image and Shape and Click Update. Note: shift the Slicer above the Image in the Selection pane before doing this.

Users OFF Bookmark would have the Slicer, Image and Shape hidden and Data button checked off.

To make the Users button show the usernames list on clicking, go to Format button and select Action → Bookmark → Users ON

On clicking the arrow up button, the Usernames list should close. So we assign the Users OFF Bookmark to it. Hover over the button, Ctrl+Click to make it work.

Choose the CurrencyAlternateKey and put the slicer on the table for the Currency button

Click on the arrow up and make the button Currency OFF

We have to keep checking Bookmarks. For instance, when choosing the Users ON Bookmark, the Currency ON bookmark is also on display

Hide the Image, Slicer, Shape on top, which is for the Currency ON bookmark not the Users Bookmark and click Update

Select the SalesTerritory[Country] column and make it a slicer. Fit it in the box.

The Country column has NA values which need to be removed. To do this, click on Transform data to go to the Power Query Editor

The Bottom row has NA values thus select Remove Bottom Rows

16. Create a bookmark that clears all the filters and apply it to “Clear Filters” button

The Clear Filter bookmark would have all the shapes, slicers, images hidden. Don’t forget to click Update!

Use the Clear Button Filter to remove all filters(bookmarks)

17. Import Timeline Slicer into the visualizations panel. The slicer should be positioned below the header.

Choose the Get more visuals for the Time Slicer visualization

Put the Date column in the Time Slicer Visual

The Time Slicer inside a rectangular shape with color coded borders

18. Create three boxes — first one to show Sales Amount in Selected Currency, second one to show Sales Amount for All Currencies in Previous Month, Sales Amount for All Time for All Currencies. Sales Amount for Last Month and Sales Amount for All Time should not be filtered when choosing currency or region from the Header menu.

Drag the the Sales_in_selected_currency measure Card visual in the rectangle. And select the Format as Bring to front

Select the last_month measure and fit it in the rectangle. Change the title on from the format pane to ‘Sales Last Month’

19. Find out how to Increase Canvas Size to 1720h x 1280w

Choose this option to change the size of the page

With the specified dimensions the page width shrinks, thus resize to fit to page and and you can keep scrolling to get more page size

20. Position the Dynamic Title Measure in the middle inside a card visual with appropriate font size.

21. Using Line and stacked column chart create a visual that shows all Sales Amount in All Currencies, split by month name. Font size to be increased accordingly. *Inside the chart we should show the selected currency from the dropdown menu as a dot. Marker size to be 10 pixels. The chart should also have a Legend. Legend to be positioned in Top Center. Data lables should be visible for the bars, but not for the dots. The visual should have a placeholder in the background

Choose TotalSalesAmountAll and Month Name. The Month names need to be arranged from January to February

Sort the Date Table’s Month Name column, with Month column which contains month numbers. Sort in ascending order, then sort the figure in report view by Month Name →ascending

Select a currency from the slicer and edit interaction to none for Sales last month and Total Sales amount as they should always show the total aggregate.

Choose a column line chart to put the Sales in selected currency on the Total Sales by Month chart

Sales amount in Canadian Dollar (marked by dots) as compared to all currencies

22. Additional Dynamic Title Measure to be created. This title should tell us Total Products sold in the selected country or alternatively Total Products Sold Worldwide. This should be triggered by the Sales Territory Dropdown button from the Header

Create a new measure called MapTitle to return the text of total quantity of products produced in the selected country, and if no country is selected give the worldwide total products.

No country is selected thus the title shows Total Products Sold Worldwide

Select the SalesTerritoryCountry column and insert the Map visualization. Fit it inside the Rectangle shape and bring the Map visu to the front.

23. Number of Products Sold by Country to be shown inside a map visual. The visual should have a placeholder in the background.

Create a new measure called Products_sold which would count all the rows in the Internet Sales table where each row represents a product sold.

The bubbles represents the no of products sold in each country. Color and bubble size can be formatted.

24. Create a table that shows the SalesTerritory along with the Sales Amount as %, Total Sales Amount, Sales for Previous One Month, Previous Three Months, Previous Six Months. Font size to be increased accordingly, outline to be removed. Totals should have the same background color as per headers. Remove the outline from Totals as well. SalesTerritoryCountry background color to be changed to the same color as Totals. All headers should be capitalised. Apply conditional formatting to the Total Sales Amount %. If values are between 0 and less than or equal to 20% make it blue, if it is greater than or equal to 30% and less than 50% make it yellow, if it is greater than or equal to 50% and less than or equal to 100%, make it red