Microsoft Office Tutorials and References

In Depth Information

Chapter 9

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.