Evil Angel on YouTube asks if you can do a VLOOKUP in a Pivot Table Calculated Field. I am fairly sure this won't work in a regular pivot table based on the pivot table cache. But if you build a pivot table using the Data Model, then you can build a DAX Measure that will do the equivalent of a VLOOKUP.
This video shows how to create a relationship between the lookup table and the data table.
It shows how you can pull in text fields from the lookup table.
It shows how to use the =RELATED function directly in the power pivot grid, but this calculation fails on the total row.
Finally, it shows how to use a Measure to perform the calculation.
Table of Contents
(0:00) Can you VLOOKUP in a pivot table calculated field?
(1:02) Converting data to a table and adding a relationship
(1:37) Building a pivot table based on two tables
(2:19) Adding a RELATED calculation in the Power Pivot grid
(3:33) The calculation fails on Grand Total
(3:55) Adding a DAX Measure for Sales Per Square Foot
This video shows how to create a relationship between the lookup table and the data table.
It shows how you can pull in text fields from the lookup table.
It shows how to use the =RELATED function directly in the power pivot grid, but this calculation fails on the total row.
Finally, it shows how to use a Measure to perform the calculation.
Table of Contents
(0:00) Can you VLOOKUP in a pivot table calculated field?
(1:02) Converting data to a table and adding a relationship
(1:37) Building a pivot table based on two tables
(2:19) Adding a RELATED calculation in the Power Pivot grid
(3:33) The calculation fails on Grand Total
(3:55) Adding a DAX Measure for Sales Per Square Foot
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2456. Can you use a VLOOKUP in a pivot table calculated field?
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today, a question from You Tube.
Can we use a VLOOKUP formula in a pivot table calculated field?
I don't think so. Not in a regular calculated field.
But we could use it in a measure using the DAX formula language.
I don't have a video on this so I thought I would just quickly show this.
What I have over here in the orange table is a detailed data set, store ID, date, sales.
And then in the green is a lookup table Store ID and then selling square feet.
That's a common metric that we want to do. How many sales per square feet? Because you're paying rent based on square feet, so if it's a bigger store you want to have more sales in order to justify the stores existence.
N now, in order to use DAX measures, these are going to have to be tables.
From this first one I press control T to format as a table.
They call it table one. I'm just going to rename it to be called Data.
From the lookup table, Ctrl+T. They call it Table2.
I'm going to call it Lookup.
Data, out here in the far right hand side, this is new in Excel 2016: Relationships. I'm going to create a New relationship.
From the table called Data, there's a field called Store.
It's related to the lookup table based on the store ID.
Click OK, click close.
We'll just come out here where we are going to build our pivot table. And Insert Pivot Table from the Data Model.
Click OK.
I can build a report here with Store Name from the Lookup table and Sales from the Data table.
Alright, but now here's where I want to do the calculation.
I want to take that sum of sales and divide it by the square foot for the store.
So I needed to basically do a VLOOKUP here of equal this cell divided by that cell.
And if I would come in and try and do this with a calculated field, there's no good way to do the VLOOKUP in there.
Alright, So what I'm going to do instead is come here to the Power Pivot tab. Go to Manage.
You see, we have two tabs across the bottom data and look up.
I'm going to build the field here in the Data table.
Let's say we want to take our sales for this row, divided by….
Instead of Vlookup - the DAX formula language and Excel are very similar.
There's a few things that have changed, like TEXT in Excel becomes FORMAT in DAX.
And VLOOKUP in Excel becomes RELATED in DAX.
So we want the RELATED field and we want selling square feet right? It's a lot simpler that VLOOKUP.
It just says, “Hey, look, we've already told you what the relationship is, just travel over to the other table and get the selling square feet”.
So 7384 divided by the size of the store is $6.36 per square foot. Calculated column 1 - that's a horrible name.
I'll rename this to be Sales Per SF (for square foot).
Looks good. Close.
See when we come back here to Excel now over in the PivotTable Fields, we now have sales per square foot. Right, we add that in.
You know what? That's not it.
I mean, it looks right. Equal J12 divided by Freehold.
That number right there, that number is correct 2357.
But this number 46,000? That's not doing what we want, shoot.
OK, let's try this. Second try.
We're going to add selling square feet right into the pivot table.
And hopefully this will total to 14,930. It does OK good.
So now we have two measures in here, Sum of Sales and Sum of Selling Square Feet.
Now this is a little bit different. We're not going to add it to the power pivot grid.
We're going to add it to the pivot table, which kind of forces it to do the calculation of the summary level.
So over here on the Data heading I'm going to right click and say Add Measure. And we'll call this Sales Per Square Foot Try Two.
Right and we're going to take that Sum of Sales number - so hit the left square bracket.
We'll get a list of all the measures, so we have Sum of Sales.
Divided by. And it's called Sum of Selling Square Feet.
We can format this as a currency with two decimal places, click OK.
Get a new measure over here. We can add that in.
You see that these 2357 these all match.
But the difference is down here in the grand total that 3022 is taking the 45 million and dividing it by the 14,930 total square footage for the whole company and coming up with a 3022.
That is a better calculation than this sales per square foot.
OK, and then the big question can we remove this Sum of Selling Square Feet? Can we take that out?
And it continues to work. All right there you go.
So trying to do a VLOOKUP in the pivot table by using DAX and a Power Pivot pivot table seems to give us what we need.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank Evil Angel for sending that question in.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today, a question from You Tube.
Can we use a VLOOKUP formula in a pivot table calculated field?
I don't think so. Not in a regular calculated field.
But we could use it in a measure using the DAX formula language.
I don't have a video on this so I thought I would just quickly show this.
What I have over here in the orange table is a detailed data set, store ID, date, sales.
And then in the green is a lookup table Store ID and then selling square feet.
That's a common metric that we want to do. How many sales per square feet? Because you're paying rent based on square feet, so if it's a bigger store you want to have more sales in order to justify the stores existence.
N now, in order to use DAX measures, these are going to have to be tables.
From this first one I press control T to format as a table.
They call it table one. I'm just going to rename it to be called Data.
From the lookup table, Ctrl+T. They call it Table2.
I'm going to call it Lookup.
Data, out here in the far right hand side, this is new in Excel 2016: Relationships. I'm going to create a New relationship.
From the table called Data, there's a field called Store.
It's related to the lookup table based on the store ID.
Click OK, click close.
We'll just come out here where we are going to build our pivot table. And Insert Pivot Table from the Data Model.
Click OK.
I can build a report here with Store Name from the Lookup table and Sales from the Data table.
Alright, but now here's where I want to do the calculation.
I want to take that sum of sales and divide it by the square foot for the store.
So I needed to basically do a VLOOKUP here of equal this cell divided by that cell.
And if I would come in and try and do this with a calculated field, there's no good way to do the VLOOKUP in there.
Alright, So what I'm going to do instead is come here to the Power Pivot tab. Go to Manage.
You see, we have two tabs across the bottom data and look up.
I'm going to build the field here in the Data table.
Let's say we want to take our sales for this row, divided by….
Instead of Vlookup - the DAX formula language and Excel are very similar.
There's a few things that have changed, like TEXT in Excel becomes FORMAT in DAX.
And VLOOKUP in Excel becomes RELATED in DAX.
So we want the RELATED field and we want selling square feet right? It's a lot simpler that VLOOKUP.
It just says, “Hey, look, we've already told you what the relationship is, just travel over to the other table and get the selling square feet”.
So 7384 divided by the size of the store is $6.36 per square foot. Calculated column 1 - that's a horrible name.
I'll rename this to be Sales Per SF (for square foot).
Looks good. Close.
See when we come back here to Excel now over in the PivotTable Fields, we now have sales per square foot. Right, we add that in.
You know what? That's not it.
I mean, it looks right. Equal J12 divided by Freehold.
That number right there, that number is correct 2357.
But this number 46,000? That's not doing what we want, shoot.
OK, let's try this. Second try.
We're going to add selling square feet right into the pivot table.
And hopefully this will total to 14,930. It does OK good.
So now we have two measures in here, Sum of Sales and Sum of Selling Square Feet.
Now this is a little bit different. We're not going to add it to the power pivot grid.
We're going to add it to the pivot table, which kind of forces it to do the calculation of the summary level.
So over here on the Data heading I'm going to right click and say Add Measure. And we'll call this Sales Per Square Foot Try Two.
Right and we're going to take that Sum of Sales number - so hit the left square bracket.
We'll get a list of all the measures, so we have Sum of Sales.
Divided by. And it's called Sum of Selling Square Feet.
We can format this as a currency with two decimal places, click OK.
Get a new measure over here. We can add that in.
You see that these 2357 these all match.
But the difference is down here in the grand total that 3022 is taking the 45 million and dividing it by the 14,930 total square footage for the whole company and coming up with a 3022.
That is a better calculation than this sales per square foot.
OK, and then the big question can we remove this Sum of Selling Square Feet? Can we take that out?
And it continues to work. All right there you go.
So trying to do a VLOOKUP in the pivot table by using DAX and a Power Pivot pivot table seems to give us what we need.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank Evil Angel for sending that question in.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Last edited by a moderator:
