1-Month Power BI Internship with Foresight BI & Analytics

Last Month, in July, I enrolled in the Mastering DAX Foundations course by Foresightbi, An online system owned by Mr. Ahmed Oyelowo.

What is DAX?

Dax, which means Data Analysis Expression,  is a formula expression language and can be used in different BI and visualization tools. It can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models.

I got certified before the month’s end and enrolled in their Power BI internship class called 1-Month Internship with Foresight BI & Analytics.

In the program introduction, Read the message below:

“This program is designed to provide you with an opportunity to use Power BI to solve real-life Reporting and Analytics problems.

There will be several levels involved. While completing lower levels are accomplishment on its own, completing lower levels is also a prerequisite to the higher levels.

I wish you a successful learning experience as you complete your tasks and the project.”

So we were given an overview of what we would come across during the one-month program. Below is an introduction to the company and the data that was given to us as we began the program.

Background

“Forggith Pharmaceuticals (Forggith) is a Pharmaceutical Manufacturing company based in Germany. As a Manufacturing company, they produce medical drugs that get to consumers through their Distributors.

Forggith provided a template for their distributors to capture records of their sales which are then sent to Forggith on a monthly basis. This data is then used for reporting and analysis by Forggith to achieve their goals Sales and Marketing objectives through tracking and monitoring of KPIs.

In their efforts to maximize growth, Forggith works with a team of Sales and Marketing pros who ensure retailers are able to get their products through the distributors. That is, Forggith does not sell directly to retailers or end-users, they sell to Distributors. But they maintain interaction with retailers, through their Sales and Marketing pros.”

Understanding what the company is all about already solved 30% of the challenges I might faced doing the analysis because as a data analyst, you need to have knowledge about the dataset you are working with before opening your Excel or database.

Moving on, we were given some already identified questions that the company (Forggith ) would like us to answer when performing the analysis. READ BELOW

Power BI Reporting Requirement.

Forggith is looking to create some Power BI Reports to assist in guiding their strategies, tactics, and operations as a company. For a start, they have identified a couple of numbers they would like to report from their data.

Note: You are to use the company’s standard colors across your reports. You will find the Logo and Color guides in the Assets Folder you can download in the next lesson.

Sales Performance Overview (Sliced by: Year, Month, Quarter, Team)

Total  Revenue

Total Revenue Year To Date (YTD)

Total Revenue Previous Year YTD

Total Revenue Same Period Last Year(SPLY)

Total Target

Total TargetYTD

Actual Revenue Performance Previous Year YTD vs Target Previous Year YTD

Actual Revenue Performance YTD vs Target YTD

Revenue Month-on-Month Percentage Change

Revenue Distribution by Location

Revenue by Channel

Revenue by Product Class

Marketing Performance (Slice by Year, Quarter, Month, Product Category and Team)

Revenue Achieved vs Revenue Target

Volume Achieved vs Volume Target

Actual Revenue by Sales Representative

Target Revenue Achievement% by Sales Representative

Actual Volume by Sales Representative

Target Volume Achievement by Sales Representative

Actual Revenue Achievement by Sales Team

Revenue and Volume Achievement by Product.

With these breakdown questions, I am already 50% done with my analysis even when I have not opened the dataset, The reason is that the company has answered the first rule of a data analysis which is to “ASK” Questions

“Asking questions will give you enough context and help you understand the stakeholder’s needs”

Now I proceeded to download and open the dataset (Forggith dataset) to prepare and process it.

Yes, we were also given ideas of how the Forggith organization will use the reports

How these reports will be used:

  • The Sales Representatives can track their performances throughout the period to plan their marketing activities.
  • The Team Managers can track their teams’ performances throughout the periods to plan their teams’ activities.
  • The executive team can track Revenue numbers to monitor alignment with the set targets to influence medium to long-term strategies.

Preparation & Processing

The dataset consists of two files, The Target and the Actual data.

The Target data contains the Target each sales rep where to meet on every product from 2022 to 2025. Below is a preview of the dataset.

Target data

The actual data contains these tables: Location, channel, products, Employees, and Sales 2022 – 2025.

Actual data

The data is cleaned so there are no cleaning processes.

If you noticed in the above preview, the Target and actual data  are not on the same granularity level so we need to keep them separate and we will be having two facts tables unlike how it usually be one fact table and plenty of dimensional lol

I started by importing my data into Power BI power query for Transformation.

What I did first after importing my datasets into power query was to append the 2022 – 2025 sales. You can see the screenshot below

To append data, go to power query, Home, and click on append as new.

Now I have my sales from 2022 – 2025 in one Table.

What I did next was to have months and years for the target table in a single column by using the unpivot column and merging the two together. You can see in the below screenshot that I now have my column for month year in a single column in a data type

Next is to merge the channel and sub-channel together using the channel ID. 

Channel

Now we can load our data into Power BI for modeling

DATA MODELING

Power BI will automatically create a relationship between the dimensional table and to facts table but you have to crosscheck it.

Screenshot below shows how I created relationship between my tables

Model View

I will be performing periodic analysis so I will need to create a calendar table. I used the below Dax function

I then proceeded to connect the calendar table with my actual table and target table using the date column.

ANALYSIS

I started by creating some measures using Dax, I would need these measures to perform my analysis and make data-driven decisions.

Total  Revenue

Revenue = SUM(Sales[Total Sales])

Total Revenue Year To Date (YTD)

Revenue YTD = TOTALYTD([Revenue], Calender[Date])

Total Revenue Previous Year YTD

Revenue PYTD = CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR(Calender[Date]))

Total Revenue Same Period Last Year(SPLY)

Revenue SPLY = CALCULATE([Revenue], SAMEPERIODLASTYEAR(Calender[Date]))

Total Target

Total Target = SUM(Targets[Quantity])

Total TargetYTD

TargetYTD = TOTALYTD([Total Target], Calender[Date])

Revenue Month-on-Month Percentage Change

RMOM% Change = DIVIDE([Revenue], [Revenue PREVM], BLANK()) -1

Volume Archived

Volume Achived = SUM(Sales[Quantity])

Volume Target

Volume Target = SUM(Targets[Quantity])

Total orders

Total orders = COUNT(Sales[Quantity])

Total target

Total Target = SUM(Targets[Quantity])

No of manager

No of Manager = DISTINCTCOUNT(DimEmployees[Manager])

No of sales rep

No of Sales Rep = COUNT(DimEmployees[ID])

When I finished with the measures I started answering the business questions by creating a matrix before visualizing it.

VISUALIZATION

If you read the ideas shared above, it shows that three departments will be using my analysis, the Executives, Team, and sales Representatives.

To make my dashboard simple and insightful, I decided to create three different dashboards that meet the needs of each department.

To have a beautiful dashboard, I used PowerPoint to create my background so I could have control over the background of the dashboard and how I wanted it to look.

Background

My final visualization

I submitted my assignment to Foresight BI and it was approved, and I was awarded a certification. See my certification below

Power BI Internship Certification

The preparation, processing, and analysis took me a week because I submitted my model, and measures and I needed to wait for its approval before proceeding.

 

Special Thanks to Ahmed Oyelowo and the entire team of Foresight Bi for this incredible course and training.

Thanks for reading.

Damidez

I'm Damilare Damidez, A Data Analyst and Webmaster. I have been on the internet for over a decade. I help business owners to make data driven decision.

More Reading

Post navigation

Leave a Comment

Leave a Reply

Your email address will not be published. Required fields are marked *