Microsoft Office Tutorials and References
In Depth Information
The technique is always to count the rows returned by a FILTER call. The predicate of the filter
compares the value of the ModelSalesAmount of the row iterated by the FILTER call with the
ModelSalesAmount value of the current row.
Explaining the ModelRankTotal Calculated Column
The purpose of the CALCULATE call in the predicate is to transfer the current row
context of the table into a filter context. The FILTER defines an iteration in just the Model
column, so the result of the ALLEXCEPT call to the CALCULATE function is to define a filter
context formed by just the Model column values iterated by the FILTER call and no filter
at all on other columns. (Otherwise, the current row filter for the ModelRankTotal
calculation would be applied instead.) There could be several rows in the Sales table for
a model, but the CALCULATE statement requires VALUES( Sales[ModelSalesAmount] ),
which returns just one value because all the rows filtered are of the same product model
and have the same value for ModelSalesAmount. Thus, the CALCULATE statement returns
the ModelSalesAmount value of the row iterated by FILTER, which is compared with the
ModelSalesAmount value of the current row for which the ModelRankTotal calculated
column is computed. Because the filter just iterates all the Model column values, it does
not affect the current row of the ModelSalesAmount column, even if these columns are
on the same table.
An example of the resulting PowerPivot table is shown in Figure 9-13.
FIguRE 9-13 Ranking for product and models in calculated columns of a PowerPivot table.
The ranking calculations shown in this section can be easily adapted to other scenarios in
which data is stored in a single denormalized table. If you have normalized data in other
tables, the following section describes a few differences in procedure.