The ALL() Function - Application

Quick reference

• 00:05 So let's look at a practical example of using All.
• 00:07 One thing I'm gonna do before we actually jump into this,
• 00:10 I'm gonna add some subtotals to the bottom of this table.
• 00:12 Because we really need something here for revenues and expenses to total them up.
• 00:16 So we'll go to the Design tab, and we'll go to Subtotals, and
• 00:20 we're gonna show all our subtotals at the bottom of the group to make
• 00:22 things look a little bit nicer here.
• 00:24 And then we're also gonna come back and say, you know what, let's right-click and
• 00:28 uncheck the subtotal for
• 00:29 year to remove those from the pivot table on the right-hand side.
• 00:33 So that looks a lot better now, something we can actually compare some things with.
• 00:37 What I'd like to do at this point, is I'd like to actually build a measure
• 00:41 that will allow me to show my values as a percentage of gross revenues.
• 00:46 So I wanna show as a percentage of this $92,000.
• 00:50 So I am going to start by going to Power Pivot and
• 00:52 I am going to create myself a measure for gross revenue.
• 00:56 So we'll say new measure and we call this one here gross revenue, seems logical.
• 01:02 And we'll start by saying, well, it's going to need to be a calculate function.
• 01:07 We'll start with actual, and then what are we going to do?
• 01:11 Well, you say well, in order to get to gross revenue, plainly I'm going to
• 01:14 need to override the filter context with my class equal to revenues.
• 01:19 So the class is on the COA table.
• 01:21 Here's class, equals and in quotes, revenues because it's a text field.
• 01:27 I'll now choose it's a number, dial it down to be consistent,
• 01:31 put my 1000s, check my formula, of course.
• 01:35 And we can see that everything looks pretty good here.
• 01:38 So this one, if I'm right, should go back and say all right,
• 01:41 I want to force it to always be revenues.
• 01:44 And it will say okay.
• 01:46 And what you're gonna see is that we don't quite get all the way there.
• 01:50 And you sit there and you go, well why?
• 01:52 I mean, my gross revenue's working here but in my expenses I've got nothing and
• 01:56 I thought that it would override this filter context.
• 01:59 And in my individual line items it looks like it's showing me the revenues for
• 02:04 sure but only for the individual line item.
• 02:06 It's not giving me gross.
• 02:08 So here's the thing.
• 02:10 In this particular case, what I'm gonna do now is I'm gonna go and
• 02:13 I'm gonna flip this guy out into a slightly different format.
• 02:16 We're gonna go move into tabular form for a second.
• 02:19 So here's my filter context.
• 02:22 It's probably easier even if I see it to repeat all the item labels so
• 02:26 we can fill them down.
• 02:28 When I look at the filter context for this cell,
• 02:30 we can see that class equals revenue, and group equals alcoholic beverages.
• 02:36 That makes sense.
• 02:37 There is a subgroup that's actually narrowing this down further.
• 02:40 I actually want to have everything as my subgroup to force it to be the $92,000.
• 02:45 Once we get down here, things get little bit more confusing.
• 02:48 We're overriding our class with revenues, but then when you filter it down even
• 02:53 further, well guess what, there's none of these subgroups in the revenue category.
• 02:58 So we're actually removing all rows that don't equal revenues.
• 03:01 And then it says well now I want to remove all rows that don't equal allocations.
• 03:04 Well there aren't any allocations anyway, so we end up with zero rows.
• 03:07 So that's what we see going on over here.
• 03:09 So what we need to do is we need to say well, wait a minute.
• 03:12 We need to bring back the filters on our tables so
• 03:16 that they're not actually being filtered by the account group.
• 03:20 So we've got a couple of different ways to do that.
• 03:22 What I'm gonna do is I'm gonna say, you know what?
• 03:24 If this is gonna be gross revenues,
• 03:26 I don't want any filter to be set on this thing that's really gonna mess this up.
• 03:30 So I'm gonna come back in.
• 03:31 I'm gonna manage my gross revenues.
• 03:33 And I'm gonna say, edit.
• 03:35 And I'm going to add something new to this.
• 03:36 Now, I'm going to hold down my control key and roll my mouse wheel up a bit cuz
• 03:39 this'll make this form a little bit bigger so we can see it.
• 03:42 And now, right after here, I'm going to say let's go with COA.
• 03:47 Actually, let's not.
• 03:48 Let's back up a minute.
• 03:50 Let's go with All.
• 03:51 Open parenthesis COA and
• 03:53 we're going to remove every filter from the chart of accounts column.
• 03:58 So we're going to remove class, we're gonna remove group.
• 04:01 But because we've got COA class here filtered revenues,
• 04:04 it will keep that around.
• 04:05 The order here as you see is actually not that important.
• 04:09 We'll click check formula.
• 04:11 We'll say okay.
• 04:13 And now we'll hit Close and
• 04:15 you'll see that it will override every line item with the $92,000.
• 04:19 So that's pretty cool.
• 04:21 Now what I can do is I can go back and say, hey, let's create a measure.
• 04:25 We'll call this one New Measure.
• 04:28 We're gonna save.
• 04:29 This guy here is equal to percent of revenue.
• 04:34 And this is going to be equal to divide.
• 04:38 Numerator is going to be actual and the denominator will be gross revenue.
• 04:45 Close our parenthesis, check our formula.
• 04:49 We'll make this guy a number.
• 04:50 We'll make him a percentage with one decimal point, and
• 04:56 use the thousand separator should we ever get that high.
• 04:59 And now we can say okay, and you'll notice that we'll get a nice percentage that
• 05:03 actually shows that gross revenue is 100%, but all of our numbers as a percentage of
• 05:08 gross revenues, which is really, really cool.
• 05:12 Naturally, do we need to see gross revenue all over the place?
• 05:15 Not at all.
• 05:16 We can pull that off the pivot table,
• 05:18 get rid of it because it's not really relevant.
• 05:21 I can even move my percent of revenue to where I wanted which would be
• 05:25 next to actual.
• 05:26 So I can see that my alcoholic beverages is 6.3% of my gross revenue.
• 05:31 And I can see that my expenses are 6.1% of the gross revenue, as well.
• 05:35 So this is how we can use All to actually start removing different things from
• 05:38 different filters.
• 05:41 Now, there is one more piece I want to look at.
• 05:43 Let's go and grab these two slicers here.
• 05:45 Which by the way are slicing very well and we can see that our growth versus 2009 for
• 05:51 February, 2010, is showing negative 1,326.
• 05:54 I want you to remember that.
• 05:56 And I'm gonna delete both these slicers, and I'm gonna use something different now.
• 06:00 We're gonna go back.
• 06:01 You can see these filters have stuck on the pivot table for right now,
• 06:04 which is fine.
• 06:05 We're gonna go back to Analyze.
• 06:06 We're gonna choose Insert a Timeline.
• 06:09 And we're gonna insert it based on the date field.
• 06:12 We'll say okay, and you'll notice that everything filters down very,
• 06:15 very quickly here, into a much smaller subset.
• 06:19 But what I'm gonna do is I'm gonna go and take a look at months.
• 06:23 And I'm gonna filter to February 2010, which is where we were before.
• 06:26 And you'll notice this time that my growth versus 2009 is not working.
• 06:31 It's not giving me the negative 1,326 which comes from February 2009.
• 06:37 It's giving me 5,832, which is the original measure.
• 06:39 You go, well what the heck is going on?
• 06:41 I mean, we were in February 2010 before and it was working, and now we're not.
• 06:46 And this is the key where it becomes really important to understand what's
• 06:49 actually happening on these filters.
• 06:52 Which field is driving the date slicer?
• 06:55 Well it's actually calendar date.
• 06:58 So when you're trying to debug what's happening with your February growth
• 07:01 versus 2009 measure, you go back to Power Pivot, measures and manage.
• 07:07 And we go and take a look at our Growth vs 2009, and we say Edit.
• 07:10 And it says, well, it's actual.
• 07:11 We know how that one works, minus the 2009 Actuals, so okay, fair enough.
• 07:15 Let's go look at 2009 Actual.
• 07:18 The 2009 Actual, if you look at it carefully, says,
• 07:23 take the Actual and override the Calendar Year.
• 07:27 Well, what field is being used on the timeline?
• 07:30 It's not calendar year, it's calendar date.
• 07:34 It's a different field.
• 07:35 So if we want it to do this, we'd have to go back and
• 07:38 say, there is something else that we need to remove first.
• 07:42 We need to come back and say, All Calendar
• 07:46 in order to remove the filters that are being set by the actual timeline itself.
• 07:51 Or if we're feeling very specific, we would say, hey listen, I wanna remove all
• 07:56 calendar dates in order to remove that specific piece.
• 08:01 When I do this and say okay, and
• 08:04 close, you'll see that that measure now corrects itself.
• 08:07 So this is one of the things that is really, really important is in
• 08:10 understanding that filter context when you're debugging measures.
• 08:13 And I'll be honest, this kind of stuff still gets me today.
• 08:16 You'll be looking at it, you're going, why is this not working?
• 08:18 And it turns out, that as you start to go back and walk your way through that
• 08:22 measure calculation sheet, you realize when your determining your filter context,
• 08:26 the filter context that I'm looking at, I used a different field from the table.
• 08:30 And that is what I'm trying to override, and that why its not working.
• 08:33 So keep that in mind as you debugging your measures.

Lesson notes are only available for subscribers.