DEV Community: DataWired22

Case-Study: HR Analytics in PowerBI

DataWired22 — Wed, 01 Mar 2023 10:37:33 +0000

Summary of case study

This case study is to apply skills developed through the Power BI course offered by Data Camp using a real-world problem. This is the second project assignment.

The Problem

In this case study, a fictitious client, Atlas Labs, have the primary goal of monitoring Key HR metrics on employees and understanding what factors impact attrition (Employee attrition is the naturally occurring, voluntary departure of employees from a company)

Project Approach

As we build their report, it is key to understand the process we will follow in our report development in PowerBI. The process is summarized below:

Step 1: Building the data model and analyzing the data

Requirements gathering
Data Connections
Data Transformation
Building the data model
Writing DAX measures

Step 2: Report Design

Branding
Defining the report layout
Building your report with chart visualizations

The Dataset: Requirements gathering, Data Connections and Data Transformation

For this case study, we will use the Kimball modelling approach as we model our data.

The Fact table stores the information about employee yearly reviews. We will also be working with multiple dimension tables to provide more context to the data we are working with. The final data model will follow a snowflake schema below.

The first step we will take is to open an empty PowerBI file and load and prepare our dataset. We will import all the CSV files and load them into PowerBI. Ensure that the data is clean. You may refer to this article on the expectations of clean data. Ensure you add either Fact OR Dim at the beginning of each table name, depending on the type of table it is.

Building the data model: Date dimension and relating tables

In any Power BI report, it is recommended to have a dedicated date table for accurate date and time reporting. In this stage, we will also model our data. This will enable us to connect our six different tables.

The first step we will take is to create a dedicated date table. Here is the DAX code we will use for the calculated Date Table. Next we will connect the following tables:

Connect the DimDate table to FactPerfomanceRating and the DimEmployee table. Note that the last relationship will be inactive because we cannot have more than one active relationship between the same tables at once, in PowerBI.
Connect the DimEducationLevel table to the DimEmployee table
Connect FactPerfomanceRating table columns (EnvironmentSatisfaction, JobSatisfaction, RelationshipSatisfaction and WorkLifeBalance) to DimSatisfactionLevel table and use EnvironmentSatisfaction as the active connection.
Connect FactPerfomanceRating table columns (SelfRating, ManagerRating) to DimRatingLevel and use the SelfRating column as the active connection.

Exploring the data

We will explore our data and produce high level metrics to understand the attrition at the company. Rename page 1 of your PowerBI file to “Overview” and create a new table called _Measures to store all the measures that we will create. We will then display these measures using the card visual in our overview page. Let’s create the measures below:

A measure to take count of all the employees



TotalEmployees = DISTINCTCOUNT(DimEmployee[EmployeeID])

Measures that take count of all employees that are currently active or inactive



ActiveEmployee = CALCULATE([TotalEmployees], DimEmployee[Attrition] = "No")



InactiveEmployee = CALCULATE([TotalEmployees], DimEmployee[Attrition] = "Yes")

Attrition Rate based on the measures we have created above.



% Attrition Rate = [InactiveEmployee]/[TotalEmployees]

Hiring trends over time

Next, we will analyze Atlas Labs' hiring trends over time to see where they have had the biggest growth in employees.

We will create a new measure called TotalEmployeesDate that uses the CALCULATE() function on our TotalEmployees Measure and the USERELATIONSHIP function in the filter. You may read more about USERELATIONSHIP function here



TotalEmployeesDate = 
CALCULATE (
    [TotalEmployees],
    USERELATIONSHIP ( DimEmployee[HireDate], DimDate[Date] )
)

Go ahead and create a stacked column chart. Add attrition to the legend on the chart to see the split of employees by active vs inactive. Change the X-axis from continuous to Categorical and Rename the chart to “Employee Hiring Trends”

Analyzing departments and Job Roles

The next step, we will look into the typical roles department managers are hiring into the organization. This will enable every department to plan for new hiring requests in the future.

Create a clustered bar chart to show ActiveEmployees by Department. Rename this chart to “Active Employees by Department”. Add a treemap visualization to show Active employees by department and job role. Rename this chart to “Active Employees by Department and job role.”

Keys Insights so far

Atlas Labs has employed over 1,470 people
Atlas Labs currently employs over 1,200 people
The largest department so far is Technology
The attrition rate for employees leaving the organization is 16%

Is Atlas labs competitive enough as an employer?

Demographics: age and gender

The next step is to develop a good understanding of the diversity and inclusion metrics. We will focus on employee age and gender. Let’s create a new page in the report called “Demographics”

Below we can see the minimum and maximum value for age displayed by card visuals.

We want to take a closer look at the age distribution of employees so we will create a conditional column called AgeBins that separates employees' ages by bins in the following structure: <\20, 20-29, 30-39, 40-49, 50>.



=Table.AddColumn(#"Changed Type", "AgeBins", each if [Age] >= 50 then "50>" else if [Age] >= 40 then "40-49" else if [Age] >= 30 then "30-39" else if [Age] >= 20 then "20-29" else "<20")

Next, we will visualize our results. We will create a visualization that shows Employees by age and another visualization that shows Total employees value distribution across Age Bins and Gender.

It would be beneficial to add a page level filter that enables you to look at the report page based on whether an employee is currently active or not.

Marital Status and ethnicity

Next, we will be looking at further employee information regarding marital status and ethnicity. We will first visualize the count of all employees by marital status

Next, we will create a measure, AverageSalary inside the measure table. This works out the average salary of all employees.



AverageSalary = AVERAGE(DimEmployee[Salary])
Below is a visualization of employees by their average salary and ethnicity.

Performance Tracker 1:

The HR Team would like to track the performance of the employees based on yearly performance reviews. We will create a new page in the report called “Performance Tracker”.

We will add a slicer with employee full name which has single select and search enabled. We will go on to add a card which shows selected employee start date, last review date and next review date

Performance Tracker 2

Next we would like to take a closer look at individual review ratings. We will create satisfaction metrics inside the _Measures table and visualise them.



EnvironmentSatisfaction = CALCULATE(
MAX(FACTPerformanceRating[EnvironmentSatisfaction]),
USERELATIONSHIP(FACTPerformanceRating[EnvironmentSatisfaction], DimSatisfiedLevel[SatisfactionID]))



JobSatisfaction = MAX(FactPerformanceRating[JobSatisfaction])



RelationshipSatisfaction = CALCULATE(
MAX(FACTPerformanceRating
[RelationshipSatisfaction]),
USERELATIONSHIP(FACTPerformanceRating
[RelationshipSatisfaction], DimSatisfiedLevel[SatisfactionID])
)

More Insights uncovered

Majority of employees are between 20-29 yrs
Atlas Labs employs 2.7% more women than men
Employees who identify as
Non Binary makeup 8.5% of total employees
White have the highest average salary
Mixed or multiple ethnic groups have one of the lowest average salaries

From the charts we’ve built, we can also see that the employees that have been considered frequent travelers have the highest attrition rate despite only making up 19% of the total hires.

Creating a cohesive report

The final step is preparing to deliver our report to the key stakeholders at Atlas Labs. We will focus on delivering insights on attrition and what factors affect employee retention.

Here is the Overview Dashboard.

Here is the final report delivered.

Thank you for reading

Author: Gcinithemba Sherilyn Maphosa

Data Wired

A 5 step approach to Data analysis in PowerBI

DataWired22 — Wed, 08 Feb 2023 10:28:23 +0000

As the data around us continues to become more available, it continues to grow both in amount and complexity, as does the need for an effective process to derive the value the data holds. This value is derived through data analysis. The data analysis process typically moves through several iterative phases which we have grouped into 5 different steps.

*But first, what is data analysis? *

Data analysis is the process of collecting, cleaning, modelling, analyzing, interpreting, and visualizing data to extract insights that support decision-making using various techniques and business intelligence tools.

In this article, we’ll explain the different stages of the data analysis process. The article is aimed at helping you understand what happens at each step in the data analysis process flow. When you’re done, you’ll have a great understanding of the analysis flow which you may start incorporating even in your daily tasks. Here are the 5 steps we’ll go through:

Step 1: Ask questions
Step 2: Data Collection
Step 3: Cleaning the data
Step 4: Analyzing the data
Step 5: Communicate your results

Step 1: Ask questions

Questions help you identify the data you need to collect or use and help you focus on relevant parts of your data and direct your analysis towards meaningful insights. For example: What outcomes do you want to achieve? What specific issues would you like to address? For subscription-based businesses, you might want to ask, what are the causes of customer churn and how can you reduce it ? For an oncology center you might want to ask, what size range of each tumor feature determines that a tumor is cancerous ? For a sales business you might want to ask, does an increase in revenue lead to an increase in profit ? Or a business might what to find out possible future consumer behavior

Step 2: Data Collection

Once you understand the questions that you need to answer the focus of your analysis, you need to define the type of data you need and collect the appropriate data. This might be quantitative (numeric) data, e.g. sales figures, or qualitative (descriptive) data, such as customer surveys and reviews. This data may be collected internally or externally. Internal data is data directly collected by the company you’re doing the analysis for (First - party data). External data is data collected from other companies (Second-party data) or data generated from multiple sources which are usually open data repositories (Third-party data).

The next step is for you to import this data into PowerBI desktop. Open PowerBI and open the Get Data dialog box directly by selecting the Get Data icon itself. You will find multiple options of importing data from different data sources.

This step is important because the nature of the collected data sources determines how in-depth the analysis is. You need to make sure you have enough data to solve the questions.

Step 3: Cleaning the Data

Once you have collected the data, you need to prepare it for analysis. This is called data cleaning. Data cleaning is a vital step in the data analysis process because the accuracy of your analysis will depend on the quality of your data. This process involves checking for incompleteness or inconsistencies and removing incorrect data. Clean data should have the characteristics below:

Free from missing values (nulls)
Typos/data entry errors are corrected
No duplicated data
Irrelevant Data is excluded
Outliers are dealt with (Kept at a certain value)
Correct data types for all columns
Column and table names are short and descriptive
Consistently formatted

In powerBI, cleaning data is done through powerQuery. A data manipulation framework present in PowerBI which allows us to connect to and transform our data according to our needs in a series of transformations. Below are images of some power query functions that are used to clean data.

Step 4: Analyzing/Interpreting the data (Exploratory, Descriptive, Diagnostic, Predictive and Prescriptive analysis)

Once the data is clean, you can start analyzing the data.The type of data analysis you carry out largely depends on what your goal is.

The first step is to carry out exploratory analysis. This process will determine if your data is appropriate and ready for the next stages through further sorting. This process will help you Uncover and resolve data quality issues such as detecting anomalies in your data set as well as understand existing patterns and correlations between variables. In this stage you may also create new variables and measures that will help you with the steps that will follow. New variables and measures are created using DAX( Data Analysis Expressions). DAX is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Below is a snapshot of what a DAX function looks like.

Churned = IF('Databel - Data'[Churn Label]="Yes", 1, 0)

The next step is to carry out descriptive analysis which identifies what has already happened. An example of this for a subscription based business concerned with customer churn, would be identifying that Region A has 15% customer churn. This summary will help you determine how to proceed.

After descriptive analytics, you will need to carry out diagnostic analysis which focuses on understanding why we have the reasons for the results shown from the descriptive analysis. For instance, the reason for a 15% customer churn within region A would be because of competitors having better customer service and cheaper products.

Another step is to carry out predictive analysis, this will allow you to identify future trends based on the historical data in your dataset. This type of analysis is commonly used to forecast future activity. For example, which customer demographic group is more likely to churn ?

The last process of step 4 includes prescriptive analysis which allows you to make recommendations for the future. It incorporates aspects of all the other analyses.

Step 5: Communicating/ Sharing your results

The final step of the entire analysis process is to share your insights. This involves presenting them in a manner that is understandable for everyone within the audience who will be receiving the analysis. In PowerBI, this is done through data storytelling, which involves turning your data into a compelling narrative. This will be through visualization using dashboards and reports.

An example of a dashboard for customer churn analysis may be seen below.

You may find the PowerBI file here.

In summary, The steps above are the core steps for data analysis. They may be re - ordered or amended as per your need. From here, you should consider learning and mastering power BI DAX functions and familiarizing yourself with different concepts of data analytics. It's important to remember that data analysis is not a one-size-fits-all solution, and your results will depend on a variety of factors. The best way to get the most value out of your data analysis is by embedding it in your business strategy.

Thank you for reading

Author: Gcinithemba Sherilyn Maphosa, Data Wired

Case Study: Analyzing Customer Churn in Power BI

DataWired22 — Wed, 04 Jan 2023 13:56:28 +0000

Summary of case study

This case study is for the purpose of applying skills developed through the Power Bi course offered by Data Camp using a real-world problem.

The Problem:

The task is to solve customer churn for a Telecom provider called Databel where I will be using a fictitious churn dataset. I will be analyzing why customers are churning and the churn rate.

Defining churn

A good definition is the one from Investopedia: “The churn rate, also known as the rate of attrition or customer churn, is the rate at which customers stop doing business with an entity.”

Calculating churn

The simplified formula for churn is to divide customers lost by the total number of customers.

Churn rate = customers lost/total number of customers

Example:
Churn rate = 10/100 = 10%

There are multiple methods to calculate churn, and depending on the industry. A traditional e-commerce platform might consider a certain customer a churner if he or she hasn’t made a purchase in the last 12 months.

The data

Key Characteristics:

One big table with 29 different columns with one row per customer.
Snapshot of the database at a specific moment in time, meaning there is no time dimension.
The dataset contains more than just dimensions. Here is a view of the metadata

Data check

The first step in any analysis is doing a data check. I will create two measures to check if the count of customer ids is equal to the count of unique customer ids. This check is particularly important to prevent double-count costs later incase of duplicates.



Number of Customers = COUNT('Databel - Data'[Customer ID])



Number of unique customers = DISTINCTCOUNT('Databel - Data'[Customer ID])

Both values are the same.

Calculating Churn
Before getting further into the analysis, there is a churn label column that indicates “Yes” (if customer has churned) or “No” (If customer has not churned.) Because working with this column is difficult, it is best to convert it to a binomial column (1/0) that indicates whether or not the client churned.

Creating a measure for the churned customers using a conditional IF expression based on the Churn Label



Churned = IF('Databel - Data'[Churn Label]="Yes", 1, 0)

Creating a measure to calculate the churn rate



Churn Rate = [Churned customers]/[Number of Customers]

The total churn rate for “Databel” is *26.86% *

Investigating Churn reasons

Next, I will investigate the different reasons why customers churned.

The top three reasons are:

Competitors made better offers
Competitors had better devices
Attitude of support person

Digging deeper into churn categories

Churn Reasons are grouped together in the Churn Category column. The “Extra data charges”, “Price too high” and other price related reasons are grouped together in the “Price” category. I will be displaying all churn categories in one visualization.

The largest proportion of churned customers churning is related to the competitor category.

Using Maps

Databel wants to know if the aggressive promotions launched by competitors in different states has had an impact on their customers. The task is to create a map that will allow me to look at the churn rate by state.

Insights discovered so far

The Churn rate is for Databel is on average 27%
45% of the reasons why customers churn is related to competitors
The Churn rate in California is abnormally high

The next stage is to analyze more columns, starting with the demographics of Databel.

Analyzing Demographics

The IF() method will be used to create a column with three age demography categories:

“Senior,”
“Under 30,”
and “Other.”



Demographics = IF('Databel - Data'[Under 30] = "Yes", "Under 30", IF('Databel - Data'[Senior] = "Yes", "Senior", "Other"))

The churn rate for senior citizens is higher than the average.

Analyzing Age groups

From the above visualization, the senior citizens churn more often. This suggests the need t have a more detailed look at the ages. Next, I will create combo chart visualizing the number of customers per bracket and their respective churn rates.

In general, the churn rate has an increasing trend through the age brackets. As the age increases the average churn rate for age brackets also increases.

Multiple field investigation

For this task, I will use the function SWITCH() which allows creation of a new column by assigning new results to the values in a column. We will group 3 different contract types into two for easy observation of yearly and monthly contracts. We will further analyze the churn rate based on gender.



Contract Category = SWITCH('Databel - Data'[Contract Type], "One Year", "Yearly", "Two Year", "Yearly", "Monthly")

Monthly contract customers churn more than yearly contract customers and the larger churning gender is females.

Group Consumption and Unlimited Plan

Databel has a hypothesis that people who are not on an unlimited data plan are more likely to churn. The task is to investigate this theory and prove if its accurate or not. I will also create a new column Grouped Consumption that categorizes the average monthly GB download into the following groups to determine whether it’s related to the amount of mobile data (GB) used:

Less than 5 GB.
Between 5 and 10 GB.
10 or more GB.

It appears that the hypothesis is incorrect and instead, customers who are on an unlimited plan are more likely to churn.

International Calls and Contract Types

Databel has a request to analyze the international activity of customers and its relationship to churn. They would like to know if paying for an international plan influences customer loyalty. This will also help us find out if customers without international plans are making international calls and vice versa.

The findings were that customers who pay for an international plan but do not make international calls had a very high churn rate. The recommendation to Databel would be to contact customers who have an international plan but have not made any international calls and suggest that they downgrade their plan.

Contract Type

It seems the churn rate decreases over time.

Summary Overview

The Churn rate is for Databel is on average 27%
45% of the reasons why customers churn is related to competitors
The Churn rate in California is abnormally high and this is due to campaigns launched by competitors inthe region
There is a higher churn rate from senior citizens
There is a higher churn rate from individual who have an international plan but have not made any international calls
Customers with a monthly contract type have a higher churn rate

The next phase would be to identify reasons for the churn rates inthe different categories and recommend solutions for Databel.

Thank you for reading

Author:
Gcinithemba Sherilyn Maphosa
Data Wired