- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using the RELATED Function in the PowerPivot Grid
Once you have defined a relationship between two worksheets, you can refer to the other worksheet when building formulas in the PowerPivot grid. The RELATED function is a simpler way to do VLOOKUP. Say that you wanted to calculate sales per square foot. Revenue is in the Demo worksheet and Square Feet is in the StoreInfo worksheet. In Regular Excel, you might build something like this:
=E2/VLOOKUP(A2,StoreInfo!$A$2:$B$144,2,False)
In the PowerPivot grid, the formula becomes this:
=demo[Revenue]/Related(StoreInfo[Selling SF])
With VLOOKUP, you had to define the lookup value, the table, which column in the table to return, and specify FALSE to show that you wanted an exact match. The RELATED function does the same lookup by telling PowerPivot to follow the existing defined relationship to get the Selling SF field from the StoreInfo table. PowerPivot figures out that it has to use the Store Number field in both tables to return the correct lookup value.