1
00:00:00,920 --> 00:00:05,930
This exercise took me about 20 minutes to complete, making it slightly more challenging than I had

2
00:00:05,930 --> 00:00:06,440
intended.

3
00:00:06,890 --> 00:00:11,570
I've accelerated the solution to speed it up and fast forward through some of the parts where I was

4
00:00:11,570 --> 00:00:15,010
thinking the logic for the DAX can still take some thought.

5
00:00:15,230 --> 00:00:19,910
So don't feel bad if you struggled, but hopefully you were able to work your way through some of the

6
00:00:19,910 --> 00:00:23,540
necessary context challenges and you figured out a solution.

7
00:00:24,020 --> 00:00:29,040
If not, hopefully this video will help you get past any hurdles and you can try to finish.

8
00:00:29,780 --> 00:00:31,010
So let's go and get into it.

9
00:00:32,460 --> 00:00:36,430
Step one is to simply push all of your data into the data model.

10
00:00:36,840 --> 00:00:42,240
There were three different tables and clicking on each one, going to the power pivot ribbon and choosing

11
00:00:42,240 --> 00:00:44,310
add data model will get you there.

12
00:00:45,240 --> 00:00:50,730
One minor trick is to downsize the power pivot window and continue adding tables to your data model.

13
00:00:51,060 --> 00:00:53,040
This can save you just a little bit of time.

14
00:00:54,440 --> 00:01:00,290
Now, I'll create the calendar table and power pivot by going to design date table and choose a new.

15
00:01:01,350 --> 00:01:06,450
I'll immediately add my quarter field as well, using the end of quarter function and then add it to

16
00:01:06,450 --> 00:01:07,410
the date hierarchy.

17
00:01:08,400 --> 00:01:11,790
Now we need to define the dimensional tables for this data set.

18
00:01:11,820 --> 00:01:17,430
That means the claim types and policy types tables are coffee, the claim type in coverage field from

19
00:01:17,430 --> 00:01:19,770
the claims table and remove duplicates.

20
00:01:20,440 --> 00:01:23,460
And then I turn this into a table for the second.

21
00:01:23,460 --> 00:01:29,250
I copy the policy type and remove those duplicates and turn this small subset into a table.

22
00:01:29,820 --> 00:01:33,510
Once they're both tables and have renamed them, I add them to the data model.

23
00:01:34,290 --> 00:01:37,620
Now I want to define the relationships between each buy tables.

24
00:01:38,530 --> 00:01:44,200
On the design tab, under managed relationships, I'm going to go through and create the relationships

25
00:01:44,200 --> 00:01:45,040
one at a time.

26
00:01:45,920 --> 00:01:50,810
If you've played around with the diagram view, you've probably discovered that you can drag one field

27
00:01:50,810 --> 00:01:53,470
to another to create some of these relationships.

28
00:01:53,720 --> 00:01:56,210
However, I'll use the create command for each of them.

29
00:01:57,150 --> 00:02:03,930
The only semi tricky relationship here is between the claims, the claim details, the policy types

30
00:02:03,930 --> 00:02:04,830
and the policies.

31
00:02:06,100 --> 00:02:10,960
I think the claims to the crime detail on the claim types and then the claim tied to the policy types

32
00:02:11,140 --> 00:02:16,660
and then to the policies, if you skip this and used a single table between claims and policies, you'll

33
00:02:16,660 --> 00:02:18,520
still get the same answers and measures.

34
00:02:20,270 --> 00:02:25,310
Now I'm going to copy a single cell value from one of the tables and use the paste command along the

35
00:02:25,310 --> 00:02:27,930
ribbon to create my underscore measures table.

36
00:02:28,370 --> 00:02:32,450
This will be the table that I load all of my measures into to keep them organized.

37
00:02:33,440 --> 00:02:34,740
Let's create a hierarchy.

38
00:02:35,090 --> 00:02:38,720
I'm going to add a column with all to my policy types table.

39
00:02:38,990 --> 00:02:44,960
Once I do that, I'll refresh the data, create a hierarchy and add my policy type and policy group

40
00:02:44,960 --> 00:02:45,380
to that.

41
00:02:46,040 --> 00:02:49,660
You could do the same for your clients detail, but it's not entirely necessary.

42
00:02:50,790 --> 00:02:56,760
I'm going to build the basic measures that will support our report at this point, the first few premium's

43
00:02:56,760 --> 00:03:02,460
wages and claim payments are simply the sum values for the amount fields for each of their respective

44
00:03:02,460 --> 00:03:05,390
tables will be editing these again later.

45
00:03:06,210 --> 00:03:11,280
I'll also create some supporting measures, the expenses, which is the wages, plus the claim payments

46
00:03:12,090 --> 00:03:18,540
premiums minus expenses as cash flow, and then the combined ratio, which is the division of expenses

47
00:03:18,540 --> 00:03:19,320
into premium.

48
00:03:20,650 --> 00:03:25,870
The next set of measures that I'll create will be cumulative measures for each of our basic measures.

49
00:03:26,620 --> 00:03:30,490
I only write out the first one, the premiums calculation in its entirety.

50
00:03:30,970 --> 00:03:36,460
From there, I copy that formula and modify it for the remaining measures, since they all follow that

51
00:03:36,460 --> 00:03:38,110
pattern, whatever.

52
00:03:38,110 --> 00:03:39,670
I refer to a DACS pattern.

53
00:03:39,850 --> 00:03:40,860
This is what I mean.

54
00:03:41,230 --> 00:03:45,620
The logical structure is almost perfectly consistent at that level.

55
00:03:46,270 --> 00:03:51,310
However, the measure to which it is applied is different and may have its own special logic, as we'll

56
00:03:51,310 --> 00:03:52,870
see later with this exercise.

57
00:03:53,740 --> 00:03:59,770
Once I get to the cumulative combined ratio, I'm back to writing a custom formula, redivide my cumulative

58
00:03:59,770 --> 00:04:02,380
expenses into my cumulative premiums.

59
00:04:03,430 --> 00:04:09,310
It's unrealistic to build this report start to finish without actually looking at it, these initial

60
00:04:09,310 --> 00:04:14,740
measures are obvious first steps and the easiest way to figure out the next step is to start laying

61
00:04:14,740 --> 00:04:19,650
out the pivot table report and looking for the places where the context doesn't quite fit.

62
00:04:20,920 --> 00:04:26,020
I'm going to create a pivot table based upon this policy type, since I haven't really contemplated

63
00:04:26,020 --> 00:04:29,950
it yet with any of my measures and I'll add all of my measures to the table.

64
00:04:30,890 --> 00:04:35,960
The first thing that jumps out to me is that the wages value has no relationship to the policy type.

65
00:04:36,860 --> 00:04:38,950
This impacts other measures along the way.

66
00:04:39,380 --> 00:04:44,750
And if you look back at the assumptions, I requested that wages be distributed between policy types

67
00:04:45,020 --> 00:04:47,750
based upon how many of each policy type was written.

68
00:04:47,900 --> 00:04:49,640
So it's time to make some more measures.

69
00:04:51,850 --> 00:04:56,680
To allocate the wages, I'm going to use an approach similar to what I used in the advanced measures

70
00:04:56,680 --> 00:05:02,050
lesson, I'm going to create two measures to determine the appropriate ratio of wages to use at the

71
00:05:02,050 --> 00:05:03,120
allocated level.

72
00:05:03,850 --> 00:05:05,040
The first measure is easy.

73
00:05:05,230 --> 00:05:07,200
It's simply the count of the policies.

74
00:05:08,170 --> 00:05:13,600
The second measure will filter this count, but it is going to ignore the filter on policy type.

75
00:05:14,140 --> 00:05:19,840
I do this by using the calculator function and rather than in an all except approach, I take all of

76
00:05:19,840 --> 00:05:22,720
the policies and then I reapply the calendar filter.

77
00:05:23,350 --> 00:05:28,750
Once I have these measures in place, I'll update my wages measure and multiply the sum of the wages

78
00:05:28,900 --> 00:05:34,970
by the ratio of the policy cut to the policy count, all looking back at our pivot table.

79
00:05:34,990 --> 00:05:36,960
This appears to solve our wages issue.

80
00:05:37,450 --> 00:05:40,630
Let's add our date hierarchy and look around a little bit more.

81
00:05:41,770 --> 00:05:43,990
At this stage, everything looks pretty good.

82
00:05:44,140 --> 00:05:47,140
However, we have a few special conditions that we need to consider.

83
00:05:48,040 --> 00:05:53,740
Notably, our instruction state that premiums are to be paid in full three months after the effective

84
00:05:53,740 --> 00:05:57,370
date of the policy, which means it's time to edit our premiums measure.

85
00:05:58,560 --> 00:06:03,660
The measure we need doesn't quite fall exactly into the examples that I use during the course, it's

86
00:06:03,660 --> 00:06:10,500
closest cousin is the prior month expenses calculated for our KPI in the advanced measures lessen for

87
00:06:10,500 --> 00:06:11,290
the premiums.

88
00:06:11,310 --> 00:06:17,400
We want to modify the context of our computations such that the premiums paid on, say, March 20th

89
00:06:17,400 --> 00:06:19,710
were billed on the date three months earlier.

90
00:06:21,120 --> 00:06:27,120
To think about this in steps I need to unfelt for my calendar table using all, once I've done that,

91
00:06:27,120 --> 00:06:33,210
I want to refill it based upon the date from three months ago, following between the bounds of my originally

92
00:06:33,210 --> 00:06:34,520
filtered calendar table.

93
00:06:35,550 --> 00:06:40,950
That sounds awfully complicated, but what I'm doing is I'm adding three months to each calendar date

94
00:06:41,310 --> 00:06:47,220
that I'm comparing that to the maximum calendar date and to the minimum calendar date from our context

95
00:06:47,340 --> 00:06:49,580
and taking only those dates that fall between.

96
00:06:50,130 --> 00:06:55,650
So imagine we have a policy with the January 20th premium payment and we're going to add three months

97
00:06:55,650 --> 00:06:57,750
to that to get to April 20th.

98
00:06:58,320 --> 00:07:05,460
Whatever my context encapsulates April 20th, then that premium will be included in the sum taking a

99
00:07:05,460 --> 00:07:10,050
brief look at our premiums by date, it looks like they've been pushed forward by three months as we

100
00:07:10,050 --> 00:07:10,580
intended.

101
00:07:11,250 --> 00:07:15,360
Most of the fields that we have loaded aren't necessary for any of our questions.

102
00:07:15,390 --> 00:07:20,610
So I'll clear them out and we can begin building the KPIs before we go tackle that quiz.

103
00:07:21,690 --> 00:07:25,860
The first KPI I'll build is the simple one that I used for the combined ratio.

104
00:07:26,280 --> 00:07:30,720
This one compares our percentage based combined ratio against a fixed number.

105
00:07:31,140 --> 00:07:36,060
In this case, my target value is one hundred percent and I'm going to use my combined ratio cumulative

106
00:07:36,330 --> 00:07:38,940
so I can see it as the losses accumulate.

107
00:07:39,700 --> 00:07:45,940
I set my tolerances to 90 percent and ninety five percent and I select the green, yellow, red so that

108
00:07:45,940 --> 00:07:48,030
my good is less than the 90 percent.

109
00:07:48,460 --> 00:07:52,660
And then I realized after checking my assumptions, that this should have been ninety five percent and

110
00:07:52,660 --> 00:07:53,400
one hundred percent.

111
00:07:53,410 --> 00:07:55,330
So I go and correct that.

112
00:07:56,050 --> 00:08:01,900
Once I've done that, I update the pivot table to properly include the status and sent to this column.

113
00:08:02,290 --> 00:08:06,310
The second and final copy I'll build is my cash on hand indicator.

114
00:08:06,850 --> 00:08:12,310
This indicator wants to look at how our cash on hand compares to our expense costs in the prior month.

115
00:08:13,230 --> 00:08:18,810
In order to make this KPI, then I need a supporting measure, which I literally call the cash on hand

116
00:08:18,810 --> 00:08:19,770
KPI measure.

117
00:08:20,560 --> 00:08:25,470
I use the same pattern that I used for my moving one month average from the advanced measures lesson

118
00:08:25,480 --> 00:08:26,070
to do this.

119
00:08:27,130 --> 00:08:30,130
Now let's discuss how this is different from our premium's measure.

120
00:08:30,670 --> 00:08:36,940
The premiums measure has a variable time size, that is, I want the premiums added together that fall

121
00:08:36,940 --> 00:08:38,490
within the given context.

122
00:08:38,710 --> 00:08:42,430
So that could mean a day, a week, a month or a year's worth of premium.

123
00:08:43,510 --> 00:08:48,400
Thus it uses both the maximum and minimum calendar dates in constructing that range.

124
00:08:50,060 --> 00:08:55,070
For this measure, I want a fixed time range that is always exactly one month in size.

125
00:08:55,400 --> 00:09:01,640
So rather than varying by comparison point, I always use the maximum date and then I compare this to

126
00:09:01,640 --> 00:09:04,260
the dates with one month added and two months added.

127
00:09:04,760 --> 00:09:10,910
This means that my context, whether it's one day or one year, will always pull the last month worth

128
00:09:10,910 --> 00:09:11,770
of information.

129
00:09:12,470 --> 00:09:16,810
Once I've created the supporting measure, the KPI is only a few quick steps away.

130
00:09:17,300 --> 00:09:22,580
The only difference from before is that now I'll reference the cash on hand KPI measure instead of an

131
00:09:22,580 --> 00:09:23,650
absolute value.

132
00:09:24,410 --> 00:09:29,210
I'll go check the assumptions to see what the proper tolerances should be before going and setting them

133
00:09:29,210 --> 00:09:33,650
at 10 and 20 percent once I've done this and added the status to the table.

134
00:09:34,610 --> 00:09:40,370
All slightly reorganized the pivot table and now I'm ready to tackle the quiz, the following video

135
00:09:40,370 --> 00:09:44,480
will show how I navigate through the pivot table to answer the questions posed.
