Chapter 37: Adding Formulas to Power Pivot
37
FIGURE 37.17
The final Discounted Revenue calculated column using the Discount% column from the
Customers table
Nesting functions
In the previous example, you first created a Discount% column using the RELATED func-
tion and then used that column in another calculated column to calculate the discounted
revenue.
It’s important to note that you don’t necessarily have to create multiple calculated columns
to accomplish a task like this. You could, instead, nest the RELATED function into the dis-
counted revenue calculation. This would be the syntax for the nested calculation:
=[UnitPrice]*[Quantity]*(1-RELATED(Customers[Discount Amount]))As you can see, nesting simply means embedding desired functions within a calculation.
In this case, instead of using the RELATED function in a separate Discount% field, you can
embed it directly in your discounted revenue calculation.
Nesting functions can definitely save time and even improve performance in larger data
models. On the other hand, complicated nested functions can be harder to read and
understand.
Understanding Calculated Measures
You can enhance the functionality of your Power Pivot reports with another kind of calcu-
lation called a calculated measure. Calculated measures are used to perform more complex
calculations that work on an aggregation of data. These calculations are not applied to the
Power Pivot window like calculated columns. Instead, they are applied directly to your
PivotTable, creating a sort of virtual column that can’t be seen in the Power Pivot window.
You use calculated measures when you need to calculate based on an aggregated grouping
of rows.