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.