Extracting Data Points with GETPIVOTDATA()

Quick reference

• 00:05 Now, sometimes when you're using pivot tables to build dashboards,
• 00:08 you don't want to actually display an entire pivot table.
• 00:12 You want to use the pivot table as a means to an end to get a specific data point.
• 00:17 But having all the extra framework and
• 00:20 bulk around the whole thing really doesn't make any sense.
• 00:23 So this is a particular case here where we have a summary statistic.
• 00:26 So what I'd like to see is I'd like to see gross sales, which is never filtered.
• 00:31 It always shows the gross sales for the organization.
• 00:34 I'd like to see the total tent sales, but I'd also like to show how much the tent
• 00:38 sales are that are included in the dashboard.
• 00:40 Now we can see that right here, the 77,828.
• 00:44 So let's go and say equals, and say equals to that cell.
• 00:49 And what you'll see is that you get a GETPIVOTDATA function.
• 00:53 And it says GETPIVOTDATA, Sales $, so it's pulling the measure name.
• 00:58 Comma B10, B10 is the top left hand corner of the pivot table itself.
• 01:03 And then it gives pairs of information here, we say comma categories.
• 01:06 So it says, tell me what the category is where it equals tents,
• 01:10 all right, no problem.
• 01:11 So when we say okay to that, you'll notice that it pulls the 77,828, and
• 01:16 that's great.
• 01:18 If I go now and look at this dashboard and I filter to sleeping bags,
• 01:23 though, you'll notice that it gives me a #REF error.
• 01:27 Now I would expect at this point, that this would come back and say zero.
• 01:31 Hey, there's no tents that are included on the dashboard this time,
• 01:34 because I've filtered it out to sleeping bags, but #REF, that's awful.
• 01:38 So this is one of those things you have to be really careful with
• 01:41 right off the bat with GETPIVOTDATA, is how do I
• 01:45 deal with this particular problem if my data is filtered out of the table?
• 01:49 And the answer to that is to go back and actually use and
• 01:53 if error function to deal with this.
• 01:55 So what we do is we come back and we say,
• 01:57 all right, let's take our GETPIVOTDATA function, and we're gonna use IFERROR.
• 02:02 So right after equals, I'm gonna type in IFERROR open parenthesis.
• 02:07 And then it says, what's the value?
• 02:09 So the way that IFERROR works is, it says let's take the formula and
• 02:13 I'm gonna try it, if it works I'll return the answer.
• 02:18 But if it fails, then we go to the end and say comma,
• 02:22 what do I want to see if there's an error?
• 02:24 Well you know what, I'm gonna put in 0 in this case, and close my parenthesis.
• 02:28 So this is how we wrap our GETPIVOTDATA in an IFERROR function.
• 02:33 When we hit enter now, it says hey,
• 02:35 it's 77,828, great, let's filter the sleeping bags.
• 02:38 Now it tells me that there's no sleeping bags, or no tents rather,
• 02:42 included in the dashboard anywhere.
• 02:44 So that's much better than looking like a #REF error,
• 02:46 nobody wants to see those on their dashboard at all.
• 02:50 But now this brings up another question, well, how do I get my total tent sales?
• 02:55 Because, as you can see here, the big challenge is that if I start going and
• 02:59 actually playing around with this, let's say, for example, I want my gross sales.
• 03:02 I say equals 130,000, everything looks good there, and
• 03:07 then when I go filter to sleeping bag, it drops that number down.
• 03:10 So how do I actually keep that as the 130?
• 03:13 Well the answer is to create another pivot table, so
• 03:18 what we're gonna do is we're gonna go back to source data.
• 03:21 We'll grab our data listing here, we're gonna say insert pivot table.
• 03:26 We'll put this one on a new worksheet,
• 03:28 we're gonna bury this away somewhere where nobody needs to see it, here we go.
• 03:31 Now I'm just gonna rename this little worksheet here to summaries.
• 03:37 On this pivot table, what I'm gonna do is I'm gonna drop the category on rows, and
• 03:42 I'm gonna drop my sales dollars on values.
• 03:46 And maybe, you know what, I'll even put my units in here as well.
• 03:50 I don't even really care what these things are called,
• 03:53 I could call them the same thing, I don't have to.
• 03:55 The key thing that I'm really looking for here is that my data here is
• 04:00 showing what would be unfiltered on this set in a different table.
• 04:06 Because here's the thing, when I go now and say equals gross sales,
• 04:11 and I pull it from summaries, and
• 04:14 pull it from this 130,000, it's gonna be pulling from that set.
• 04:19 If I go and grab my tent sales and go back to summaries and
• 04:23 pull it from here as well.
• 04:25 Cool thing now is that when I click on sleeping bags these stay, why?
• 04:32 Because this slicer is not linked to PivotTable7,
• 04:37 which again has a horrible name.
• 04:40 So as a matter of course I'm just gonna go back and clean this up and say,
• 04:45 analysis here.
• 04:46 We'll call this one Pvt summaries, just so that I know in future if I'm linking
• 04:51 any more slices that I don't wanna hit that one.
• 04:53 But the big key there is with that separate pivot table disconnected,
• 04:57 I can even hide the work sheet.
• 04:58 Nobody needs to know it exists, and now I have something that I can slice and
• 05:02 dice and see.
• 05:03 When I click on tents, you'll see that it actually pulls this up.
• 05:06 And that works quite nicely for
• 05:08 driving some key stats without having to show the entire pivot table in some cases.

Lesson notes are only available for subscribers.