Welcome to Excel Magic Trick number 1,439.
Hey, if you want to download this Excel workbook file,
either the finished file or start
file so you can follow along, click on the link
below the video.
Hey, this is 1,439, last video was 1,438, and both videos,
we're seeing how to create a cross-tabulated OR
and AND logical test sales total report.
Here's the formula version, here's the DAX formula,
and that's what we want to do in this video.
We want to see how to create this cross-tabulated total
for sales based on AND and an OR logical test.
Now in the last video we did formulas,
and the logic for any one of these cells is hey,
please go over to the Sales column
and pick out the sales where the Channel is
Mail Order and the Product is any one of these.
Now really, what this is doing is we're saying,
hey, is the Sale Mail Order and Aspen or Mail Order and Bellen
or Mail Order and Yanaki?
All right, this is the finished file,
let's go over to the start file.
Now the first thing we need to do
is add this to the data model.
Now I do have this extra column here that I don't need.
I actually shot the first video, 1,438, with his column here,
and it won't affect us, so I'm still
going to leave it here for this video.
Now I have Power Pivot, so I can click inside the Excel table
and click Add to Data Model, but I'm
going to show you how to do this without Power Pivot.
As long as you have Excel 2016, we can access the data model,
meaning load this to data model, and then
build our DAX formulas.
So watch this, here's how we're going to do it.
I'm going to click in a single cell,
go up to Insert, Pivot Table, or use the keyboard Alt-N,
V. Now it wants to use the actual Excel
table from the sheet, but I don't want that.
I want to come down and click Add this to the Data Model.
Now I'm going to also click Existing Sheet and Location,
click in cell G14, click OK.
Now we can see our table and it's in the data model.
You can tell that that's a table from the data model
because it has that black line at the top.
If this was just a straight table from the Excel sheet,
that black line wouldn't be there.
Now we want to drag Channel down to Rows.
And now we want to build our measure--
we actually have two of them.
We need a total for Freestyle and for Beginner.
I'm going to go up to the table and right-click Add Measure.
As long as you have Excel 2016, that feature is there.
Now our measure name, this is going to be--
we'll call it Freestyle Sales, and then I'm
going to click down here.
Now we would like to just use the SUM function, Tab,
and I'm going to Down Arrow to fSales Sales column.
Always the table name, and then in square
brackets the field name.
Now if I did this, it would add all of the sales from our Sales
column for the Dealer, In Store, Mail Order, and Web Site.
But when I copy this formula down in the pivot table,
I want it to only pick out the products
from the freestyle group.
So in order to alter the filter context,
we put whatever our measure is inside the CALCULATE function.
I type the letter C and then Tab.
There's our expression, that's the formula.
Comma, at the end, and we can change the filter.
Remember, we want-- since it's freestyle,
we want Carlota or Quad.
So in filter argument, I'm in the type
F-S Down Arrow to Product and Tab.
Now I'm going to type a space and use the IN operator just
as we did two videos ago, Space.
Two videos ago we use the Values functions to pick out values
from a column, but here, we're simply
going to type it in using the correct syntax.
Curly brackets will house the conditions
that are valid for our OR logical test.
IN double quotes, I'm going to type Quad, and double quotes,
comma, double quotes, in then Carlota, and double quotes,
close curly bracket.
Now IN, if you know IN in SQL, it's the same here.
It will go through that column and pick out any transactions
that are Quad or Carlota.
Now I'm going to close parentheses on the CALCULATE,
and that will work.
Now I love this about DAX.
I can add my number formatting directly to the formula.
I'm going to increase the decimals to two, click OK.
And there's the freestyle sales.
You can simply drag down to Values, and boom, there it is.
Now we need to add our second measure-- right-click,
Add Measure.
And the measure name, Beginner Sales, click down here.
Now I'm going to put the SUM function, F-S, and then
Down Arrow to Sales.
Now actually when I close parentheses,
we could have created over here a third measure,
we could've started with Sales and then use
that total sales in both Freestyle and Beginner Sales,
but I only wanted these two options,
so I'm going to repeat the SUM function once in each formula.
Now I'm not going to use CALCULATE and then IN
and then list the three since there's only two categories,
I'm simply going to say, hey, the SUM--
that's a total of everything-- minus and then square bracket
to get to our dropdown list, and Down Arrow,
and we see that the f of x icon, that means measure--
there's our Freestyle Sales tab, and that we'll do it.
Currency, two decimals, click OK.
Now I can drag this down there, and boom, there it is.
Now if you wanted to see a 0, we can right-click, Pivot Table,
Options.
For empty cell, show, and we'll put a 0, click OK.
And there we go.
For any intersecting sale, we have the Channel
and our OR logical test for the products in that group.
All right, that was a little fun.
Right-click, Edit Measure with DAX to do an AND and OR logical
test to create our cross-tabulated table.
All right, we'll see you next video.