Power BI Grade Predictions for Success
Budget: $30 – $250 USD
Module 4 Linear Regression Model Development using Power BI
Scenario:
You are a university student, and you are trying to understand the best ways to succeed. The professor has been offering study sessions to help the students and has asked each student to keep track of their time on the last assignment. Your data analytics professor has written a survey and asked each student to complete it.
The professor provides you with the data (attached) and asks you to analyze it, create visualizations, perform a linear regression on the grades and minutes spent on assignments, and write a letter to future students with advice on how to succeed.
Objectives:
Conduct Exploratory Data Analysis (EDA) to prepare data for further analysis.
Analyze data for relationships and/or trends using PowerBI.
Create three visualizations to explain the data.
Create a linear regression model to perform a prediction.
Write a letter to future students in your university providing them with data-driven advice.
Skills:
Data Visualization
Data Analysis
Linear Regression Analysis
Model interpretation
Critical Thinking
Communication
DAX Programming
Assignment Steps:
Consider your goals for the project.
After reading the scenario, how would you answer these questions?
How would you state the problem you are trying to solve?
What are the project goals?
What questions are you trying to answer?
Who is your audience?
Perform an exploratory data analysis.
Load the provided dataset into Power BI Desktop. If you are using the Virtual Lab (VDA) to access the Power BI desktop, follow the instructions- Loading files and Publishing Power BI in the UMGC Virtual Lab found in the classroom.
Click on the “Student Survey” worksheet and click Transform Data.
You should now be in Power Query Editor.
Look through each column (field).
Which fields are categorical?
Which fields have continuous values?
How many rows are in the dataset?
Create three visualizations.
Using your knowledge about data visualizations, create at least three visualizations that help to show or explain something significant about the data. Create a new page for each visualization. Do not create a visualization for the relationship between grade and minutes on assignment (you’ll do that next).
Create a linear regression.
Create a new page.
Create a scatterplot visualization with Total Minutes Spent on Assignment in the X-Axis and Grade in the Y-Axis.
Change the aggregation defaults from SUM to Don’t Summarize for both.
Look at the scatterplot created- what is a general statement you can say about this graph?
From the Analytics pane- add a Trend Line
You should now have a trend line. While this is visually helpful, you want more details so you can interpret the relationship between the two variables.
We will use Data Analysis Expressions (DAX) in Power BI to create a correlation coefficient calculation. This will produce a number that we can use to interpret how strongly linked two variables (fields) are.
In the “Home” menu, find “New Measure”.
Copy and paste the following DAX code to create a new field called “coeff corr”.
coeff corr =
//x̄
var __muX =calculate(AVERAGE('Student survey'[Total_Minutes_Spent]))
//ȳ
var __muY=calculate(AVERAGE('Student survey'[Grade]))
//numerator
var __numerator = sumx('Student survey',( 'Student survey'[Total_Minutes_Spent]-__muX)*('Student survey'[Grade]-__muY))
//denominator
var __denominator= SQRT(sumx('Student survey',('Student survey'[Total_Minutes_Spent]-__muX)^2)*sumx('Student survey',('Student survey'[Grade]-__muY)^2))
return divide(__numerator,__denominator)
Click the Check box. Then click the X to close the window.
You should now have a new field under your data menu.
Adjust your scatterplot to add a little room to the right of the graph.
Click the “Card” visualization next to your scatterplot.
Click and drag “coeff corr” you just made onto the card. You should now see the Correlation Coefficient.
What is the number it returned?
What does a correlation coefficient mean? Review the Week 7 Readings and Presentations to understand the correlation coefficient.
Create a linear regression model.
The linear regression model is one of the oldest statistical methods for predicting continuous variables. As you have done, we plotted two variables, x=time spent on assignment and y=grade, on a scatterplot, and a trend line was calculated to find the line closest to all the points plotted on the graph. The equation is written as y=a+bx.
Y is the output you want to know, X is the input, a is where the line crosses, and b is the slope of the line. The slope helps us to know the relationship between the two variables; in other words, what happens to the grade when the time spent on the assignment decreases or increases?
We are going to create this formula using DAX-calculated columns and measures.
Under the “Modeling” menu, select “New Column”. Copy and paste the DAX syntax below. Click the check mark to save each.
Math Formula DAX Formula
x² xsq =
'Student survey'[Total_Minutes_Spent]^2
xy xy =
'Student survey'[Total_Minutes_Spent]*'Student survey'[Grade]
Now, create the following measures we need to make and their DAX syntax. You will save each using the checkbox, then close the measure and create another new measure:
Math Formula DAX Formula
n n =
COUNTROWS('Student survey')
∑xy xysum =
SUM('Student survey'[xy])
∑x xsum =
SUM('Student survey'[Total_Minutes_Spent])
∑y ysum =
SUM('Student survey'[Grade])
∑x² xsqrsum =
SUM('Student survey'[xsq])
(n(∑xy)-(∑x)(∑y))/(n(∑x²)-(∑x)²) m (Slope) =
DIVIDE(
[n]*[xysum]-[xsum]*[ysum],
[n]*[xsqrsum]-[xsum]^2,
0
)
((∑y)(∑x^2)-(∑x)(∑xy))/(n(∑x²)-(∑x)²) b (Intercept) =
DIVIDE( [ysum]*[xsqrsum]-[xsum]*[xysum], [n]*[xsqrsum]-[xsum]^2,
0
)
Create a what-if parameter
Under the “Modeling” menu, click on “New parameter”.
Select the “numeric range” option.
Change the name to “Minutes Spent” (capitalize exactly to match the DAX in the next step). Create a maximum of 300 minutes.
You might now have a visualization that looks like this:
Complete the equation of the line and visualize.
Now, using the previously created components, create a Predicted grade measure containing the complete formula for the regression equation of a line y = mx + b.
Math Formula DAX Formula
y = mx + b Predicted Grade =
([m (Slope)]*
'Minutes Spent'[Minutes Spent Value]+
[b (Intercept)]
)
We can now insert a card visual in the report and select the measure Predicted Grade.
You should now have a visualization with a sliding schedule of the total minutes; the output is the predicted grade (see example below).
Write a letter to future students in class advising them how to use the linear regression output successfully.
Now that you have done all the data exploration, data preparation, visualizations, and linear regression analysis, you are ready to enter your findings into the Assignment 4 Letter to Future Student Template. You have been given a template to use, so follow all the instructions in that template.
You can take a screenshot of your visualizations from this assignment using a snipping tool.
Submit your assignment.
You will submit one file in LEO:
Assignment 4 Letter to Future Student (Your last name)
Scenario:
You are a university student, and you are trying to understand the best ways to succeed. The professor has been offering study sessions to help the students and has asked each student to keep track of their time on the last assignment. Your data analytics professor has written a survey and asked each student to complete it.
The professor provides you with the data (attached) and asks you to analyze it, create visualizations, perform a linear regression on the grades and minutes spent on assignments, and write a letter to future students with advice on how to succeed.
Objectives:
Conduct Exploratory Data Analysis (EDA) to prepare data for further analysis.
Analyze data for relationships and/or trends using PowerBI.
Create three visualizations to explain the data.
Create a linear regression model to perform a prediction.
Write a letter to future students in your university providing them with data-driven advice.
Skills:
Data Visualization
Data Analysis
Linear Regression Analysis
Model interpretation
Critical Thinking
Communication
DAX Programming
Assignment Steps:
Consider your goals for the project.
After reading the scenario, how would you answer these questions?
How would you state the problem you are trying to solve?
What are the project goals?
What questions are you trying to answer?
Who is your audience?
Perform an exploratory data analysis.
Load the provided dataset into Power BI Desktop. If you are using the Virtual Lab (VDA) to access the Power BI desktop, follow the instructions- Loading files and Publishing Power BI in the UMGC Virtual Lab found in the classroom.
Click on the “Student Survey” worksheet and click Transform Data.
You should now be in Power Query Editor.
Look through each column (field).
Which fields are categorical?
Which fields have continuous values?
How many rows are in the dataset?
Create three visualizations.
Using your knowledge about data visualizations, create at least three visualizations that help to show or explain something significant about the data. Create a new page for each visualization. Do not create a visualization for the relationship between grade and minutes on assignment (you’ll do that next).
Create a linear regression.
Create a new page.
Create a scatterplot visualization with Total Minutes Spent on Assignment in the X-Axis and Grade in the Y-Axis.
Change the aggregation defaults from SUM to Don’t Summarize for both.
Look at the scatterplot created- what is a general statement you can say about this graph?
From the Analytics pane- add a Trend Line
You should now have a trend line. While this is visually helpful, you want more details so you can interpret the relationship between the two variables.
We will use Data Analysis Expressions (DAX) in Power BI to create a correlation coefficient calculation. This will produce a number that we can use to interpret how strongly linked two variables (fields) are.
In the “Home” menu, find “New Measure”.
Copy and paste the following DAX code to create a new field called “coeff corr”.
coeff corr =
//x̄
var __muX =calculate(AVERAGE('Student survey'[Total_Minutes_Spent]))
//ȳ
var __muY=calculate(AVERAGE('Student survey'[Grade]))
//numerator
var __numerator = sumx('Student survey',( 'Student survey'[Total_Minutes_Spent]-__muX)*('Student survey'[Grade]-__muY))
//denominator
var __denominator= SQRT(sumx('Student survey',('Student survey'[Total_Minutes_Spent]-__muX)^2)*sumx('Student survey',('Student survey'[Grade]-__muY)^2))
return divide(__numerator,__denominator)
Click the Check box. Then click the X to close the window.
You should now have a new field under your data menu.
Adjust your scatterplot to add a little room to the right of the graph.
Click the “Card” visualization next to your scatterplot.
Click and drag “coeff corr” you just made onto the card. You should now see the Correlation Coefficient.
What is the number it returned?
What does a correlation coefficient mean? Review the Week 7 Readings and Presentations to understand the correlation coefficient.
Create a linear regression model.
The linear regression model is one of the oldest statistical methods for predicting continuous variables. As you have done, we plotted two variables, x=time spent on assignment and y=grade, on a scatterplot, and a trend line was calculated to find the line closest to all the points plotted on the graph. The equation is written as y=a+bx.
Y is the output you want to know, X is the input, a is where the line crosses, and b is the slope of the line. The slope helps us to know the relationship between the two variables; in other words, what happens to the grade when the time spent on the assignment decreases or increases?
We are going to create this formula using DAX-calculated columns and measures.
Under the “Modeling” menu, select “New Column”. Copy and paste the DAX syntax below. Click the check mark to save each.
Math Formula DAX Formula
x² xsq =
'Student survey'[Total_Minutes_Spent]^2
xy xy =
'Student survey'[Total_Minutes_Spent]*'Student survey'[Grade]
Now, create the following measures we need to make and their DAX syntax. You will save each using the checkbox, then close the measure and create another new measure:
Math Formula DAX Formula
n n =
COUNTROWS('Student survey')
∑xy xysum =
SUM('Student survey'[xy])
∑x xsum =
SUM('Student survey'[Total_Minutes_Spent])
∑y ysum =
SUM('Student survey'[Grade])
∑x² xsqrsum =
SUM('Student survey'[xsq])
(n(∑xy)-(∑x)(∑y))/(n(∑x²)-(∑x)²) m (Slope) =
DIVIDE(
[n]*[xysum]-[xsum]*[ysum],
[n]*[xsqrsum]-[xsum]^2,
0
)
((∑y)(∑x^2)-(∑x)(∑xy))/(n(∑x²)-(∑x)²) b (Intercept) =
DIVIDE( [ysum]*[xsqrsum]-[xsum]*[xysum], [n]*[xsqrsum]-[xsum]^2,
0
)
Create a what-if parameter
Under the “Modeling” menu, click on “New parameter”.
Select the “numeric range” option.
Change the name to “Minutes Spent” (capitalize exactly to match the DAX in the next step). Create a maximum of 300 minutes.
You might now have a visualization that looks like this:
Complete the equation of the line and visualize.
Now, using the previously created components, create a Predicted grade measure containing the complete formula for the regression equation of a line y = mx + b.
Math Formula DAX Formula
y = mx + b Predicted Grade =
([m (Slope)]*
'Minutes Spent'[Minutes Spent Value]+
[b (Intercept)]
)
We can now insert a card visual in the report and select the measure Predicted Grade.
You should now have a visualization with a sliding schedule of the total minutes; the output is the predicted grade (see example below).
Write a letter to future students in class advising them how to use the linear regression output successfully.
Now that you have done all the data exploration, data preparation, visualizations, and linear regression analysis, you are ready to enter your findings into the Assignment 4 Letter to Future Student Template. You have been given a template to use, so follow all the instructions in that template.
You can take a screenshot of your visualizations from this assignment using a snipping tool.
Submit your assignment.
You will submit one file in LEO:
Assignment 4 Letter to Future Student (Your last name)