Module 10: End of Module Project 1 | Peak Partners

1 00:00:02,399 --> 00:00:07,680 all right so this is module 10 in the

2 00:00:05,160 --> 00:00:10,019 module project one so make sure you

3 00:00:07,680 --> 00:00:12,179 change the name and download these files

4 00:00:10,019 --> 00:00:15,179 and then also read this these are some

5 00:00:12,179 --> 00:00:18,119 simple steps so okay so here is the main

6 00:00:15,179 --> 00:00:20,760 part so step one it is saying Roshana is

7 00:00:18,119 --> 00:00:22,380 a financial analyst for Peak Partners a

8 00:00:20,760 --> 00:00:24,539 full service okay you can skip the

9 00:00:22,380 --> 00:00:26,340 details so it is saying she wants to

10 00:00:24,539 --> 00:00:28,619 analyze revenue for the first year and

11 00:00:26,340 --> 00:00:31,199 project future sales for all the offices

12 00:00:28,619 --> 00:00:33,120 to create the report Unity input data

13 00:00:31,199 --> 00:00:35,520 from various sources and use the Excel

14 00:00:33,120 --> 00:00:37,320 power tools okay so it is saying go to

15 00:00:35,520 --> 00:00:39,960 the revenue history worksheets on the

16 00:00:37,320 --> 00:00:41,879 bottom click click here and then where

17 00:00:39,960 --> 00:00:44,340 she wants to view our sales substance is

18 00:00:41,879 --> 00:00:46,020 the business was founded okay she has a

19 00:00:44,340 --> 00:00:48,420 text file that already contains this

20 00:00:46,020 --> 00:00:51,480 data okay create a new query that

21 00:00:48,420 --> 00:00:53,640 Imports data from this file so this is

22 00:00:51,480 --> 00:00:56,579 the text file.csb

23 00:00:53,640 --> 00:00:58,800 okay so for that go to data Tab and then

24 00:00:56,579 --> 00:01:01,800 click this great get data drop down and

25 00:00:58,800 --> 00:01:03,059 then from file and then from Text slash

26 00:01:01,800 --> 00:01:06,840 CSV

27 00:01:03,059 --> 00:01:09,299 and look at that file so in my case it

28 00:01:06,840 --> 00:01:11,400 is in downloads and yeah it is annual

29 00:01:09,299 --> 00:01:14,280 and then click this file and then click

30 00:01:11,400 --> 00:01:15,420 on import bottom

31 00:01:14,280 --> 00:01:18,119 um okay

32 00:01:15,420 --> 00:01:19,979 so let's read other thing so it is

33 00:01:18,119 --> 00:01:23,820 saying edit the query to remove the

34 00:01:19,979 --> 00:01:26,400 first four uh row of data and use the

35 00:01:23,820 --> 00:01:28,080 titles in the fifth row okay as the

36 00:01:26,400 --> 00:01:29,640 column reader so fifth rows will be

37 00:01:28,080 --> 00:01:32,820 column header

38 00:01:29,640 --> 00:01:34,740 close and load the query data to the

39 00:01:32,820 --> 00:01:38,340 table in cell B3 of the existing

40 00:01:34,740 --> 00:01:41,280 worksheet and apply this okay so

41 00:01:38,340 --> 00:01:42,780 to remove the first four row click on

42 00:01:41,280 --> 00:01:45,360 transform data

43 00:01:42,780 --> 00:01:47,520 and then

44 00:01:45,360 --> 00:01:49,860 it will bring us to the power query

45 00:01:47,520 --> 00:01:53,460 editor so after let's click on remove

46 00:01:49,860 --> 00:01:56,040 rows remove top rows so it was saying

47 00:01:53,460 --> 00:01:58,799 first four row so enter four as the

48 00:01:56,040 --> 00:02:02,399 number of rows and then click OK see it

49 00:01:58,799 --> 00:02:04,439 remove those rows and click here use

50 00:02:02,399 --> 00:02:06,960 first row as the editor

51 00:02:04,439 --> 00:02:09,420 so it will use this row as the header

52 00:02:06,960 --> 00:02:11,300 and then it was saying close and load to

53 00:02:09,420 --> 00:02:14,400 the cell B3 okay

54 00:02:11,300 --> 00:02:16,739 so there must be close F here it is

55 00:02:14,400 --> 00:02:20,239 click here and then it is saying close

56 00:02:16,739 --> 00:02:24,180 and load to click here

57 00:02:20,239 --> 00:02:27,300 so close and load to existing worksheet

58 00:02:24,180 --> 00:02:30,239 and then with cell B3

59 00:02:27,300 --> 00:02:32,340 okay here's the B3

60 00:02:30,239 --> 00:02:35,520 and then let's make sure so edit the

61 00:02:32,340 --> 00:02:38,340 query okay close and load all right so

62 00:02:35,520 --> 00:02:42,080 this is okay and it is not saying

63 00:02:38,340 --> 00:02:42,080 anything so let's click ok

64 00:02:42,120 --> 00:02:47,459 all right so annual is loaded

65 00:02:45,120 --> 00:02:49,500 now cancel this

66 00:02:47,459 --> 00:02:51,900 and it is saying I apply the dark green

67 00:02:49,500 --> 00:02:55,260 uh table style medium three okay so

68 00:02:51,900 --> 00:02:58,140 click here and then go to table design

69 00:02:55,260 --> 00:03:00,959 okay let's expand this so table design

70 00:02:58,140 --> 00:03:03,660 and then table is Styles and then it was

71 00:03:00,959 --> 00:03:07,260 saying dark green three

72 00:03:03,660 --> 00:03:09,959 and table style medium three instead

73 00:03:07,260 --> 00:03:14,480 okay so a dark green table style medium

74 00:03:09,959 --> 00:03:17,700 three let's see yeah that is okay so to

75 00:03:14,480 --> 00:03:20,459 to the new table to coordinate with logo

76 00:03:17,700 --> 00:03:22,739 all right so step two design Rosanna ask

77 00:03:20,459 --> 00:03:25,319 you to create a chart that computes the

78 00:03:22,739 --> 00:03:27,540 revenue of each business here create a

79 00:03:25,319 --> 00:03:32,280 scatter chart of the business here and

80 00:03:27,540 --> 00:03:36,300 annual review data so C3 through d25 so

81 00:03:32,280 --> 00:03:39,840 click on C3 and then scroll and then

82 00:03:36,300 --> 00:03:41,940 hold shift and click d25 so C3 through

83 00:03:39,840 --> 00:03:44,760 d25 is selected

84 00:03:41,940 --> 00:03:47,099 it is saying is scattered sound so for

85 00:03:44,760 --> 00:03:49,319 that go to insert Tab and then here is

86 00:03:47,099 --> 00:03:52,620 the chart section click on the drop down

87 00:03:49,319 --> 00:03:55,860 of scanner section and then scattered

88 00:03:52,620 --> 00:03:59,700 chart okay so click here now it is

89 00:03:55,860 --> 00:04:04,140 saying uh okay so resize

90 00:03:59,700 --> 00:04:07,519 uh in cell E3 and k16 all right

91 00:04:04,140 --> 00:04:11,640 so drag it to E3

92 00:04:07,519 --> 00:04:14,459 and then click here go to the corner and

93 00:04:11,640 --> 00:04:17,060 it is saying E3 through k16

94 00:04:14,459 --> 00:04:17,060 okay

95 00:04:17,160 --> 00:04:22,139 all right so this is k16 now it is

96 00:04:20,160 --> 00:04:23,940 saying add a linear forecast trend line

97 00:04:22,139 --> 00:04:26,460 to the Chart okay so further click on

98 00:04:23,940 --> 00:04:28,020 the side and then click on plus sign and

99 00:04:26,460 --> 00:04:30,180 here is the trend line

100 00:04:28,020 --> 00:04:33,060 click this arrow and it is saying linear

101 00:04:30,180 --> 00:04:35,340 forecast so click here and everything

102 00:04:33,060 --> 00:04:38,400 and then forecast the trend line forward

103 00:04:35,340 --> 00:04:40,620 two periods okay so for that click on

104 00:04:38,400 --> 00:04:44,160 more option and then this section will

105 00:04:40,620 --> 00:04:48,540 have here and then so forecast forward

106 00:04:44,160 --> 00:04:50,699 So currently it's zero edit e22 and then

107 00:04:48,540 --> 00:04:54,360 hit enter okay

108 00:04:50,699 --> 00:04:55,800 now you can close this all right so it

109 00:04:54,360 --> 00:04:57,180 is saying go to the monthly sales

110 00:04:55,800 --> 00:05:00,240 worksheet

111 00:04:57,180 --> 00:05:02,460 so here it is monthly sales where

112 00:05:00,240 --> 00:05:04,919 Rosanna has already put in one CCL from

113 00:05:02,460 --> 00:05:07,380 the text file and loaded it okay Sheena

114 00:05:04,919 --> 00:05:09,540 wants to forecast the monthly sale data

115 00:05:07,380 --> 00:05:13,380 for the next year click of forecast

116 00:05:09,540 --> 00:05:15,360 sheet based okay create a forecast sheet

117 00:05:13,380 --> 00:05:18,660 based on the data in the range B3

118 00:05:15,360 --> 00:05:20,220 through c27 so let's select B3 through

119 00:05:18,660 --> 00:05:24,419 c27

120 00:05:20,220 --> 00:05:27,900 click there hold sheet and c27 so

121 00:05:24,419 --> 00:05:29,580 for forecast sheet so just go to data

122 00:05:27,900 --> 00:05:32,460 Tab and then here is the forecast

123 00:05:29,580 --> 00:05:35,000 section and this is the forecast sheet

124 00:05:32,460 --> 00:05:35,000 okay

125 00:05:35,280 --> 00:05:43,199 so let's read forecast it from B3 to c27

126 00:05:39,419 --> 00:05:44,940 okay so forecast CL through uh okay 2022

127 00:05:43,199 --> 00:05:47,160 December 31

128 00:05:44,940 --> 00:05:50,580 so click on this Arrow Sun plus

129 00:05:47,160 --> 00:05:55,100 September October Edition so December 31

130 00:05:50,580 --> 00:05:55,100 2022 forecasting all right

131 00:05:55,639 --> 00:06:01,740 to 12 months okay so let's click on the

132 00:05:59,280 --> 00:06:04,800 options and then here this season I'll

133 00:06:01,740 --> 00:06:06,300 be click set manually and then edit it

134 00:06:04,800 --> 00:06:07,440 to 12 months

135 00:06:06,300 --> 00:06:11,100 okay

136 00:06:07,440 --> 00:06:14,400 so let's see 12 months and name the new

137 00:06:11,100 --> 00:06:16,199 action okay so now click on Create and

138 00:06:14,400 --> 00:06:19,139 then currently it's sheet1 so we need to

139 00:06:16,199 --> 00:06:21,360 rename it to 2022 forecast so right

140 00:06:19,139 --> 00:06:25,940 click and then rename

141 00:06:21,360 --> 00:06:29,400 okay and then quantity 22

142 00:06:25,940 --> 00:06:31,860 forecasters forecast okay now hit enter

143 00:06:29,400 --> 00:06:37,560 so it is saying remove and resize the

144 00:06:31,860 --> 00:06:42,120 forecasts are in C2 through e25 okay

145 00:06:37,560 --> 00:06:44,280 so here is the C oops Ctrl G okay

146 00:06:42,120 --> 00:06:47,520 make sure you select the chart and then

147 00:06:44,280 --> 00:06:50,880 drag it so C2

148 00:06:47,520 --> 00:06:53,100 through e25 let's see

149 00:06:50,880 --> 00:06:55,500 so this is the e

150 00:06:53,100 --> 00:06:58,319 sorry

151 00:06:55,500 --> 00:07:00,860 825

152 00:06:58,319 --> 00:07:00,860 okay

153 00:07:03,660 --> 00:07:10,800 is this okay this is 24 this is 25

154 00:07:08,039 --> 00:07:16,560 and then it will sing C2 so let's make

155 00:07:10,800 --> 00:07:16,560 sure this B2 and this g dog worksheet

156 00:07:17,160 --> 00:07:21,199 okay let's see what is the question

157 00:07:19,139 --> 00:07:23,400 paper down here

158 00:07:21,199 --> 00:07:25,560 worksheet and then it is saying Rosanna

159 00:07:23,400 --> 00:07:28,860 wants to display information about Peak

160 00:07:25,560 --> 00:07:31,139 Partners clients contracts and position

161 00:07:28,860 --> 00:07:33,900 she has been maintaining this data in an

162 00:07:31,139 --> 00:07:36,479 access access database create a new

163 00:07:33,900 --> 00:07:38,699 query in putting data from this database

164 00:07:36,479 --> 00:07:41,880 selecting all five table in the database

165 00:07:38,699 --> 00:07:45,120 for input okay so for that so let's

166 00:07:41,880 --> 00:07:47,099 expand this go to Power pivot Tab and

167 00:07:45,120 --> 00:07:50,400 then click on manage

168 00:07:47,099 --> 00:07:53,280 okay so let's see and it will open this

169 00:07:50,400 --> 00:07:55,680 window so power pivot window so get

170 00:07:53,280 --> 00:07:58,199 external data and then we need from

171 00:07:55,680 --> 00:08:01,020 database and it was saying from access

172 00:07:58,199 --> 00:08:05,280 database so click on from SS

173 00:08:01,020 --> 00:08:06,960 okay so click on browse so let's see so

174 00:08:05,280 --> 00:08:09,660 if you have downloaded then it must

175 00:08:06,960 --> 00:08:12,360 appear here so this this is the required

176 00:08:09,660 --> 00:08:14,819 file click here and then click open

177 00:08:12,360 --> 00:08:16,860 okay so let's see other details oops

178 00:08:14,819 --> 00:08:20,759 oops

179 00:08:16,860 --> 00:08:23,280 okay so just click on next

180 00:08:20,759 --> 00:08:25,080 and then select uh select from list of

181 00:08:23,280 --> 00:08:28,440 table and view okay so when it reaches

182 00:08:25,080 --> 00:08:31,139 so click on next okay so it was saying

183 00:08:28,440 --> 00:08:33,680 select all five tables so just click on

184 00:08:31,139 --> 00:08:38,159 this segment and it will select all the

185 00:08:33,680 --> 00:08:40,380 tables now Okay click finish

186 00:08:38,159 --> 00:08:45,080 all right so everything are transferred

187 00:08:40,380 --> 00:08:45,080 now it close oops close

188 00:08:45,120 --> 00:08:50,519 okay

189 00:08:47,580 --> 00:08:52,440 so let's see the instruction so it will

190 00:08:50,519 --> 00:08:55,380 not allow us to see the instruction let

191 00:08:52,440 --> 00:08:57,959 me close this window and do like this

192 00:08:55,380 --> 00:09:00,360 and then I'll log and click manage okay

193 00:08:57,959 --> 00:09:03,360 so it will open this my login

194 00:09:00,360 --> 00:09:05,459 all right so it is saying uh okay one

195 00:09:03,360 --> 00:09:08,160 liquid connection to the data and add

196 00:09:05,459 --> 00:09:09,899 data to the data model okay so use the

197 00:09:08,160 --> 00:09:13,500 pivot table window to add the pivot

198 00:09:09,899 --> 00:09:16,320 table to the positions all right now it

199 00:09:13,500 --> 00:09:19,200 is saying uh use the window to add the

200 00:09:16,320 --> 00:09:22,320 pivot table so here's the pivot table in

201 00:09:19,200 --> 00:09:25,800 cell B3 of the same of the positions we

202 00:09:22,320 --> 00:09:29,279 go to worksheet okay so pivot table

203 00:09:25,800 --> 00:09:30,660 and then existing and then in cell B3

204 00:09:29,279 --> 00:09:35,279 okay

205 00:09:30,660 --> 00:09:38,060 so let us delete ourselves so B and then

206 00:09:35,279 --> 00:09:41,160 three and then hit OK

207 00:09:38,060 --> 00:09:43,500 alrighty so step five it is saying add

208 00:09:41,160 --> 00:09:44,399 the category field from the positions

209 00:09:43,500 --> 00:09:46,380 table

210 00:09:44,399 --> 00:09:49,500 okay so let's see what is the position

211 00:09:46,380 --> 00:09:52,800 stable here it is so for instance table

212 00:09:49,500 --> 00:09:55,320 and then category field

213 00:09:52,800 --> 00:09:58,220 um okay into the value area so let's

214 00:09:55,320 --> 00:10:00,959 drag it to the value area

215 00:09:58,220 --> 00:10:03,360 all right so step six it is saying

216 00:10:00,959 --> 00:10:06,660 Roshana wants to view a position data

217 00:10:03,360 --> 00:10:08,940 organized as a hierarchy so maximize the

218 00:10:06,660 --> 00:10:12,540 power pivot window all right so let's

219 00:10:08,940 --> 00:10:16,080 close this and then click on manage to

220 00:10:12,540 --> 00:10:18,600 build our pivot window and then it is

221 00:10:16,080 --> 00:10:21,420 saying okay

222 00:10:18,600 --> 00:10:23,580 display the important imported table in

223 00:10:21,420 --> 00:10:26,459 the diagram view okay so click on

224 00:10:23,580 --> 00:10:28,920 diagram view Under The View group and

225 00:10:26,459 --> 00:10:30,720 then okay

226 00:10:28,920 --> 00:10:33,420 let's see

227 00:10:30,720 --> 00:10:35,519 diagram View and then add a hierarchy

228 00:10:33,420 --> 00:10:38,160 named position list to the positions

229 00:10:35,519 --> 00:10:40,860 table okay so copy this

230 00:10:38,160 --> 00:10:44,339 so let's go to the position table so

231 00:10:40,860 --> 00:10:46,440 here it is and it is saying edit okay so

232 00:10:44,339 --> 00:10:49,560 right click and then create a hierarchy

233 00:10:46,440 --> 00:10:52,860 click here and then so the name will be

234 00:10:49,560 --> 00:10:55,740 positions or position list and then hit

235 00:10:52,860 --> 00:10:58,200 enter okay so it will create it noticing

236 00:10:55,740 --> 00:11:00,899 add category group and position field to

237 00:10:58,200 --> 00:11:02,700 the position list Arabic okay so here is

238 00:11:00,899 --> 00:11:04,440 the category so simply click here and

239 00:11:02,700 --> 00:11:06,180 then drag it here

240 00:11:04,440 --> 00:11:10,579 drag it

241 00:11:06,180 --> 00:11:10,579 oops again drag it

242 00:11:11,040 --> 00:11:16,920 okay so let me maximize the design a

243 00:11:13,500 --> 00:11:20,300 category group and position field okay

244 00:11:16,920 --> 00:11:20,300 so category

245 00:11:21,000 --> 00:11:26,339 okay and then group

246 00:11:24,420 --> 00:11:28,920 under the category

247 00:11:26,339 --> 00:11:31,079 okay and then position

248 00:11:28,920 --> 00:11:34,019 under the category

249 00:11:31,079 --> 00:11:36,779 all that so this is done

250 00:11:34,019 --> 00:11:39,899 now okay

251 00:11:36,779 --> 00:11:42,000 so it is saying add these okay okay so

252 00:11:39,899 --> 00:11:44,459 return to the positions pivot worksheet

253 00:11:42,000 --> 00:11:46,200 so minimize it and then add the

254 00:11:44,459 --> 00:11:49,560 positional list hierarchy to the row

255 00:11:46,200 --> 00:11:52,440 area all right so click here

256 00:11:49,560 --> 00:11:54,540 and then go to pivot table analyze and

257 00:11:52,440 --> 00:11:57,300 then click on so and click on so freely

258 00:11:54,540 --> 00:12:00,000 so this section will appear after using

259 00:11:57,300 --> 00:12:03,839 add the position list hierarchy okay

260 00:12:00,000 --> 00:12:05,339 so here is the position list hierarchy

261 00:12:03,839 --> 00:12:08,839 and

262 00:12:05,339 --> 00:12:14,540 let's see I got okay so to the row area

263 00:12:08,839 --> 00:12:17,399 so drag it to the row area all right

264 00:12:14,540 --> 00:12:19,440 and then remove the position field from

265 00:12:17,399 --> 00:12:22,260 the rows if necessary so it's not here

266 00:12:19,440 --> 00:12:23,820 so we are good so step seven it is

267 00:12:22,260 --> 00:12:26,519 saying Rosanna wants to display the same

268 00:12:23,820 --> 00:12:28,620 position information as outside and then

269 00:12:26,519 --> 00:12:31,019 display only position in three group in

270 00:12:28,620 --> 00:12:33,420 the technology category okay so create a

271 00:12:31,019 --> 00:12:35,940 clustered bar keywords are based on the

272 00:12:33,420 --> 00:12:37,740 pivot table on the positions people to

273 00:12:35,940 --> 00:12:40,320 worksheet so let's close this section

274 00:12:37,740 --> 00:12:42,540 and then oops

275 00:12:40,320 --> 00:12:46,560 and then click here any cell inside it

276 00:12:42,540 --> 00:12:49,740 and then go to insert

277 00:12:46,560 --> 00:12:52,800 insert tab let's see so insert Tab and

278 00:12:49,740 --> 00:12:55,920 then go to Pivot chart click here

279 00:12:52,800 --> 00:12:58,980 and then let's see it is saying

280 00:12:55,920 --> 00:13:01,680 okay clustered bar keyboard set so it is

281 00:12:58,980 --> 00:13:03,899 column so go to the bar this is cluster

282 00:13:01,680 --> 00:13:06,839 bar and then click ok

283 00:13:03,899 --> 00:13:13,260 online so let's see if your position is

284 00:13:06,839 --> 00:13:16,019 D3 through I15 of it so here's the D3

285 00:13:13,260 --> 00:13:19,500 and then let's see click here then drag

286 00:13:16,019 --> 00:13:20,820 it I 15

287 00:13:19,500 --> 00:13:23,519 okay

288 00:13:20,820 --> 00:13:25,500 so I 15 it is saying drill down the bar

289 00:13:23,519 --> 00:13:27,240 chart through the technology category to

290 00:13:25,500 --> 00:13:30,480 display the number of positions in each

291 00:13:27,240 --> 00:13:33,779 Technology Group so click on technology

292 00:13:30,480 --> 00:13:36,180 and then expand and then puberty will

293 00:13:33,779 --> 00:13:37,079 lie and then click on drill drop or you

294 00:13:36,180 --> 00:13:39,540 can

295 00:13:37,079 --> 00:13:41,160 okay so this is the one way or you can

296 00:13:39,540 --> 00:13:43,440 control Z

297 00:13:41,160 --> 00:13:45,959 let's see and then click on technology

298 00:13:43,440 --> 00:13:48,060 and then double click and then it will

299 00:13:45,959 --> 00:13:50,820 drill down automatically

300 00:13:48,060 --> 00:13:53,040 that is the another way so or you can

301 00:13:50,820 --> 00:13:55,320 also Ctrl Z and then double click on it

302 00:13:53,040 --> 00:13:58,079 so there are different way just do

303 00:13:55,320 --> 00:14:00,660 whichever you find it's easy okay so I

304 00:13:58,079 --> 00:14:03,240 am from the future I realized I skipped

305 00:14:00,660 --> 00:14:05,399 this step so let me do that so it is

306 00:14:03,240 --> 00:14:08,399 saying hide the field bottom and listen

307 00:14:05,399 --> 00:14:09,779 Okay so field bottom analyzing so for

308 00:14:08,399 --> 00:14:12,720 that click on Plus

309 00:14:09,779 --> 00:14:15,600 and then field button so here is the

310 00:14:12,720 --> 00:14:17,160 Legend So on check mark this so for

311 00:14:15,600 --> 00:14:20,040 button click on the chart

312 00:14:17,160 --> 00:14:22,620 and then maximize it

313 00:14:20,040 --> 00:14:26,480 and then let's see so is the fill button

314 00:14:22,620 --> 00:14:26,480 just click here to unselect it

315 00:14:26,700 --> 00:14:31,500 so hide fill button and Legend and then

316 00:14:29,100 --> 00:14:33,360 move okay so we are good

317 00:14:31,500 --> 00:14:37,620 okay

318 00:14:33,360 --> 00:14:39,540 so step seven is done so step 8 go to

319 00:14:37,620 --> 00:14:41,579 the revenue by State Worksheet where

320 00:14:39,540 --> 00:14:44,420 Rosanna wants to view the recruiting

321 00:14:41,579 --> 00:14:47,220 Revenue by state and office okay so use

322 00:14:44,420 --> 00:14:48,899 use power people to insert pivot table

323 00:14:47,220 --> 00:14:53,639 in cell B3

324 00:14:48,899 --> 00:14:56,519 okay so firstly let's go to this and

325 00:14:53,639 --> 00:14:59,160 then use okay so use power we would go

326 00:14:56,519 --> 00:15:02,220 to here and then manage

327 00:14:59,160 --> 00:15:05,820 all right uh to insert a pivot table in

328 00:15:02,220 --> 00:15:07,740 cell B3 of the revenue register so click

329 00:15:05,820 --> 00:15:10,380 on pivot table and then existing

330 00:15:07,740 --> 00:15:12,300 worksheet and then Revenue by state and

331 00:15:10,380 --> 00:15:15,540 this time it's B

332 00:15:12,300 --> 00:15:17,880 3 and then click ok

333 00:15:15,540 --> 00:15:20,220 all right so it is saying add the State

334 00:15:17,880 --> 00:15:22,320 field to the office table okay so for

335 00:15:20,220 --> 00:15:24,899 that we need the section right here so

336 00:15:22,320 --> 00:15:27,720 for that go to Pivot analyze pivot table

337 00:15:24,899 --> 00:15:30,540 analyze and then so so field list so we

338 00:15:27,720 --> 00:15:33,420 need this pivot table Field section for

339 00:15:30,540 --> 00:15:35,880 that so it is saying add State field

340 00:15:33,420 --> 00:15:37,380 from the office table so let's see so

341 00:15:35,880 --> 00:15:40,560 here is the office table

342 00:15:37,380 --> 00:15:41,540 and then here is the state field right

343 00:15:40,560 --> 00:15:45,540 here

344 00:15:41,540 --> 00:15:47,940 so drag it to the row section

345 00:15:45,540 --> 00:15:50,339 and then it is saying add the type field

346 00:15:47,940 --> 00:15:53,220 of the office table so okay let's see

347 00:15:50,339 --> 00:15:55,440 how is the type so type field to the

348 00:15:53,220 --> 00:15:57,000 column area so drag it to the column

349 00:15:55,440 --> 00:16:00,120 area

350 00:15:57,000 --> 00:16:01,920 okay anything as add the charge field

351 00:16:00,120 --> 00:16:04,139 from the invoices table to the value

352 00:16:01,920 --> 00:16:06,899 area so let's see where the invoices

353 00:16:04,139 --> 00:16:09,120 table here it is expanded and then

354 00:16:06,899 --> 00:16:12,720 you're saying search here is the charge

355 00:16:09,120 --> 00:16:16,380 and then it is saying values area

356 00:16:12,720 --> 00:16:18,180 okay so we got this warning so let's see

357 00:16:16,380 --> 00:16:20,579 another step it is saying in order to

358 00:16:18,180 --> 00:16:23,040 relate the data in the offices and

359 00:16:20,579 --> 00:16:25,680 invoices table to make the proper

360 00:16:23,040 --> 00:16:27,779 comparison okay so it is addressing this

361 00:16:25,680 --> 00:16:30,480 warning so it is saying use the pivot

362 00:16:27,779 --> 00:16:32,760 table when power pivot window to create

363 00:16:30,480 --> 00:16:34,740 a relationship between the invoices and

364 00:16:32,760 --> 00:16:35,880 office table based on the office ID

365 00:16:34,740 --> 00:16:38,940 field

366 00:16:35,880 --> 00:16:42,360 okay so let's go to the power pivot

367 00:16:38,940 --> 00:16:46,320 window click on manage and then it is

368 00:16:42,360 --> 00:16:48,300 saying invoices and office table okay so

369 00:16:46,320 --> 00:16:51,420 right click on the office table and then

370 00:16:48,300 --> 00:16:53,100 click create relationship so it is

371 00:16:51,420 --> 00:16:55,620 saying to create relationship based on

372 00:16:53,100 --> 00:16:58,560 the office ID field so click on office

373 00:16:55,620 --> 00:17:01,440 ID and then click on this drop down of

374 00:16:58,560 --> 00:17:03,720 another section and the invoices

375 00:17:01,440 --> 00:17:07,439 okay here is your office ID and there's

376 00:17:03,720 --> 00:17:09,240 also office ID and then let's read

377 00:17:07,439 --> 00:17:11,459 okay

378 00:17:09,240 --> 00:17:14,220 so create a relationship okay so this is

379 00:17:11,459 --> 00:17:16,500 done click on OK

380 00:17:14,220 --> 00:17:18,720 all right so relationship is created now

381 00:17:16,500 --> 00:17:22,079 if we minimize this this one it should

382 00:17:18,720 --> 00:17:24,380 disappear and it is disappeared all

383 00:17:22,079 --> 00:17:27,480 right so step 10 it is

384 00:17:24,380 --> 00:17:29,940 also wants to focus on Revenue by month

385 00:17:27,480 --> 00:17:32,460 in the paper table insert a timeline

386 00:17:29,940 --> 00:17:35,580 slicer that uses the data field from the

387 00:17:32,460 --> 00:17:37,020 invoices table order so okay so let's

388 00:17:35,580 --> 00:17:40,020 close this

389 00:17:37,020 --> 00:17:42,299 and then it's saying timeline slicer so

390 00:17:40,020 --> 00:17:44,280 for that let's see

391 00:17:42,299 --> 00:17:46,799 uh okay

392 00:17:44,280 --> 00:17:49,260 so let's see in the insert

393 00:17:46,799 --> 00:17:51,600 so here is the slicer and click on the

394 00:17:49,260 --> 00:17:53,700 timeline slicer and then it was saying

395 00:17:51,600 --> 00:17:56,400 date from the invoices so select the

396 00:17:53,700 --> 00:17:58,860 date and then click ok

397 00:17:56,400 --> 00:18:01,080 all right so let's read the other thing

398 00:17:58,860 --> 00:18:03,480 and so it is saying use the timeline

399 00:18:01,080 --> 00:18:06,539 sizler to display the revenue from Mars

400 00:18:03,480 --> 00:18:08,460 to June of 2020 So currently it's

401 00:18:06,539 --> 00:18:13,860 showing us from

402 00:18:08,460 --> 00:18:16,980 okay 2020 to all the way to 2021 okay so

403 00:18:13,860 --> 00:18:21,299 but we just need to display from March

404 00:18:16,980 --> 00:18:25,679 of 2020 so let's drag it to the Mars

405 00:18:21,299 --> 00:18:28,980 March of 2020 to June okay

406 00:18:25,679 --> 00:18:31,799 so let's go here another and then

407 00:18:28,980 --> 00:18:34,140 another so March through Zoom okay

408 00:18:31,799 --> 00:18:36,600 so this is done and it is saying move

409 00:18:34,140 --> 00:18:38,400 and resize the time slicer okay B13

410 00:18:36,600 --> 00:18:41,539 through z19

411 00:18:38,400 --> 00:18:44,960 so B13

412 00:18:41,539 --> 00:18:47,160 through G

413 00:18:44,960 --> 00:18:50,580 19 already

414 00:18:47,160 --> 00:18:52,919 so last step it is saying Roshana wants

415 00:18:50,580 --> 00:18:55,620 to display the revenue by the state data

416 00:18:52,919 --> 00:18:58,140 in a in a map chart okay so copy the

417 00:18:55,620 --> 00:19:03,179 data in the not non-adjacent range B5

418 00:18:58,140 --> 00:19:05,760 through B10 so let's select B oops

419 00:19:03,179 --> 00:19:08,880 B5 through B10

420 00:19:05,760 --> 00:19:13,380 and then G5 resulted so select this and

421 00:19:08,880 --> 00:19:15,480 then hold Ctrl and then G5 through G10

422 00:19:13,380 --> 00:19:17,820 okay

423 00:19:15,480 --> 00:19:21,480 and then paste it in the paste it

424 00:19:17,820 --> 00:19:24,360 beginning in cell I5 so Ctrl C copy it

425 00:19:21,480 --> 00:19:28,080 and then skip to remove this round

426 00:19:24,360 --> 00:19:31,140 borders and then let's go to I5 sale so

427 00:19:28,080 --> 00:19:32,640 here it is and then paste it oops

428 00:19:31,140 --> 00:19:37,380 right click

429 00:19:32,640 --> 00:19:40,919 oops 18 copied okay so it was saying B5

430 00:19:37,380 --> 00:19:43,080 through B10 and then control click here

431 00:19:40,919 --> 00:19:45,840 and then shift click here or you can

432 00:19:43,080 --> 00:19:48,720 just drag it so copy

433 00:19:45,840 --> 00:19:52,039 now go to

434 00:19:48,720 --> 00:19:56,880 let's maximize it so we copied

435 00:19:52,039 --> 00:20:00,840 and then i5 and then paste all right

436 00:19:56,880 --> 00:20:02,460 so it is saying resize the column J to

437 00:20:00,840 --> 00:20:05,400 display the revenue So currently it is

438 00:20:02,460 --> 00:20:06,840 runs like this so double click on here

439 00:20:05,400 --> 00:20:10,320 roots

440 00:20:06,840 --> 00:20:12,480 so go to between J and K column and this

441 00:20:10,320 --> 00:20:13,980 will appear and double click so it will

442 00:20:12,480 --> 00:20:17,460 resize

443 00:20:13,980 --> 00:20:20,400 to its feed oops Ctrl Z okay

444 00:20:17,460 --> 00:20:23,160 now it is saying resize column this is

445 00:20:20,400 --> 00:20:24,000 done type state in I4 and revenue in j4

446 00:20:23,160 --> 00:20:28,700 already

447 00:20:24,000 --> 00:20:28,700 so instead and then

448 00:20:28,740 --> 00:20:33,120 UE okay Revenue in j4 and it is saying

449 00:20:31,260 --> 00:20:36,480 create a field map plant based on the

450 00:20:33,120 --> 00:20:38,220 Range I4 through Z10 so select these and

451 00:20:36,480 --> 00:20:41,460 then it is saying field map

452 00:20:38,220 --> 00:20:43,740 so go to insert and then

453 00:20:41,460 --> 00:20:45,960 let's see so map and then here is the

454 00:20:43,740 --> 00:20:49,200 field map and click here

455 00:20:45,960 --> 00:20:51,900 okay so it is saying remove and resize

456 00:20:49,200 --> 00:20:54,960 I13 through en25

457 00:20:51,900 --> 00:20:58,860 so I 13 is

458 00:20:54,960 --> 00:21:02,240 here and then let's see again 25

459 00:20:58,860 --> 00:21:02,240 so click on the search

460 00:21:02,580 --> 00:21:07,020 and then

461 00:21:04,320 --> 00:21:10,980 go to the corners

462 00:21:07,020 --> 00:21:14,900 so click on this ad go to the corner

463 00:21:10,980 --> 00:21:14,900 it is not giving me that Arrow

464 00:21:17,220 --> 00:21:21,780 so if this happens then you might need

465 00:21:19,799 --> 00:21:23,220 to save the project and then restart the

466 00:21:21,780 --> 00:21:25,559 project again

467 00:21:23,220 --> 00:21:28,620 so usually it gives it change this

468 00:21:25,559 --> 00:21:32,880 cursor to different thing or card leads

469 00:21:28,620 --> 00:21:36,000 us allow me to track Okay so n25

470 00:21:32,880 --> 00:21:38,760 let's try one more time

471 00:21:36,000 --> 00:21:42,299 okay so click on this chart

472 00:21:38,760 --> 00:21:46,880 okay so I'll restart this file

473 00:21:42,299 --> 00:21:46,880 and close this file and reopen it okay

474 00:22:00,780 --> 00:22:09,440 all right so I reopened the file let's

475 00:22:03,299 --> 00:22:09,440 see so it is saying G5 at 25.

476 00:22:10,039 --> 00:22:15,240 see this time it is giving me this year

477 00:22:12,539 --> 00:22:17,340 so sometimes you might need to reopen

478 00:22:15,240 --> 00:22:23,159 the file so just save it cancel it and

479 00:22:17,340 --> 00:22:23,159 then reopen it so and 25 already

480 00:22:23,640 --> 00:22:29,820 now it is saying remove the chart title

481 00:22:26,820 --> 00:22:32,159 and then after that we are done so oops

482 00:22:29,820 --> 00:22:35,039 Skip and remove

483 00:22:32,159 --> 00:22:38,600 all right so let's take our result

484 00:22:35,039 --> 00:22:38,600 so Revenue history

485 00:22:40,620 --> 00:22:45,919 all right so this is good forecast

486 00:22:51,480 --> 00:22:54,919 let's see six

487 00:22:57,360 --> 00:23:03,419 yeah this is good

488 00:22:59,880 --> 00:23:07,200 and then monthly sales

489 00:23:03,419 --> 00:23:10,880 okay this is called

490 00:23:07,200 --> 00:23:10,880 and then four reasons

491 00:23:14,299 --> 00:23:20,280 okay we forgot to

492 00:23:16,980 --> 00:23:22,500 do some steps here so what I do it I

493 00:23:20,280 --> 00:23:24,120 will record this step again and then add

494 00:23:22,500 --> 00:23:27,059 it to that recording

495 00:23:24,120 --> 00:23:29,780 okay let's see how that thing so Revenue

496 00:23:27,059 --> 00:23:29,780 by state

497 00:23:31,919 --> 00:23:36,260 okay so this is also good