This exercise took me about 20 minutes to complete, making it slightly more challenging than I had
intended.
I've accelerated the solution to speed it up and fast forward through some of the parts where I was
thinking the logic for the DAX can still take some thought.
So don't feel bad if you struggled, but hopefully you were able to work your way through some of the
necessary context challenges and you figured out a solution.
If not, hopefully this video will help you get past any hurdles and you can try to finish.
So let's go and get into it.
Step one is to simply push all of your data into the data model.
There were three different tables and clicking on each one, going to the power pivot ribbon and choosing
add data model will get you there.
One minor trick is to downsize the power pivot window and continue adding tables to your data model.
This can save you just a little bit of time.
Now, I'll create the calendar table and power pivot by going to design date table and choose a new.
I'll immediately add my quarter field as well, using the end of quarter function and then add it to
the date hierarchy.
Now we need to define the dimensional tables for this data set.
That means the claim types and policy types tables are coffee, the claim type in coverage field from
the claims table and remove duplicates.
And then I turn this into a table for the second.
I copy the policy type and remove those duplicates and turn this small subset into a table.
Once they're both tables and have renamed them, I add them to the data model.
Now I want to define the relationships between each buy tables.
On the design tab, under managed relationships, I'm going to go through and create the relationships
one at a time.
If you've played around with the diagram view, you've probably discovered that you can drag one field
to another to create some of these relationships.
However, I'll use the create command for each of them.
The only semi tricky relationship here is between the claims, the claim details, the policy types
and the policies.
I think the claims to the crime detail on the claim types and then the claim tied to the policy types
and then to the policies, if you skip this and used a single table between claims and policies, you'll
still get the same answers and measures.
Now I'm going to copy a single cell value from one of the tables and use the paste command along the
ribbon to create my underscore measures table.
This will be the table that I load all of my measures into to keep them organized.
Let's create a hierarchy.
I'm going to add a column with all to my policy types table.
Once I do that, I'll refresh the data, create a hierarchy and add my policy type and policy group
to that.
You could do the same for your clients detail, but it's not entirely necessary.
I'm going to build the basic measures that will support our report at this point, the first few premium's
wages and claim payments are simply the sum values for the amount fields for each of their respective
tables will be editing these again later.
I'll also create some supporting measures, the expenses, which is the wages, plus the claim payments
premiums minus expenses as cash flow, and then the combined ratio, which is the division of expenses
into premium.
The next set of measures that I'll create will be cumulative measures for each of our basic measures.
I only write out the first one, the premiums calculation in its entirety.
From there, I copy that formula and modify it for the remaining measures, since they all follow that
pattern, whatever.
I refer to a DACS pattern.
This is what I mean.
The logical structure is almost perfectly consistent at that level.
However, the measure to which it is applied is different and may have its own special logic, as we'll
see later with this exercise.
Once I get to the cumulative combined ratio, I'm back to writing a custom formula, redivide my cumulative
expenses into my cumulative premiums.
It's unrealistic to build this report start to finish without actually looking at it, these initial
measures are obvious first steps and the easiest way to figure out the next step is to start laying
out the pivot table report and looking for the places where the context doesn't quite fit.
I'm going to create a pivot table based upon this policy type, since I haven't really contemplated
it yet with any of my measures and I'll add all of my measures to the table.
The first thing that jumps out to me is that the wages value has no relationship to the policy type.
This impacts other measures along the way.
And if you look back at the assumptions, I requested that wages be distributed between policy types
based upon how many of each policy type was written.
So it's time to make some more measures.
To allocate the wages, I'm going to use an approach similar to what I used in the advanced measures
lesson, I'm going to create two measures to determine the appropriate ratio of wages to use at the
allocated level.
The first measure is easy.
It's simply the count of the policies.
The second measure will filter this count, but it is going to ignore the filter on policy type.
I do this by using the calculator function and rather than in an all except approach, I take all of
the policies and then I reapply the calendar filter.
Once I have these measures in place, I'll update my wages measure and multiply the sum of the wages
by the ratio of the policy cut to the policy count, all looking back at our pivot table.
This appears to solve our wages issue.
Let's add our date hierarchy and look around a little bit more.
At this stage, everything looks pretty good.
However, we have a few special conditions that we need to consider.
Notably, our instruction state that premiums are to be paid in full three months after the effective
date of the policy, which means it's time to edit our premiums measure.
The measure we need doesn't quite fall exactly into the examples that I use during the course, it's
closest cousin is the prior month expenses calculated for our KPI in the advanced measures lessen for
the premiums.
We want to modify the context of our computations such that the premiums paid on, say, March 20th
were billed on the date three months earlier.
To think about this in steps I need to unfelt for my calendar table using all, once I've done that,
I want to refill it based upon the date from three months ago, following between the bounds of my originally
filtered calendar table.
That sounds awfully complicated, but what I'm doing is I'm adding three months to each calendar date
that I'm comparing that to the maximum calendar date and to the minimum calendar date from our context
and taking only those dates that fall between.
So imagine we have a policy with the January 20th premium payment and we're going to add three months
to that to get to April 20th.
Whatever my context encapsulates April 20th, then that premium will be included in the sum taking a
brief look at our premiums by date, it looks like they've been pushed forward by three months as we
intended.
Most of the fields that we have loaded aren't necessary for any of our questions.
So I'll clear them out and we can begin building the KPIs before we go tackle that quiz.
The first KPI I'll build is the simple one that I used for the combined ratio.
This one compares our percentage based combined ratio against a fixed number.
In this case, my target value is one hundred percent and I'm going to use my combined ratio cumulative
so I can see it as the losses accumulate.
I set my tolerances to 90 percent and ninety five percent and I select the green, yellow, red so that
my good is less than the 90 percent.
And then I realized after checking my assumptions, that this should have been ninety five percent and
one hundred percent.
So I go and correct that.
Once I've done that, I update the pivot table to properly include the status and sent to this column.
The second and final copy I'll build is my cash on hand indicator.
This indicator wants to look at how our cash on hand compares to our expense costs in the prior month.
In order to make this KPI, then I need a supporting measure, which I literally call the cash on hand
KPI measure.
I use the same pattern that I used for my moving one month average from the advanced measures lesson
to do this.
Now let's discuss how this is different from our premium's measure.
The premiums measure has a variable time size, that is, I want the premiums added together that fall
within the given context.
So that could mean a day, a week, a month or a year's worth of premium.
Thus it uses both the maximum and minimum calendar dates in constructing that range.
For this measure, I want a fixed time range that is always exactly one month in size.
So rather than varying by comparison point, I always use the maximum date and then I compare this to
the dates with one month added and two months added.
This means that my context, whether it's one day or one year, will always pull the last month worth
of information.
Once I've created the supporting measure, the KPI is only a few quick steps away.
The only difference from before is that now I'll reference the cash on hand KPI measure instead of an
absolute value.
I'll go check the assumptions to see what the proper tolerances should be before going and setting them
at 10 and 20 percent once I've done this and added the status to the table.
All slightly reorganized the pivot table and now I'm ready to tackle the quiz, the following video
will show how I navigate through the pivot table to answer the questions posed.