digest
Welcome to Power Pivot video number four. Hey, if you want to download this work with Power Pivot for array formulas or Power Pivot, click on the link below the video. Hey, in this video we want to look at an array formula. We're going to see two different array formulas, then see that it's probably a lot easier and faster calculating when we use DAX formulas instead of array formulas.
Here's our data set. It's a bunch of names. Control down arrow, so it looks like we have almost 20,000 names. Control Home. Our goal is to count unique items in this list. We just want to count how many unique names there are in this list.
There are a few array formulas we could use, and I've done lots of videos on this. My Control Shift Enter book covers these formulas in great detail. That's a great formula, and when I hit Enter it doesn't take very long to calculate. I'd better turn this off, but that's pretty complicated. We'll see that the comparable function in DAX, DISTINCTCOUNT, is much easier than knowing how to do that.
Now let me show you another formula. This one's pretty easy and pretty well known: SUMPRODUCT. You simply do COUNTIF, and you highlight the range. So you do range, Control Shift Down Arrow, Control Backspace, comma, and we'll do the same range for criteria. This is a fun function. The argument is an array operation, and that argument is expecting a single value. We're going to give it almost 20,000. Control Backspace, close parenthesis, close parenthesis, and I'm actually going to come to the beginning and do one divided by.
That formula is not too hard to create, and the problem is that when I hit Enter, watch this. It is just going to take forever to calculate. This formula is great on small data sets, and that's basically what I point out in the book. Otherwise you're going to have to go to something much more complicated that is faster calculating.
But check this out. We can use the DAX function DISTINCTCOUNT. For this DAX function we don't even have to invoke Power Pivot. In our last video we saw how to add data sets like this to the data model using Power Pivot, and not using Power Pivot. As long as you have Excel 2013, you can do this. Ready? You have to convert it to a table: Control T, Enter. Alt JTA to name it. We can see up here the list of names. Enter.
All right, so now we can add this to our data model. How do we do that? Just as we saw in the last video: PivotTable, Insert PivotTable, or Alt NV. This opens our Create PivotTable dialogue box, and just as in the last video, we're going to use Add this to Data Model. In the last video we used this feature to add the data model to add two tables. We only have one table here.
All we're going to do is, if you check this in 2013, the PRODUCT function in a regular PivotTable gets replaced with DISTINCTCOUNT. All right, click OK. Watch this. Whoops, I didn't want to click OK. I'm going to move this: Move PivotTable, Existing Sheet. I'm going to click back over here. I should have done this in the first place. This is a cool trick in itself: you can change it. Click OK.
Now I'm going to click and drag it to the Values, and instantly it will count it. But I can right-click in the Values area, come down to Summarize Values By, More Options, Value Field Settings. We drag it down, and there's no more PRODUCT; it's DISTINCTCOUNT. Click OK.
Now, this is kind of cheating. I'm not really going over and using Power Pivot, but that is fast. If we did go over to Power Pivot, check this out: we could go to Manage, Alt BM. This opens the Manage Data Model window, and there is our table, which took the name we gave it: list of names.
Now, this table down here is the measure grid. This is where we can create our calculated fields. I'm going to point to the gray bar, click and drag up, point to the column, and click and drag, just to give us a little breathing room there.
Now, just as in video number one, we're going to use the measure grid. When I click in a cell down here and start typing the calculated field name, then the assignment operator and the formula, it will appear up here in the formula bar. Ready? We're going to call this UniqueList, and you can see in the formula bar there it is. The assignment operator is a colon and an equal sign. Over in Excel we never use that term; we use the equal sign in a cell to tell Excel it's a formula. Here, to say that this is a calculated field or measure, you type a colon and then an equal sign. As we mentioned in the first video, you might have seen this in Access, where you have the actual calculated field name, and everything following the colon is the formula.
So now we're just going to use DISTINCTCOUNT, down arrow, tab, and we use our convention of typing out the name of the table and the field. I'll arrow down: there's the table name and field name in square brackets. Tab, close parentheses, and Enter. Now we can see the 72 there. We can use this calculated field in a PivotTable.
You might be saying, "Wait a second, this is a lot more work than just creating it with our PivotTable." Yes, absolutely. If you're just after DISTINCTCOUNT, that PivotTable is awesome. However, later in this video series we'll see that creating explicit calculated fields, rather than relying on the internal functions inside a PivotTable, is awesome, because we can format them and use them in other formulas. Later in the series we'll see how to use unique count matched with total revenue to get things like average sales per day.
All right, let's create a PivotTable to finish this section. I'll choose Existing Sheet, click the collapse button, and I'm going to be risky here: I'll put it right below. Click OK, click OK. Notice I have these other tables here. These are coming from this sheet, as we said a couple of videos ago. Whatever number of tables you have in your workbook will show up in your field list. These ones are not added to the data model yet; this one is. We can see that black line there. I'm simply going to open it up and drag, not the names field, but this actual calculated field, a formula that shows up in our field list, down to Values.
So we can clearly see that DISTINCTCOUNT is a great new feature in DAX. We can get it with a PivotTable or build it over in a calculated field. We saw that some of these other array formulas are too slow calculating and too complicated.
Now I want to show you one last example in this video of an array formula. Here is similar data to the data I'll be using all the way through. We have three tables. If we weren't allowed to create helper columns over in video number one, we saw how VLOOKUP with a helper column is sometimes exactly what you want. But check this out: what if we weren't allowed to add that extra helper column? We could absolutely create a very complicated array formula to total up the East. I have a video that shows you how to do this. This formula, with all these wild array calculations, is doing relational databasing without VLOOKUP helper columns and without relationships between the tables like we would have in Power Pivot. To summarize by region and get the revenue, we had to look up from this table over to that table, get the price, multiply discount times units, and then also have a relationship, or a VLOOKUP, or an array formula to simulate that with region. The point is that this is unbelievably complicated, not easy to do, super advanced Excel. It's totally easier, if you're not allowed to have helper columns, to just import these into Power Pivot and build relationships.
All right, in our next video we will start a bigger, comprehensive example. Next video we'll see how to import from multiple sources and filter as we import the data into Power Pivot. All right, see you next video.