Power BI Projects – connect2rk.com

Project description (POWER-BI)

ABOUT THE PROJECT

Data Gathering / Requirement:
Sales (folder by year)
Categories (Excel)
Geography (Excel)
Product (CSV / Database)
SalesRep (Excel)
SubCategories (Excel)

Task 1.1:

Create a mechanism to load all the files from the sales folder in a single Sales fact table.

The mechanism needs to be resilient as:

-removing a file from the sales folder does not create an error for missing files.

-adding a new yearly sales file will automatically be loaded in the fact query upon refresh.

Data Modeling:

Task 2.1:

Do the respective transformations to the Sales fact table in order to split the Country form the City in field “Location”. Make sure you set up the correct Data Type to allow Geo maps.

Do the necessary updates in the Date field to make sure you can setup the Date format.

Task 2.2:

Create unique key (GeoKey) in Sales and Geography table

Task 2.3:

The Dimensional queries SalesRep and Subcategory need additional treatment. Create a small function that removes the “ID – ” part of these columns that you can invoke and reuse for these two queries to clean the IDs.

Task 2.4:

Create the Data Model connecting all tables and using the Calendar table already set up in the pbix.

DAX calculations

Task 3.1:

Calculate Total Revenue in Sales table, using the Product’s Retail Price, and multiplying it by the Units.

Task 3.2:

Calculate Total Cost in Sales table, using the Product’s Standard Cost, and multiplying it by the Units.

Task 3.3:

Calculate Gross Profit in Sales: Total Revenue – Total Cost

Task 3.4:

Calculate a Gross profit MoM growth Change% measure that could benefit us in decision making

Task 3.5:

Calculate a measure for AVG sales per day – this is the average sum of Total Revenue per day based on the Dates of actual Sales.

Task 3.7:

Breakdown Analysis by Product (drop or increase)

Calculate the following time measures:

This is QBR Report. So QoQ Growth is required

ABOUT THE PROJECT

Data Dictionary

RowNumber—corresponds to the record (row) number and has no effect on the output.
CustomerId—contains random values and has no effect on customer leaving the bank.
Surname—the surname of a customer has no impact on their decision to leave the bank.
CreditScore—can have an effect on customer churn, since a customer with a higher credit score is less likely to leave the bank.

Credit score:

Excellent: 800–850
Very Good: 740–799
Good: 670–739
Fair: 580–669
Poor: 300–579
Geography—a customer’s location can affect their decision to leave the bank.
Gender—it’s interesting to explore whether gender plays a role in a customer leaving the bank.
Age—this is certainly relevant, since older customers are less likely to leave their bank than younger ones.
Tenure—refers to the number of years that the customer has been a client of the bank. Normally, older clients are more loyal and less likely to leave a bank.
- Balance—also a very good indicator of customer churn, as people with a higher balance in their accounts are less likely to leave the bank compared to those with lower balances.
- NumOfProducts—refers to the number of products that a customer has purchased through the bank.
- HasCrCard—denotes whether or not a customer has a credit card. This column is also relevant, since people with a credit card are less likely to leave the bank.
  - 1 represents credit card holder
  - 0 represents non credit card holder
- IsActiveMember—active customers are less likely to leave the bank.
  - 1 represents Active Member
  - 0 represents Inactive Member
- Estimated Salary—as with balance, people with lower salaries are more likely to leave the bank compared to those with higher salaries.
- Exited—whether or not the customer left the bank.