Microsoft Office Tutorials and References
In Depth Information
Now that you have created the relationship, you can easily analyze sales sliced by price band,
like the one shown in Figure 10-6.
FIguRE 10-6 Analysis of sales based on price bands.
If you need to change the price bands, you can easily do it in the original table, recalculate the
expanded table, and refresh the PowerPivot data to analyze different banding algorithms.
Tip As you might have noticed, bands are sorted alphabetically. To sort them correctly, it might
be useful to add a prefix to band names, something like 1-LOW or 2-MEDIUM works fine. Another
useful option is the one about custom lists discussed in “Custom Sorting in PivotTables” in Chapter 8,
“Mastering PivotTables.” The choice, obviously, depends entirely on your needs.
Banding is a common problem. We discussed it for prices, but as you might imagine, there are
a lot of different situations for which you need to categorize values in different ways, and this
technique can be easily adapted to different situations. Nevertheless, before taking banding
as a solved problem, let us look at some other techniques that can be used to deal with the
Banding with Basic DAX
The previous solution to banding works fine, but it requires you to have some knowledge
of Visual Basic for Applications coding. We think that you, as a reader of this topic, want to
become a DAX expert rather than a Visual Basic for Applications expert. So here is an
interesting question: can we solve the same banding problem with DAX only? As you might
imagine, the answer is yes, and the analysis of a different data model is indeed very
interesting because it involves some advanced DAX coding. You can find this workbook on the
companion DVD in the file CH10-02-BandingPricesDAX.xlsm.
In Figure 10-7, you can see the PriceBands table loaded in PowerPivot, where we have already
adopted the number in front of the price band name to accomplish correct sorting.