The CALCULATE() Function – Application

Quick reference

• 00:04 In this video, I don't want to focus so much on building calculate functions.
• 00:09 I'd really rather start looking at how they work and what's done because I think
• 00:13 you have enough already where you can build the formula.
• 00:16 You just need to know how and why.
• 00:18 So let's just go and collapse the visualizations area so
• 00:20 we get a little bit more room to look at here.
• 00:22 And you'll notice that I have a few different functions that are already
• 00:25 here ready written.
• 00:26 And I've also added a nice little bar chart here so
• 00:29 I can sort of see what's going on.
• 00:32 Notice that my total sales measure is $5.2 million,
• 00:35 but I have something here that shows my sales redraft at 1.4.
• 00:39 I've got my sales for bottled beer at 397,000 and my sales for
• 00:44 canned beer at 607.
• 00:46 The thing that's important to realize here is that draft, bottled beer and
• 00:49 canned beer are nowhere in the filter context at this particular time.
• 00:54 Now I could add them.
• 00:55 I could come over here and say, you know what,
• 00:57 let's click on this chart, click on draft beer.
• 01:00 You'll notice at this point that the sales is filtered,
• 01:02 this is a regular old sum of the sales amount, that one is filtered now to be
• 01:08 1.392608 which matches the measure that I had for draft, this did not change.
• 01:13 You'll also notice that bottled beer and canned beer did not change.
• 01:17 If I go over to canned beer, you can see that these again did not change but
• 01:22 the sales measure 607428 is matching canned beer.
• 01:25 So what's actually happening here?
• 01:27 Well, what's actually happening is,
• 01:29 I've written a calculate function to return these three measures.
• 01:31 And if we go and we take a look here and say, let's go and
• 01:34 look at sales draft, you'll notice that it's written this way,
• 01:38 sales draft equals calculate the sales measure, comma, and the next set,
• 01:42 the categories for category equal to draft beer.
• 01:45 So basically what this is going to do is going to apply an additional filter
• 01:49 to say, get rid of all of the categories that don't equal draft beer.
• 01:53 And of course, draft beer is wrapped in quotes because it's text and
• 01:56 it matches exactly to what's in the underlying source data table.
• 02:00 The same is true if I go and look at canned beer.
• 02:03 You can see it, here we are, look good, and bottled beer has the bottled beer.
• 02:08 So it doesn't have to be exactly the same, of course, in the label, naturally.
• 02:11 We use the full extended text here.
• 02:14 Now, why is this important?
• 02:16 Well, because, what if I wanna see what was actually done for
• 02:21 one specific location?
• 02:22 So I'm gonna go click on the Squintz here.
• 02:25 You'll notice now, that my sales do change.
• 02:27 Draft beer is at 482,805.
• 02:30 And if I look at the filter context that's showing here,
• 02:33 we can see that the highlighted area, or the cross filtered area is 482,805.
• 02:38 You'll also notice that I have a couple measures down here for
• 02:42 Squintz draft 1 and Squintz draft 2 at 482,805.
• 02:45 If I clear the filter now, those stay the same.
• 02:49 So how did I get those?
• 02:51 Well, if we go and take a look at the first one.
• 02:54 This is the Version 1.
• 02:55 You'll notice that I can do this by saying calculate sales.
• 02:58 The location name equals the Squintz and categories equals draft beers.
• 03:02 So this is working with multiple filters inside calculate.
• 03:04 Each one is separated by a comma, and they layer on appropriately.
• 03:09 I also have another option though.
• 03:12 I could go and say, why don't I take the sales for
• 03:16 draft which is the measure that I built up here.
• 03:18 So this one, remember, sales is the base measure,
• 03:21 sales draft applies the additional filter for just sales that are draft.
• 03:25 And now this Version 2 says take that sales draft measure and
• 03:29 apply one more filter to filter it down even further.
• 03:32 So I can layer these things on gradually if I want to.
• 03:35 It's also important to realize the Version 1 and
• 03:38 Version 2 measures that I made here are completely independent of each other.
• 03:41 I could have gone straight to this without ever doing a Version 1, so
• 03:45 that is an important thing to be aware of.
• 03:47 Now, let's look at the next one.
• 03:49 These sales from 1 to 4 PM.
• 03:51 You'll notice that no matter what location that I pick it up, it filters nicely.
• 03:55 No matter what particular category I pick up, it filters nicely as well.
• 03:59 And this is picking up sales between 1 and 4 PM.
• 04:02 But again, I don't have any visual in this place that ads that filter context.
• 04:07 This is a little bit more complicated measure but
• 04:10 actually involves doing some end matching.
• 04:14 So in this case, I'm gonna show you the format for doing this.
• 04:17 We've got the calculate the sales dollars, the filter is all one filter.
• 04:22 We're not making two filters.
• 04:24 If I added something to say, give sales hour is greater than 13,
• 04:28 sales hour is less than 16, that's gonna do some weird things to my filter setup.
• 04:34 In this case, what I've done is I set Sales Hour is greater than or equal to 13,
• 04:39 and end, this combines the two things together
• 04:43 to actually use them as a more complicated filter.
• 04:46 Now, we don't do this often but sometimes you need to do so, and
• 04:49 end will apply a matching pattern says, I need both of these things to be true.
• 04:54 So it's got to be greater than or equal too 1300 hours and less than or
• 04:58 equal to 1400 hours.
• 04:59 Which will give me my sales pattern between 1 and 4 PM.
• 05:02 Which should work out nicely and
• 05:04 you can see it is actually returning a nice little value.
• 05:07 There's also another clause that we can try.
• 05:09 And that is for working with something like where we want beer.
• 05:12 We have three individual categories.
• 05:14 Now, if I were to go and write a measure like this, that said show me the sales
• 05:18 where our sales category equals draft beer and our sales category equals canned beer.
• 05:25 Well, there's no line where the draft beer and canned beer appear on the same line.
• 05:31 Each one is an individual data point, so for that I need something that says or.
• 05:35 Now the or measure is actually built up a little bit differently.
• 05:40 It uses the same it uses two characters, but those two characters are actually this
• 05:44 pipe character, which is a shift backslash.
• 05:47 So in this case we have categories which is draft beer or bottled beer or
• 05:51 canned beer.
• 05:53 But there's also an easier way to do this.
• 05:55 We've already written measures for sales draft, bottled beer, and canned beer.
• 05:59 So, why don't we just build a nice simple compound measure that adds
• 06:03 the three of them together?
• 06:04 That's the easiest way to do this and
• 06:06 it's a heck of a lot easier to actually go back and read as well later on.
• 06:09 It was sales draft.
• 06:10 The other thing that's really important about this.
• 06:13 Is that when we look at this measure, if I were to go and
• 06:16 take the sales draft and canned beer and bottled beer off.
• 06:21 Guess what?
• 06:22 My measures all still work.
• 06:24 These things do not really need to be on the visual first in order to make it work.
• 06:28 Because they will always go back to the row and filter tables and
• 06:31 run the patterns through so that you don't have to be in the context of the visual.
• 06:34 If they're not, post criteria will just be added

Lesson notes are only available for subscribers.