Microsoft Office Tutorials and References
In Depth Information
FIguRE 9-9 Standard deviation analysis.
The standard deviation gives important information. Rows in Category A have a distribution
of Value that is usually nearer to the Category average than rows in Category B.
Ranking over a Measure
The ranking of products, customers, or other entities is sometimes an implicit action of the
user interface (like the PivotTable) that is obtained by sorting data using a specific criteria
(for example, the sales amount). However, you might need to explicitly use the rank of an
entity to make further calculations or to do particular presentations of data (for example,
highlighting entities with specific rank conditions). So in this section, you see how to define
DAX expressions to calculate the ranking of an attribute over a particular measure. You use
the same sets of data previously used in the section “Calculating Ratio and Percentage” at the
beginning of this chapter.
Calculating Ranking on a Single Denormalized Table
For example, consider a set of data in a single table, like the one you saw in Figure 9-1. You
can calculate the ranking for products and models over sales amount. Remember that each
product is unique and has only one row in this table, but there are several products (thus,
rows) for each model. Moreover, notice that there are a few changes in the calculation,
depending on whether or not the attribute to be ranked and the values used for ranking
are in the same table. In this first example, all the data is in the same table because it is
the easiest situation. You can find this example in the CH09-04-RankingSingleTable.xlsx
workbook on the companion DVD.