Microsoft Office Tutorials and References
In Depth Information
PowerPivot DAX Patterns
Chapter 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
PowerPivot DAX Patterns ...............................289
In this chapter, we show you many common DAX calculations using techniques that can be
easily applied to your own Microsoft SQL Server PowerPivot for Excel models. As you will
see, DAX calculations might have significant differences from one another, depending on
the underlying data model. For this reason, you should be sure that you understand the
data modeling concepts presented in Chapter 4, “Data Models,” of this topic.
Calculating Ratio and Percentage
In Chapter 8, “Mastering PivotTables,” you saw some of the Excel features that allow you to
show a value as a percentage of a total. However, you might need to make this calculation
in a measure or in a calculated column, for example, because you need that number as part
of another calculation.
Calculating the percentage of a total for a given value requires a simple ratio operation:
The resulting percentage should be a decimal number between 0 and 1. To get it as a
percentage, you should just set the display format as Percentage. Do not multiply that number
by 100—that is not the right way to calculate a percentage. In fact, when you use the
percentage format, the number is already multiplied by 100 by the formatting function, just
for display purposes. Moreover, you might need to use that value in other expressions (for
example, to calculate a weighted average) and in such a case, you usually need just the
value between 0 and 1.
Calculating Ratio on a Single Denormalized Table
Consider a set of data in a single table, like the one shown in Figure 9-1. You have the total
amount of sales for products, which can be grouped by product model. Each product is
unique and has only one row in this table. The CH09-01-RatioSingleTable.xlsx workbook on
the companion DVD contains this example.