Channel: MrExcel.com

Category: Howto & Style

Tags: excelcan you use vlookup in pivot table calculated fieldpower pivot in excelmrexcelrelated function in daxbuilding a pivot table from two tables using the data modela dax measure is like a calculated field but with more optionspivot tables based on data modelmicrosoft excelbill jelen

Description: 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. Check out my courses on Retrieve: 'mrexcel.retrieve.com/store/# 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 (5:48) Check out my courses on Retrieve