Welcome to Excel Magic Trick number 1418.
Hey, if you want to download this Excel
workbook so you could follow along, click on the link
below the video.
Hey, last video, we saw how to rank
for unit profit and total profit,
either for products or products within manufacturer.
But in the last video, we used formulas.
This video, we want to see how to do it with pivot tables.
Again, here is the last video.
And then the next video after this
will be the same exact example, but with dax formulas.
Now, here's our same two tables we had in the last video.
We have, yes, the products from here
and the profit for each transaction.
Then we have a lookup or dimension
table with products and retail price and standard costs.
And then over here is our finished report
we're trying to create, where we calculate total profit
for each product, rank all the profits.
Then we rank within each manufacturer, just
those profits.
Then we need to calculate profit per unit,
rank those, and rank unit profit within manufacturer.
Now formulas, we were allowed to just make
our formulas all in one report.
Our pivot tables, we're going to run
into a little bit of trouble trying
to do different calculations in the same pivot table.
But we'll see if we can create a workaround
to get all the calculations together.
Now, I've clicked on the sheet one 1418.
Here's our table.
All right, so a pivot table, we have
proper data set field names at the top records, and rows,
empty cells all the way around for both tables.
So we can start off by calculating product total
profit.
I click in a single sale.
Go up to Insert Pivot Table, or I use the keyboard Alt-N-V.
Now, I want to put this existing sheet,
I'm going to try and put it right in M6.
Click OK.
Now I'm going to scroll over.
Now, the beautiful thing about pivot tables
is, that's the field from our transaction table
that has lots of duplicate products.
But when I drag this down to rows,
I instantly get a unique list of all our products.
Not only that, but when I drag this Profit field, which
is a number field down, to Values, it defaults to Sum.
That is so much easier than creating this list ourself
and using the SUMIFs functions like we did last video.
Now, I do need to format this.
So I'm going to come to one cell in the Value area.
I'm not going to have to highlight everything, just
one cell.
Right-click, Number Formatting, not the Format Cells.
We want to use Number Formatting.
This dialog box, which has only one tab,
not all the tabs like the normal Format Cells dialog box.
This one number tab dialog box will actually
add Number Formatting to the field, not the cells.
All right, I'm going to keep it like that.
Click OK.
Now, let's drag Profit down again, right below.
And now I can change the calculation
by coming to the top.
Right-click, and there's two ways we can change calculations
in a standard pivot table.
We can use Summarize Values By, which changes the functions,
including More Options to get to all 11 functions.
Or here it is, Show Values As, so much power
in a standard pivot table.
We could do percent of grand total, difference from period
to period, running total, all sorts of them.
And down here, there is our ranks
we can do inside a pivot table.
I'm going to say Rank Largest to Smallest.
Now, it's going to ask us which field.
And we only have one field for the time being,
so we don't actually even need to worry about this.
When I click OK, there is our rank.
Just like we did with our formulas,
Quad had the largest total profit.
Darnell Fast Catch had the second largest total profit.
Now I'm going to change this name up here.
Total Profit, and Enter.
Now, I'm not going to change the title yet here,
because we actually still need to have Manufacturer
as our criteria in the row area.
And it's going to change this calculation.
Let's drag Manufacturer down to Rows,
but I want it very carefully above the product.
When I click and drop, well, there it is.
This is a pivot table.
So these calculations look at both of the conditions, Product
and Manufacturer.
So it calculates our second calculation we want,
which is ranking the profits for product
within the manufacturer.
So that'll properly be our name up here.
Rank Total Profit for Product Within Manufacturer.
And I can even come up and change the width of the column.
Now I'm going to right-click this pivot table and point
to Pivot Table Options.
And I'm going to uncheck on the Layout and Format
the Autofit column widths on update.
Click OK.
Now, I'd like to change this.
I do not want subtotals.
I want to repeat the manufacturer name.
And I do not want those little dashes.
Actually, right-click, I forgot that's down in Pivot Table
Options over on Display.
Show expand collapse button, so I want to get rid of that.
Now let's go up to Design over to Report Layout.
Repeat all item labels.
Not only that, but let's get rid of the subtotals.
So I come over to layout.
Do not show subtotals.
Change the column widths.
So so far, this is looking good.
We have total profit and our first rank for total profit.
But we need to rank all of the profits
regardless of what manufacturer they are.
So if we try to drag Profit down over here,
right-click, Show Values As, there's
just not a calculation down here.
At least I have no idea how to get the pivot table to do this.
Now, with formulas we can do it.
And later in our next video with DAX,
we can build a formula to do it.
So I'm going to remove this Sum of Profit.
And I'm going to cheat.
I'm going to build a second pivot table right here.
Scroll over.
Click in a single cell, Alt-N-V open up Create Pivot Table.
Scroll over.
There's our table, existing worksheet.
I'm going to be really dangerous.
I'm going to put this right next to the other pivot table.
Click OK.
Now we're in drag Product down here to Rows.
There's our unique list.
Now we drag Profit over to Values.
Right-click, Show Values As, and down
to Rank Largest to Smallest.
And because we have a single field here, we simply click OK.
And look at that.
There's the Quad at number 1.
There's the Darnell Fast Catch at number 2.
But we have a problem here.
It's not going to line up.
This of course, is sorted on two fields.
Now, we could manually move everything.
Notice MTA, Aussie Quad.
Well, in a pivot table, you can point to the edge.
And when you drag, notice you can
move the entire row or record in this pivot table using
that green bar.
I'm going to Control-Z. I don't want
to rely on manually doing it.
The only way I know how to do it is
to sort based on a custom list.
So I'm going to copy this right here, Control-C, paste it
somewhere down out of sight below, Control-V,
and then highlight, very carefully.
There's my custom list I want as an option in our Sort feature.
We go up to File, Options.
Over here to Advanced.
Scroll down to General.
And there it is, Edit Custom List.
Now, here I'm going to say Import because I've already
selected it down here.
Import, Add, click OK.
Click OK.
Now, I have to do this in the few steps.
I'm going to click in this column of Products.
Come up to the Sort, More Sort Options.
Ascending, I don't really want to Ascending.
I want product though to be sorted by More Options.
Uncheck Sort automatically, and here's our dropdown
for our custom list.
And there it is--
MTA, Aussie Quad, Kangaroo.
Click OK.
And there we go.
Now, even though we have two different pivot tables
with different criteria in each of the row area,
the items are lining up.
I'm going to rename this, Profit TP, for Total Profit,
And Enter.
Now we're going to have to create two more pivot tables
right next to each other.
But these pivot tables will be based on--
and if we go look at the Product table,
we have the Product and Manufacturer there.
And we have our Retail and Standard Cost,
which we're actually going to have to subtract building
a formula in this pivot table.
Now I'm going to click in a single cell, ALT-N-V. There's
the name of the table.
We want out on the existing sheet--
oh, this is dangerous-- right next to the other pivot table.
Click OK.
Scroll over.
Of course it's dangerous, because if we pivot anything
here, it'll give us a warning, but it
will try to replace the other pivot table.
Now, we have to have the same conditions
in our first pivot table, so Manufacturer in the Row,
Product below Manufacturer in the Row.
Right-click, Pivot Table Options, I'm going to say,
uncheck Autofit, Display, uncheck Show expand.
Click OK.
Design, Report Layout, repeat all items,
Subtotal do not show.
Now we'll hide these in a second because we already
have them over here.
But now, well, wait a second.
We have Unit Profit.
That actually is leftover.
I should have deleted that.
I have to show you how to create that formula,
because it's actually leftover from when I created it
on the answer sheet.
We need to subtract these two fields.
And we are allowed to do that in a pivot table.
We go up to Analyze, over to Field, Items, and Sets,
dropdown, and Calculated field.
Now, I'm going to click the dropdown Unit Profit.
Delete, pretend all of this is not there.
Now we're going to name this, Profit per unit.
And down here in the formula, I can simply say, hey,
there's the Retail Price minus the Standard Cost.
Now when I click OK, there is a new calculated field,
and we can see it in our pivot table.
Right-click, Number Formatting, Currency.
And I'm going to increase this, since some of our prices
have decimals past the penny position.
I'm going to scroll over.
Drag Profit per unit a second time, right-click.
Show Values As, and we want to say Rank Largest to Smallest.
Well, we have two here.
But by selecting Product, it will rank the Product
within the Manufacturer.
Click OK.
And so down here, we can start to compare, for turning point,
profits went 1, 2, 3.
But over here, the profit per unit went 1, 2, 3.
Now we need to rename these.
I'm going to have to cheat here because Profit per Unit
is the same name as a field, so I'm
going to put a space there to trick it.
And then this will be Rank Unit Profit, Product
within Manufacturer, and Enter.
We could add wrap text if we wanted,
but I'm not going to do that.
Now, let's come over and hide these.
Highlight S and T, right-click, Hide.
So are cheater multiple pivot table
report that looks like one is starting to emerge.
We still have one more table we need to create.
I'm going to click in a single cell, Alt-N-V, Existing Sheet.
Click Location.
I'm trying to put it right in W6.
Click OK.
Now, this is just straight product.
We're definitely going to have to sort this, but look at that.
It's picking up our Custom Sort.
Now I simply drag Profit per Unit.
Right-click, Show Values As, Rank Largest to Smallest.
Product is OK, click OK.
I'm going to come up here and type something like Rank Unit
Profit, and Enter.
And now we need to hide this last column,
W. Right-click, Hide.
Come over to Q, right-click, Hide.
And there is our hacked together multiple pivot tables.
We have Manufacturer and Product.
We have Total Profit, Rank Total Profit
Product within Manufacturer.
F2, I'm going to double-click.
This is actually Product.
So we have a Rank Product for Total Profit, Profit per Unit,
ranking the unit profit product within manufacturer,
and then just straight ranking the unit
profit for all the products.
Any time we look at a particular record in our report,
there's Gel Boomerangs, Quad, total profit,
the first rank within Gel Boomerang profits,
first rank overall for total profits.
There's our Profit per Unit.
It is ranked first within the three Gel Boomerang products,
but third overall in terms of unit profit.
All right that was a bunch about pivot tables
and how to rank with a single and double condition.
Last video, we saw how to do it with Excel formulas,
this video, pivot tables.
Our next video, 1419, will be same calculations
with DAX formulas.
All right, we'll see you next video.