Power BI Case Study

MSQI
Insurance Company
• This data set is based on real‐life data from a
national insurance company. All data has
been anonymized to preserve privacy.
• Data set contains sales data for 2017
• In this tutorial, we are using a small, 24‐
record data set
• For the actual activity, you will be using the
full data set so the answers will be different
but the process will be similar
The data set we are going to use for this activity is based on real‐life data from a national
insurance company. All data has been anonymized to preserve privacy. The main data set
contains sales data for 2017. For this tutorial video, we are using a small 24‐record data set.
For the actual activity, you will be using the full data set, so the answers will be different,
but the process will be similar. The insurance types or data in the tutorial may be different
than the full data set, but the fields are all the same.
2
General instructions
You will be using the Excel file provided to
analyze the data in Power BI. Step‐by‐step
instructions using the tutorial data set follow
for each of the requirements for the
assignment. You will be starting in Excel to
clean the data set and then you will open the
file in Power Bi.
This assignment will give you a broad
overview of the functions in Power BI.
You will be using the Excel file provided to analyze the data in Power BI. Step‐by‐
step instructions using the tutorial data set follow for each of the requirements for
the assignment. You will be starting in Excel to clean the data set and then you will
open the file in Power Bi.
This assignment will give you a broad overview of the functions in Power BI.
3
Data worksheet name
The Excel data file for this project contains
multiple worksheets. One of them contains sales
data including Region, State, Salesperson,
Insurance Type, and so on. That is your main data
sheet. Here in this tutorial it is called “Main” but
the main data sheet might be called “Data” or
“MainData” or something similar. Just make note
of the worksheet name in your data set and
substitute that name wherever I use “Main” in
this tutorial.
The Excel data file for this project contains multiple worksheets. One of them
contains sales data including Region, State, Salesperson, Insurance Type, and so on.
That is your main data sheet. Here in this tutorial it is called “Main” but the main
data sheet might be called “Data” or “MainData” or something similar. Just make
note of the worksheet name in your data set and substitute that name wherever I
use “Main” in this tutorial.
4
Start by opening Excel workbook
Start by opening Excel file and click on
the Main worksheet
Start by opening the Excel workbook.
5
Requirement 1
Find and correct errors in the Region
and Insurance Type fields
The first requirement is to find and correct errors in the Region and Insurance Type fields.
6
Req 1: Find and correct errors
#1: Click on any cell in the data in the
Main worksheet
Click on any cell in the data in the Main worksheet. Remember that yours might be called
something different. If so, just substitute that worksheet name wherever I refer to “Main.”
7
#2: On the Insert ribbon, click on Pivot
Table
On the Insert ribbon in Excel (across the top of the window), click on Pivot Table.
8
#3: Accept the defaults to insert the
Pivot Table
Accept the defaults to insert the pivot table in the workbook.
9
#4: Right‐click the
worksheet tab to
rename it as “Req 1”
Before going any further, right‐click the worksheet tab and rename it as “Req 1.”
10
#5: Drag “Region” in
the Pivot Table Fields
panel down to the
Rows box and “Sales”
down to the Values
box
Next, we will populate the pivot table. In the Pivot Table Fields panel, drag “region” down
to the Rows box and “Sales” down to the Values box.
11
#6: Examine the list of
Regions to find any
error(s)
You now have a pivot table. Examine the list of Regions to find any error(s).
12
One of the regions is
misspelled
Here we see that one of the regions is misspelled. Remember that this is a small tutorial
data set. In the real data set, there may be multiple, different errors.
13
#7: Click on the Main tab
to go back to the data
Now we want to correct the original data set for the spelling error. Click on the Main tab to
go back to the original data.
14
#8: In the Home ribbon, click on Find & Select.
Type the incorrect term in “Find what” box and
the correct spelling in “Replace with” box. Click
on Replace All.
In the Home ribbon, click on Find & Select. Type the incorrect term in the “Find what” box
and the correct spelling in the “Replace with” box. Click on Replace All.
15
#9: Go back to the Req
1 worksheet, right‐
click in the pivot table,
and select Refresh
Now we need to refresh the pivot table data to reflect the corrections. Go back to the Req.
1 worksheet, right‐click within the pivot table data, and select Refresh.
16
You can see that the
incorrect region is no
longer in the data set
You can see that the incorrect region term is no longer present in the data set.
17
By the way, if this panel
ever disappears, you
can bring it back by
clicking anywhere in
the pivot table you
created.
By the way, if the PivotTable Fields panel ever disappears, you can bring it back by clicking
anywhere in the pivot table you created.
18
#10: Now repeat the
process for the
Insurance Type field.
Drag “Region” back
up to the main box
and drag “Insurance
Type” down to the
Rows box.
Now repeat the process for the Insurance Type field. Drag “Region” back up to the main
box and drag “Insurance Type” down to the Rows box.
19
#11: Review the list of
Insurance Types to find the
error(s). Correct those errors
by following the process we
just used to correct the
error(s) in Region.
Review the list of Insurance Types to find the error(s). Correct those errors by following the
process we just used to correct the error(s) in Region.
20
All errors have been
corrected now. You
can save your Excel
file and close Excel
now.
Once the errors are corrected and the pivot table is refreshed, we can see that the
incorrect insurance types are no longer present in the data set. You can save your Excel
file and close Excel now.
21
Rest of project is in Power BI…
Start by opening Power BI
The rest of this project is in Power BI, so start by opening Power BI.
22
Get data in Power BI
Click on Get Data
Click on get data.
23
Click on Excel
and then
Connect.
*Notice there
are several
types of data
files that
Power BI can
open.
Click on Excel and then Connect.
*Notice there are several types of data files that Power BI can open.
24
Navigate to the data file that you
saved in Requirement 1 and click
on Open.
Navigate to the data file that you saved in Requirement 1 and click on Open.
25
Select the sheets you
want to import. Be sure
to click in the box next
to the worksheet you
want to import. We will
import the Main sheet
and VariableCostPct
sheet for this project.
Click Load.
Select the sheets you want to import. Be sure to click in the box next to the
worksheet you want to import. We will import the Main sheet and VariableCostPct
sheet for this project. Click Load. Remember that your main data worksheet might be
called something different. If so, just substitute that worksheet name wherever I refer to
“Main.”
26
Take a moment to save your
project. Click on File, Save As.
Take a moment to save your project. Click on File, Save As.
27
Brief tour of Power BI
Power BI has three modes: Report,
Data, and Relationships.
Let’s take a moment to get familiar with Power BI. Power BI has three modes: Report, Data,
and Relationships.
28
The report mode is where you
create visual information such as
tables and graphs.
The report mode is where you create visual information such as tables and graphs.
29
The data mode contains the data
that you imported.
The data mode contains the data that you imported.
30
The relationship mode allows you
to do advanced analysis using
multiple tables.
The relationship mode allows you to do advanced analysis using multiple tables.
31
Requirement 2
Calculate the variable cost and
contribution margin for each policy
sold.
For the second requirement, we will be calculating the variable cost and contribution
margin for each policy sold.
32
Req 2: Calculate VC and CM
#1: Click on the table mode. We are going to
delete the “Variable Cost Percentage”,
“Variable Cost”, and “Contribution Margin”
columns and create new columns.
Click on the table mode. We are going to delete the “Variable Cost Percentage”,
“Variable Cost”, and “Contribution Margin” columns and create new columns.
33
#2: In the fields menu, move
your mouse to the far right
corner of the “Contribution
Margin” field until you see the
“…”. Click on the “…” and the
menu opens.
In the fields menu, move your mouse to the far right corner of the “Contribution
Margin” field until you see the “…”. Click on the “…” and the menu opens.
34
#3: Click Delete.
Click Delete.
35
#4: Click Delete to confirm.
Click Delete to confirm.
36
#5: Complete the same
process for “Variable Cost
Percentage” and “Variable
Cost” to delete those
columns.
Complete the same process for “Variable Cost Percentage” and “Variable Cost” to
delete those columns.
37
#6: Click on New Column.
We need to add the variable cost percentage to the Main data table. Click on New Column.
38
#7: Name the column by typing
in the equation bar the
following:
Variable Cost Percentage =
Name the column by typing in the equation bar the following: Variable Cost
Percentage =
39
#8: Continue typing after the = :
LOOKUPVALUE(VariableCostPct[Variable Cost
Percent], VariableCostPct[State Type], Main[State
Type])
The variable cost percentage is in the VariableCostPct table. To get the variable cost
percentage for each sale, we will use the LOOKUPVALUE function. The formula is =
LOOKUPVALUE(VariableCostPct[Variable Cost Percent], VariableCostPct[State Type],
Main[State Type]). We are going to walk through the elements of that formula next.
40
= LOOKUPVALUE(VariableCostPct[Variable Cost
Type])
Let’s look at that LOOKUPVALUE formula that we just typed into the column.
41
The name of the existing
column that contains the
value we want to return.
Type])
After typing in =LOOKUPVALUE, the first term is the name of the existing column that
contains the value we want to return. We are looking for the variable cost percent
from the variable cost pct table.
42
Type])
The state type that
corresponds with the
Variable Cost Percent in the
VariableCostPct table.
The next term is the state type that corresponds with the Variable Cost percent in the
43
Type])
The state type that The state type from the
corresponds with the Main table.
Variable Cost Percent in the
The last term is the state type from the Main table. Remember that sales data worksheet
might be called something different. If so, just substitute that worksheet name for “Main.”
44
#9: Click on “New Column.”
Next, we move on to calculate the variable cost. Click on New Column.
45
#10: In the equation bar type the following:
Variable Cost = Main[Sales] * Main[Variable
Cost Percentage]
In the equation bar type the following:
Variable Cost = Main[Sales] * Main[Variable Cost Percentage]
Again, if your sales data worksheet is named something other than “Main,” substitute that
worksheet name here for “Main.”
46
#11: Click on the Modeling tab.
Let’s take a moment to format the variable cost. With the variable cost field
highlighted, click on the modeling tab.
47
#12: Click on Format and then Currency. Select
the $ English (United States).
Click on Format and then Currency. Select the $ English (United States).
48
#13: Click on “New
Column.”
Next, we move on to calculate the contribution margin. Click on New Column.
49
Contribution Margin = Main[Sales] ‐
Main[Variable Cost]
Contribution Margin = Main[Sales] ‐ Main[Variable Cost]
50
#15: Click on the Modeling tab.
Let’s take a moment to format the contribution margin. With the contribution
margin field highlighted, click on the modeling tab.
51
#16: Click on Format and then Currency. Select
the $ English (United States).
Click on Format and then Currency. Select the $ English (United States).
52
This is Requirement 2.
Now we are done with this step.
53
Requirement 3
Analyze contribution margin by
Insurance Type.
For the third requirement, we will be analyzing the contribution margin by Insurance Type.
We will be covering all four parts to Requirement 3 coming up.
54
Req 3a&b: Analyze CM by Insurance Type
#1: Click on the Sales column.
Let’s begin by formatting the Sales column and the Variable Cost Percentage. Let’s
start with the Sales column. Click on the Sales column.
55
#2: On the modeling tab, click on Format and
then Currency. Select the $ English (United
States).
On the modeling tab, click on Format and then Currency. Select the $ English
(United States). Now let’s format the Variable Cost Percentage.
56
#3: Click on the Variable Cost Percentage column.
We will format the Variable Cost Percentage as a percent. Click on the Variable Cost
Percentage column.
57
#4: On the Modeling tab, click on Format and
then Percentage.
On the Modeling tab, click on Format and then Percentage.
58
#5: On the Modeling tab, change the decimal
places to zero by clicking the down arrow.
We will also change the decimal places to zero. On the Modeling tab, change the
decimal places to zero by clicking the down arrow.
59
#6: Click on Report Mode. Right‐
click on Page 1, and Rename Page
to Req3.
To complete requirement 3, we will use the Report mode. Click on Report Mode.
Right‐click on Page 1, and Rename Page to Req3.
60
#7: In
Visualizations,
click on Table.
In Visualizations, click on Table.
61
#8: In Fields, click on (in this
order) Insurance Type, Sales,
Variable Cost, and
Contribution Margin.
In Fields, click on (in this order) Insurance Type, Sales, Variable Cost, and
Contribution Margin.
62
#9: Under visualizations, click
the Format tool.
I’d like this table to be a little bigger so it is easier to see. I can adjust the formatting
of a visualization under visualizations, by clicking the Format tool.
63
#10: Click on Grid, scroll
down, and adjust Text Size.
There are a lot of different options here to change the way the visualization
appears. To adjust the font size, I’ll open up the Grid menu, scroll down, and adjust
the Text Size.
64
#11: Resize the table by
moving curser to the lower
right corner until you see the
double arrow. Click and drag
the table to the size needed.
I still can’t see the entire table still. I will adjust it by moving my curser to the lower
right corner until I see the double arrow. I’ll click and drag the table to the size I
would like.
65
#12: Click on the Contribution
Margin heading to sort by
total from highest to lowest.
Click on the Contribution Margin heading to sort by total from highest to lowest.
66
This answers requirement 3a and 3b.
This answers requirement 3a and 3b.
67
Req 3c: Analyze CM by Insurance Type
#13: Drag Sales over into the
Values area.
For requirement 3c, we need to know the number of each insurance policies sold by
type. We will add an additional column to our table for this. With the table
selected, drag Sales over into the Values area.
68
Req 3: Analyze CM by Insurance Type
#14: In values, click on the
arrow next to the second
Sales field.
For the new Sales column, we will change the value from sum or total to count. In
values, click on arrow next to the second Sales field.
69
#15: Select Count.
Select count.
70
This answers
requirement 3c.
This answers requirement 3c.
71
#16: Drag Contribution
Margin over into the Values
area.
For requirement 3d, we need to know determine the average contribution margin
per policy in each insurance type. We will add an additional column to our table for
this. With the table selected, drag Contribution over into the Values area.
72
#17: In values, click on
the arrow next to the
second Contribution
Margin field.
For the new Contribution Margin column, we will change the value from sum over
to average. In values, click on arrow next to the second Contribution Margin field.
73
#18: Select Average.
Select average.
74
This answers
requirement 3d.
This answers requirement 3d.
75
Requirement 4
Calculate the contribution margin ratio
for each policy. Rank the Insurance Type
field from the highest contribution
margin ratio to lowest contribution
margin ratio. Do these rankings agree
with the rankings you found in
Requirement 3? Should these two
rankings always be the same? Explain.
For the fourth requirement, we will be calculating the contribution margin ratio for each
policy and then analyzing insurance types using the contribution margin ratio.
76
Req 4: Analyze CM ratio by Insurance Type
#1: Click on Data mode.
For requirement 4, we will need to calculate the contribution margin ratio. We do this in
the Data mode. Click on the Data mode.
77
#2: On the Home tab, click
on “New Measure.”
Next, we will calculate the contribution margin. On the Home tab, click on New Measure.
We will use a measure because we want to calculate the contribution margin on an
aggregated level (by total insurance type).
78
Contribution Margin Ratio = Divide ( sum (
Main[Contribution Margin] ), sum
(Main[Sales]))
Contribution Margin Ratio = Divide ( sum ( Main[Contribution Margin] ), sum (Main[Sales]))
Again, remember to substitute the worksheet name for your sales data if it is
different from “Main.”
79
We are dividing
Contribution Margin Ratio =
Divide ( sum ( Main[Contribution Margin] ),
sum (Main[Sales]))
Let’s talk a moment to look at this formula. We are dividing
80
We are dividing the total of
contribution margin from the
main table
sum (Main[Sales]))
the total of contribution margin from the main table
81
We are dividing the total of
contribution margin from the
main table by the total of sales
from the main table.
sum (Main[Sales]))
by the total of sales from the main table.
82
#4: With the Contribution
Margin Ratio measure
selected, click on the
Modeling tab.
Let’s take a moment to format the contribution margin ratio. With the Contribution
Margin ratio measure selected, click on the Modeling tab.
83
#5: Click on Format and Percentage.
Click on Format and then Percentage.
84
#6: On the Modeling tab, change the decimal
places to zero by clicking the down arrow.
We will also change the decimal places to zero. On the Modeling tab, change the
decimal places to zero by clicking the down arrow.
85
#7: On Report Mode, click on the
“+” and Rename Page to Req4.
Now we are ready to create the report. Click on Report Mode. Click on the “+” and
Rename Page to Req4.
86
#8: In
Visualizations,
click on Table.
87
order) Insurance Type and
Contribution Margin Ratio.
In Fields, click on (in this order) Insurance Type and Contribution Margin Ratio.
88
#10: Click on the contribution
Margin Ratio heading to sort
from highest to lowest.
Click on the contribution Margin Ratio heading to sort from highest to lowest.
89
Now the data is sorted from largest to
smallest. How does it compare to Req. 3?
Now the data is sorted from largest to smallest. How does it compare to Requirement 3?
90
Requirement 5
Calculate the contribution margin
ratio for each state. Rank the states
from the highest contribution
margin ratio to the lowest
contribution margin ratio. Which
states had a contribution margin
ratio greater than 75%?
For the fifth requirement, we will be analyzing the contribution margin ratio by state.
91
Req 5: Analyze CM ratio by State
Click on Report Mode. Click on the “+” and Rename Page to Req5.
92
#2: In
Visualizations,
click on Table.
93
order) State and Contribution
Margin Ratio.
In Fields, click on (in this order) State and Contribution Margin Ratio.
94
#4: Click on the contribution
Margin Ratio heading to sort
from highest to lowest.
Click on the Contribution Margin Ratio heading to sort from highest to lowest.
95
Now the report is sorted from
highest to lowest contribution
margin.
Now the report is sorted from highest to lowest contribution margin.
96
#6: With the table selected,
under filters, click on the
arrow next to contribution
margin ratio.
To identify with states had a contribution margin ratio greater than 75%, we will use
the filters. With the table selected, under filters, click on the arrow next to
contribution margin ratio.
97
#7: Select “is greater than”
and type “0.75”.
In the show items when the value is: Select “is greater than” and type “0.75”.
98
This filters the report to show
only those states with a CM
ratio greater than 75%.
This filters the report to show only those states with a CM ratio greater than 75%.
99
Requirement 6
Within each region, what was the
most profitable state in the most
recent year, as measured by the
contribution margin ratio? The least
most profitable state in each
region?
For the sixth requirement, we will be analyzing the contribution margin ratio of each state
within the regions.
100
Req 6: Analyze CM ratio by Region and State
Click on Report Mode. Click on the “+” and Rename Page to Req6.
101
#2: In
Visualizations,
click on Table.
102
order) Region, State and
Contribution Margin Ratio.
In Fields, click on (in this order) Region, State and Contribution Margin Ratio.
103
Power BI does not allow
the user to sort by
Region and State. We
will need to use the
slicer tool to find the
leading state in each
region.
Power BI does not allow the user to sort by Region and State. We will need to use
the slicer tool to find the leading state in each region.
104
#4: Click anywhere in
the white space.
#5: Click on the Slicer
visualization.
Click anywhere in the white space. Click on the Slicer visualization.
105
#6: To sort by regions,
select regions in the
field area.
To sort by regions, select regions in the field area.
106
#7: Select the region
you want to show.
Select the region you want to show.
107
#8: Then click on the
Contribution Margin
Ratio column to sort by
CM Ratio.
Do this for each region.
This answers Req 6.
Then click on the Contribution Margin Ratio column to sort by CM Ratio. Do this for
each region. This answers Req 6.
108

Power BI Case Study

Uploaded by

Copyright:

Available Formats

Power BI Case Study

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Power BI Case Study

Uploaded by

Copyright:

Available Formats

MSQI

You might also like