Microsoft Office Tutorials and References
In Depth Information
PowerPivot Data Model Patterns
Chapter 10............................................ 317
PowerPivot Data Model Patterns......................... 317
Having seen all the features of Microsoft SQL Server PowerPivot for Excel and some advanced
models in DAX, we now want to bring all our knowledge to work and analyze ways to use
PowerPivot from the data model point of view.
This chapter presents some problematic patterns the PowerPivot user might need to face,
and for each pattern, we provide a solution, which makes use of all the techniques we have
described to you up to now. Sometimes a problem has more than one solution; when we
feel that there are many ways to solve the same problem and all of them are instructive,
we present all of them and point out the differences among them.
In Chapter 9, “PowerPivot DAX Patterns,” we focused on DAX complex formulas; this chapter
deals with different complex data models.
Some patterns might be hard to follow because the data model underlying them is a very
complex one. Nevertheless, we suggest that you study all the examples because they serve
as a useful toolbox that you can adapt to many different situations. Moreover, as is always
the case with examples, it is better to focus on the way a problem is solved than on the
problem itself because you are likely to face similar, but not identical, situations.
A very common analysis is to convert continuous values into separated bands, to make it
easier to perform analysis on them. An example of banding is the analysis of sales divided
by price ranges. You might be interested in grouping different prices into categories (for
example, high, medium, and low) and analyze sales of products using those categories.
In Figure 10-1, you can see that by using a standard PivotTable, you can put the product
price in rows and group sales by product price. Nevertheless, you end up with a lot of rows
in which each single price is separated by others. This makes the analysis difficult and the
report nearly useless. Moreover, the item price that you use to slice data is the list price,
and you cannot use the discounted one, if applicable. You can find this workbook on the
companion DVD in the file CH10-01-Banding.xlsm.