Learn how to create an interactive dashboard as a data analysts. Using Power Query and Data modeling technique.

INTRODUCTION

?

The owner of Anra Stores with Location in three regions across Nigeria requested I analyze her business based on the under listed objectives.

The company has five main products and eleven staff. The owner is seeking to expand and increase its number of locations and also promote one of the sales representatives to the position of head of sales. She also wants to appoint a sales representative for each location. Note that this is a fictitious dataset.

?

IMPLEMENTATION

To implement this, I used the Prepare, Model, Analyze, Visualize. Manage resources Process (PMAVM). And the tool used is MS. Excel 2019

STEP 1: DATA PREPARATION

In data preparation, the aim is to define the objectives, confirm the objects (table) and variables(column), get, clean, and transform the data set.

1.1?OBJECTIVES

The objectives of this study are outlined as follows:

1.?????Which is the best-selling product based on sales value?

2.?????Which location should we open an additional office.

3.?????Which day of the week has the highest orders?

4.?????The performance of each sales representative per region

5.?????Monthly sales performance

?

1.2?MEASURES

The dataset required should contain information about the sales rep., their sales vrecords, dates, amount regional information. In this case, the company made available their sales record in 2014/2015.

?

1.3?Get Data

Import the dataset from the Company’s Website. Open a new Excel Workbook> Go to data>Go to Get Data>Select File>Select from Excel Workbook>

Select the file >Import data to bring it to Power Query>Transform Data

?

?

1.4?Clean and Transform Data

Change the data type in Unit Cost and Total from decimal to currency

?

?

To create the weekday column> select the Order Date column>click on add column>go to date> select day>name of day

Change the name of the sheet to sales>click on the home tap>select close and apply > click close and load to close Power Query and load the data into Excel.

?

?

?

STEP TWO/THREE/FOUR: CREATE THE DATA MODEL/ANALYZE/VISUALS

A data model is a subset of the main data or the relationship between multiple data sets, that answer specific objectives. To create the data model, I will use Pivot Table.

?

Objective 1: Which is the best-selling product based on sales value?

To model this, use item versus total.

?

To create a Pivot table, select any of the data point>go to insert tab>click Pivot Table..allow all the options and click Ok. This will create Pivot Table

?

In Pivot table field, Drag item to Row and Total to values. This will create the model.

?

To?Format the Data>right click the number column>select number format>select currency >Ok.

?

ANALYZE

To analyze, we sort the values. To do this, right click the number part>Sort>Largest to smallest

?

?

VISUALIZE

Since the data point are less than six and there is significant difference between the point, I will use Pie Chart to visualize the data

?

Steps: Select the Data point>>select Pivot Table Analyze> Pivot Chart>select Pie Chart>Ok

To prepare the chart area for dashboard. I will remove the background on all the charts. To do this, right click the chart area>select format chart area, in the field section, in the colour fill option, set the background to no fill, and set the text to white.

?

Right click on the chart itself>select format data series>in the colour option, set the boarder colour to no line

?

?

?

?

Objective 2: Which location should we open an additional office

Plot Region against Total. I will use Doughnut Chart and format as the previous

?

Objectives 3 :Which day of the week has the highest orders?

?

Plot week day(Day Name) versus Order date. I will use a Column Chart

?

?

?

?

Objective 4:The performance of each sales representative per region

This will need three dimensions/arguments. The dimensions will be the : Rep. Region, versus Total. In the Pivot Table, we bring Rep. to Rows, Total to Vales, and Region to Columns. I will use the Stacked Bar Chart. Sort the values from smallest to largest.

Objective 5: Monthly sales performance

I will create a Line chart to show what is happening in the business over the two year period. Plot Order date Versus Total. Then we expand entire field to the quarters in the years

Click on the Quarter and repeat the same process

I will use a Line Chart

STEP 5: DASHBOARD/RECOMMENDATION

Go to a new excel worksheet>go to view>turn off your headlings, and gridlines>select all(ctrl A)>change the background to black> ?

?

?

?

Report connection to enable the slicer work on all the charts

?

To change or format the slicer, click on a slicer>select slicer>click on the arrow down>New slicer style>

?

To insert KPI’s. Total Sales

Here’s the Dashboard

#DataAnalyst#DashBoard#Skillahead