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.