Welcome to Excel Magic
Trick number 1,378. Hey, if you want to download
this Excel workbook– Excel Magic Trick 1,378 Start– or the finished file so you can
follow along, click on the link below the video. Hey, we’ve got a
great video here. We’ve got to calculate the
average of customer total sales by product. And we’re going to see how to
do it with a series of Excel formulas. Then we’ll see how
to do it with DAX. That’s Data Analysis
Expressions formulas, either in the data
model in Power Pivot or Power BI Desktop. And we’ll see two
single-cell array formulas. Now, here’s our data set– Sales, Product, and Customer. Now, for our Excel
formulas, we’ll be able to use a single table. For the DAX solution, we’ll
have our customer table, which we’ll be able to
iterate over and calculate values for each customer. And then for our
product, we’ll be able to iterate over this table
and calculate various values for each one of these. All right, so
here’s our data set. Our first step if we’re
doing it with formulas is I need to calculate
the sum for each product, but I need a total
sum for each customer. Then from those
total sum amounts, then we calculate the average. Now, this is a
cross-tabulated table. That means any intersecting
cell will be a calculation– in our case, sum– with whatever the criteria
is at the head of the row. That means product Quad. And as a second condition,
using an AND logical test, customer number three. Perfect function
for that in Excel is the SUMIFS with
an S function. Now, the SUMIFS functions
requires the sum range– those are the numbers
we need to add– and then any criteria range and
criteria for this calculation. So sum range, that’s going
to be our Sales column. Now, this is actually
an Excel table, but I’ve turned off the
table formula nomenclature. You can do that by
going to File, Options, and under Formulas. So there’s that range. And by the way, that
range is dynamic. If we add any extra rows
to this Excel table, this formula will update. But we need to lock this, so
I hit the F4 key to lock it. Comma. Criteria range one. Remember, we have
two conditions– product and customer. You can do it in any order. I’m going to choose for criteria
range one the Product column. F4 to lock that
in all directions. Comma. Now the criteria. I need to tell SUMIFS to only
find Aspen sales numbers. So I click on Aspen. Now we’re going to copy
this formula down and over. As I copy down,
that purple range needs to move to Eagle
and Quad and Sunset. But when I copy the formula
over to customer 2 and 3, that cell reference
F11 needs to be locked. So I need to lock the
column reference F So it’s locked on F
here, but not the 11. So I hit the F4 key
one, two, three times. Now comma, criteria range
two, this is the customer key. F4 to lock it. That’s criteria range two. Criteria two, that’s
the customer number. This one needs to be
locked when it’s going down across the rows, but not when
it goes across the columns to 2 and 3. So I hit the F4
key once and twice to lock the row,
but not the column. That’ll do it for adding
with two conditions. Close parentheses. Control-Enter. I’m going to copy
this to the side and then let go
and drag it down. I go to diagonally
furthest one away, hit F2, and verify that all of my
cell references are working. Yes they are. Now I want to add to get
totals for the actual products and totals down here for
the actual customers. So I’m going to do
a little trick here. I’m going to highlight
the numbers and some cells to the right and below. And now I’m going to use my
keyboard, Alt-Equals, to put the SUM function in. Now, I usually feel a little
bit nervous when I do that, because I didn’t get to verify
that it actually worked. So I’m going to click
in this cell and hit F2. That looks correct. I’m going to click
down here, F2. That looks correct also. All right, now these
are the numbers– actual totals for this
particular product. Total for customer 1. And we can see over
here I’ve added yellow. Those two numbers were added
together to get that total. So these are the sum totals. Now we need to come over
and calculate the average for this particular product
of the customer totals. So I’m going to use
the AVERAGE function. Simply highlight
relative cell references. There’s going to be a
problem with this one, because it will include the 0. Now, AVERAGE would ignore it if
there was nothing in the cell. But when there’s a
0, it includes it. Control-Enter, that’s
not the total we want. But I did that just
to show you there that that would
lead to a mistake. Now, when we get to
DAX, DAX actually will have no problem
avoiding this 0 without any special
calculation at all. It actually won’t be a 0,
it will actually be empty. All right, now we simply
have to use AVERAGE. And I’m going to choose
AVERAGEIF without the S. This is one of the
only times that I like to use the
IF without the S, because it has a totally
different screen tip. AVERAGEIF, SUMIF, COUNTIF
say range criteria and then average range. If We we’re using any of the S
versions, the actual argument that includes the numbers
always comes first. So that range right there
actually means the criteria. But the reason I’m using the IF
without the S is because range. If it happens to be that
the criteria range also is the range of numbers you want
to make your calculation on, then– comma– all I have to
do is put criteria in. And for us, this is
going to be hardcoded in. Please only take
numbers greater than 0. And double quote. It has to be in double quotes. And then we can notice that
also that last argument is in square brackets, which
is a visual cue that means if we know the default
for how this function works, we can leave that out. So there’s our formal. Control-Enter, and I’m
going to copy it down. And there it is. There’s each average of
customer total sales by product. So for Aspen, it’s \$4,463.40. For Eagle, \$2,988.65. This is the total
down at the bottom, of course, which is
simply the customer totals without any product criteria. Now, what if we didn’t want to
go through these multiple steps and we wanted to simply do it in
a single cell and copy it down? Well, let’s come
up here and notice something, that here’s SUMIFS. And we followed the proper
convention for SUMIFS. We have criteria range
one, criteria one. Criteria two here. But notice criteria
two, single item. Criteria one, single item. That’s how SUMIFS work. That mean we used an
AND logical test to add. But really what we need down
in the cell is all three of these numbers. So actually, we’re going to
do almost the same formula. Comma. And everything so
far is the same as we did before,
except for this. Criteria range two, we really
want to ask the question here, please find customer
1 or 2 or 3. Now, I’m going to highlight
for my criteria actually from our customer
key up here, which is the unique list of customers. And what do we do? We put three items in here. Anytime you put three items
into criteria arguments for any of the SUMIFS,
AVERAGEIFS, COUNTIFS, it instructs SUMIFS to
spit out three answers. So because we have three
conditions up here, it will spit out
these three answers. Now, that range when I copy
down needs to be locked. So I hit the F4 key. Close parentheses. Now, my cursor is
at the end, so I’m going to hit the
F9 key to evaluate to prove that in fact it
did just as we wanted. It calculated the three
different answers. And if we broke down
the logic, what we did is we asked the
logical question, please find Aspen and customer
1 or Aspen and customer 2 or Aspen and customer 3. All right, now what are we going
to do with that resultant array there? Well, I would love to put– like we did over here– put that into the average. But those functions can
not handle array operations in the range argument here
or the criteria range, as we saw before. So it just won’t work. So I’m going to build
an array formula. I do want to average, but
then inside number one, I’m going to say IF. And that whole
thing right there– F9 is spitting out
a resultant array. So I can ask a question of that. Is anything in there
greater than 0? Now if I were to
highlight this and hit F9, it gives me true,
true, false, which is exactly how I’m going to
pick out those three numbers. Control-Z. Now, I should
have copy this before, because we’re going to
have to repeat this array formula again. Comma. If any of those are true,
then the value of true, Control-V. We can leave
value is false out. That way the IF we’ll
insert a false, which the AVERAGE function is
programmed to ignore. So at the end, I
close parentheses. If I highlight the whole
IF and hit F9, there it is. Number, number, and false
inserted, which AVERAGE will ignore. Control-Z. Now close
parentheses for the AVERAGE. We did a bunch of
array calculation here. The actual SUMIFS
is spitting out an array of answers
from an array operation. And then we did
an array operation with this greater than symbol
inside this logical test argument. And that argument will not
calculate this array form correctly unless we use
Control-Shift-Enter. Immediately when I do
Control-Shift-Enter, I look up to the
Formula bar to verify that those curly
brackets are put in. Those curly brackets
are Excel telling us that it understood that this
was an array calculation. Now I can double click
and send it down. And look at that. All the way down, same
answers as over here. F2 to verify that all the
cell references are working. Now, I did not time
this array formula to see if this would
work on a large data set. And my suspicion
is that it might take a long time to calculate
on a large data set. So I want to choose a slightly
different formula here. And again, I didn’t
time it, but my hunch is that this one would
calculate a lot more quickly. I just want to take the
total for each product. So sum range, F4, comma,
criteria range, the product, F4, comma, and
the Aspen criteria is a relative cell reference. Control-Enter and double
click and send it down. That just calculates the total. Well, if we did this
manually for Aspen and looked through here, if we
took that total amount there and we knew what the unique
count of customers was, that would be a different way
to get at the same number. F2. Now we have to
isolate these numbers, because as my denominator here,
I can’t create a unique list based on the numbers alone. I have to pick out
just the numbers for that particular product. So I’m going to use
the SUM function. And then inside SUM,
I’m going to say IF. And this is going
to get pretty crazy. In my Control-Shift-Enter
Mastering Excel Array Formula book on the chapter four Unique
Counts, this is what we did. I’m going to use the FREQUENCY
function, which is notoriously quick at calculating. Now, the data– I need to have in
there only the customer key numbers for that product. So for data array, I’m going to
isolate not not whole customer column, but I’m going
to use a logical test. How many in the Product column,
F4, are equal to this product? If I get a true for
that, then please put in the customer key numbers. F4. Now I’m going to close
parentheses on the IF, and in the data
array I could hit F9. And we could see we
have our 2, 1, and 1. Now, the way FREQUENCY works is
we can count how many numbers occur within a category. Control-Z. Well, comma,
data bins are the category. And we’re counting a
very specific integer, so I’m just going to
give it these three. F4 to lock that. Now technically,
FREQUENCY takes these as the upper value for each
bin, including one extra bin to count any numbers
bigger than three. Now, for FREQUENCY, I’m
going to close that off, and then I’m going
to highlight this. Notice it’s sitting
in the logical test. And watch this. When I F9, it gives me a
count of 2 for customer 1, 1 for customer 2, 0
for 3, and there’s that last category, which
is everything bigger than 3. Since it’s in the
logical test of the IF, any non-zero number will be
interpreted as true, true, true. The rest will be false. Control-Z from that. Comma. The value of true
is I’m counting. So I’m just going to
put a 1 right there. I’m not putting in false at all. Close parentheses on the IF. And there we have it. If I F9, 1, 1, false, false. Similar to our AVERAGE
and AVERAGEIFS, aggregate functions
are programmed to ignore logical
values like false. Control-Z. That’s the
formula for our unique count. If I highlight the whole
sum in F9, there’s my 2. All right, so you ready? There it is. Control-Shift-Enter. I look up to the Formula bar. There’s my curly
brackets verifying that I entered it correctly and
Excel calculated it correctly. So there it is,
another alternative for a single-cell formula
Average of Customer Total Sales by Product. Now actually, I want to
do a third array formula, and then I want
to time all three. Now, notice in this
formula right here– in the SUMIFS criteria two, we
did a function argument array operation. Well, we can do the
same thing with COUNTIFS to count and get
our unique count instead of doing FREQUENCY. I still think that the
FREQUENCY is the fastest. But let’s check this out. COUNTIFS criteria. Well, we have to
look through Product. F4 to lock it. Comma. We have to look at
criteria Product– comma– and then the criteria Range. Customer key, F4
to lock it, comma. And then our function argument
array operation, three items. F4, close parentheses. When I F9, of course,
three answers come out. It found two 1’s,
one 2, and zero 4’s. Control-Z. Now, I’m
going to actually copy that formula element and then
Escape, which means I lost it. But I copied it. Now I want to open
up the clipboard. And notice I have that there. I don’t want to type out
the other formula elements that I’m going to need. So I’m going to copy this
entire formula right here. Control-C. Notice up here in
the clipboard I have the entire second
formula and then that little COUNTIFS bit. Escape. Now I’m going to
come to this cell– equal sign– and come
up to the clipboard and click that entire SUMIFS. Now remember, inside
of the logical test, we had to do the FREQUENCY. But if I F9, that just
gave me that 2, 1, 0, 0. Control-Z. With that highlighted, I’m
going to replace it and put the COUNTIFS there. So I come over to the
clipboard and click. And because we had it
highlighted, that replaces it. So now that F9 is
the logical test. Control-Z. This will also
require Control-Shift-Enter. Double click and send it down. Highlight these. Right click. And on the mini toolbar,
there’s my Format Painter. And click to apply
the formatting. Now I want to go over to another
workbook that you can download. It’s called Excel Magic
1378 TimeArrayFormulas. Now, I actually
closed the clipboard. This VBA code I’m going to use
comes from Charles Williams, an amazing Excel MVP. And what I did is I have
a data set over here. Control-DownArrow. It’s only 5,000 rows, but it
will get the point across. Here’s formula number one. That’s our two SUMIFS. Formula number two
will be the FREQUENCY, and formula number three
will be the COUNTIFS. I went ahead and highlighted
the range right here and timed it– one,
two, three time. There’s the average time. I did it for the second
and the third formula. Then here’s the average for each
and I calculated the percentage change from the fastest. So number two. That is FREQUENCY formula, as
I suspected, was the fastest. Two, SUMIFS. It was about 100% longer in
calculating than the FREQUENCY. And then the COUNTIFS was about
30% longer than the frequency. Now, one thing about
timing like this. This data set was
only 5,000 rows and I only had three
different customer keys. So the unique list
here wasn’t very long. So it always depends on
the size, the type of data, and, of course, the
formula you choose. All right, let’s go back
to our other workbook. Now, back over
here in Excel, now we want to talk
about DAX formulas. Now, DAX formulas are formulas
that we can use in the Values area of a pivot table. Now, in order to
create DAX formulas, you have to have Excel
Power Pivot, which means you have to have the
right version of Excel. Or you can download for
free Power BI Desktop. Now, I’m going to be
using Power Pivot. Now, each one of
those tools have what’s called the data model. And we have to bring
each one of these tables into the data model
and then build a relationship between them. Now, I’ve already clicked
in each one of these tables and clicked the
Add to Data Model. The last one product
I haven’t done. So I click in a single cell
and click Add to Data Model. You can see the two
previous tables are listed, and the dProduct was just added. Now, after you add
the tables, you come up to View, Diagram View. And you want to
build relationships between the tables. Now, this Product table
is a lookup table. In the first column
of the Product table, there is a unique
list of products. Over here we have potentially
many repeat products. So to create a
relationship, we simply drag Product over to Product. And just like that, you see
1, indicating a unique list, over to the many side. Now, the advantage of having
relationships between tables is now we’re allowed
to drag and drop fields from any one of the three
tables into our pivot table. Now let’s go back to Data view. We’re going to click
on the fSales tab. Here’s our table. And we have a very
small table here. In the middle, there’s
this line you can drag. But down below is
called the measure grid. And that’s where you
create your DAX formulas. Now, this is a certain
type of DAX formula that’s called a measure. These are the formulas
we drag into the Values area of the pivot table
that will see the criteria, and the calculation
will change accordingly. Now, I’m going to
click in a cell, and we have to come
up to the Formula bar. We have to create our
name and then our formula. Now, the first name
is going to be Average of Customer Total Sales. Now, notice I didn’t
put by product, because that criteria is going
to come from the pivot table. And we’re allowed to change it. The customer part
of this formula will be static, but
whatever we drop as criteria into the Row area
can change for our formula. And certainly, that is something
that our formulas over in Excel cannot do. Now, in order to type
the rest of the formula, you have to type of colon
and then an equal sign. Now, if you’re in
Power BI Desktop, you type just an equal sign. Now I’m going to type
the SUM function. And from the fSales table, we
want to add the Sales column. So I Down Arrow and Tab. Now the convention for putting
columns into DAX formulas is you always have the
table name, and then in square brackets,
the field name. Now we close parentheses. That formula right there
will respect the criteria in the Row area of
the pivot table. So it’ll calculate
the total for Quad and then Aspen and
Eagle products. Now we want to divide that by– and look at this. There’s a distinct
COUNT function. Now, we’re going to work both
parts of these formulas off of the fSales table. And we want a distinct
count from customer key. And now I close parentheses. The way the data
model will work is when I drag the product
into the Row area, for the row Quad product,
it’ll filter the table so that only the
sales will show up for Quad, which is like
our SUMIFS over in Excel. And then in this column,
the Fact Sales table will be filtered down
just to the customers who bought that product. But then the DISTINCTCOUNT
will give us a distinct count. Now I want to hit
Enter, and we can see that’s the grand overall
answer we got over in Excel. Now, we can actually add
formatting to our formula. So we come up to Formatting. And this is number
formatting we’re going to attach to our formula. Click English. That means any pivot table
we drag this formula into, that number formatting
will follow. Now, this formula is pretty
amazingly straightforward. There is another way
we could do this. Click on the cell below. Click up in the Formula bar. And we’re going to
call this Average of Customer Total Sales, AVEX,
CALCULATE, and SUM, colon, equal, and we’re going to start
with the AVERAGEX function. Now, X functions
are iterators that operate on a particular
table and make a calculation for every row. Now, if you remember
over in Excel, we had to calculate the total
for a particular product for each one of the customers. So that’s what we want to do. We want to iterate over
the dCustomer table. Now, what is– comma– the expression or the formula? Over in Excel, we did some
SUMIFS to get the total. But here, we just have
to use the SUM function. It will see the criteria
from the pivot table. Now, I’m going to Down
Arrow to fSales, Sales. What we’re hoping will happen
is the criteria will flow into here, there’ll be a sum for
a particular product like Quad, and then it will
iterate over this, calculating the
actual total for Quad for each one of these customers. Not only that, but
because there’s a relationship, when Quad gets
selected in the Product table, it will filter the Sales table
down to just Quad records, and the Customer table
will be filtered down to only the customers
who bought Quad. Now, there’s actually going
to be a problem with this. Let’s just close
parentheses and Enter. We can see the total
is not correct. Now, the problem is this. It’s that, yes, the
product criteria will flow in from the
pivot table into SUM and calculate, for
example, Quad total. But that SUM cannot see
the criteria coming from the dCustomer table unless
we add the CALCULATE function around the sum. Now, CALCULATE does
two major things. It can change the filter
context from the pivot table by adding filters. Filter context just
means the criteria coming from the pivot
table, and then we add filters to change it. But the second thing
that CALCULATE does is if it’s in a formula,
any row context, which is the iteration over
each row in the table, will flow into CALCULATE. So CALCULATE can pull
in the filter context from the pivot table and
any row context it sees. So now because we have
SUM inside of CALCULATE, product flows in from the
pivot table, customer flows in from the customer
table, and CALCULATE will make that SUM calculation
with both conditions. Not only that, but to
CALCULATE SUM part of this will sometimes
have three numbers and sometimes it’ll
have two numbers. We will not have that
problem that we had with 0’s like we did in Excel. All right, I’m
going to hit Enter. Down here it’s selected, so now
I add some number formatting. And there we go. All right, let’s go back over to
Excel and create a pivot table. Now I’m going to click
in an empty cell. Insert, Pivot Table. Or I’m going to use
the keyboard Alt-N-V. And look at that. I had an empty cell selected,
and we have a data model. So that’s the default. Use
this Workbook’s Data Model. That’s a fine cell. Click OK. There are our three tables. I’m going to open Product and
drag Product down to Rows. Now I open up fSales. We can see our list of fields,
and there are our measures with that little f of x. I can click and drag. And just like that, look at
the calculations, including that number formatting. Now we can drag this one
down, and both formulas calculate the correct answer. Now I’m going to change
that row label’s design. Report, Layout, and
Show in Tabular. Now, let’s see the real
magic of these DAX measures. I’m going to drag
Product off of Rows and I’m going to drag a
different field down here. Instantly we get a unique
list of manufacturer, and there are our
average of customer total sales for each one
of the manufacturers. Man, that is amazing. All right, in this video,
we saw a DAX formula that used AVERAGEX,
CALCULATE, and SUM; one that used SUM and
DISTINCTCOUNT; over here in Excel we saw one, two,
three different array formulas and even timed them; and
we did our two-step method, AVERAGE IF greater than 0;
and our cross-tabulated table using SUMIFS. All right, that was a
lot of fun with Average of Customer Total Sales
with other conditions. We’ll see you next video.