Part V: Understanding Power Pivot and Power Query
Take a moment to follow these steps to create a new calculated column that displays a dis-
counted revenue for each transaction in the InvoiceDetails table:
- In the InvoiceDetails table, click the first blank cell in the empty column
labeled Add Column on the far right.
- In the Formula bar, type the following:
=RELATED(
As soon as you enter the open parenthesis, a menu of available fields (shown in
Figure 37.16) will be displayed. Note that the items in the list represent the table
name followed by the field name in brackets. In this case, we’re interested in the
Customers[Discount Amount] field.
FIGURE 37.16
Use the RELATED function to look up a field from another table.
The RELATED function leverages the relationships that you defined when creating the data model to perform the
lookup. So, this list of choices will contain only the fields that are available based on the relationships you defined.
- Double-click the Customers[Discount Amount] field and then press Enter.
- Power Pivot will automatically rename the column to Calculated Column 1.
Double-click the column label and rename the column Discount%.
- Starting the next column, click the first blank cell in the empty column labeled
Add Column on the far right.
- In the Formula bar, type
=[UnitPrice][Quantity](1-[Discount%]) and then press Enter.
- Power Pivot will automatically rename the column to Calculated Column 1.
Double-click the column label and rename the column Discounted Revenue.
The reward for your efforts will be a new column that uses the discount percent from the
Customers table to calculate discounted revenue for each transaction. Figure 37.17 illus-
trates the new calculated column.
