Microsoft Office Tutorials and References

In Depth Information

In case you want to make a fixed calculation of the ranking instead of a dynamic one, or if you

want to use the ranking as a filter for browsing data in the PivotTable, you might want to get

it through a calculated column in the PowerPivot table. Such a column can be used to get a

fixed attribute for each product/model, which is not going to change following the selection

made in the PivotTable. You can use the following DAX expression for a ProductRankTotal

calculated column:

ProductRankTotal = COUNTROWS( FILTER( ALL( Sales ),

Sales[SalesAmount] > EARLIER( Sales[SalesAmount] ) )

) + 1

This formula is simpler than the one used for the corresponding measure in the PivotTable

because it can make use of the EARLIER function, having a row context external to the

FILTER call.

Explaining the ProductRankTotal Calculated Column

The formula counts the rows that have a value of SalesAmount greater than the

SalesAmount of the current row for which the ProductRankTotal column is calculated.

The EARLIER function returns the value of SalesAmount for that current row instead of

using the row context defined by the FILTER iteration over the Sales table.

The calculation that is required to define a ranked calculated column for a product model is a

little bit more complex in this case because there can be more rows for each model. (Previously,

the assumption that there is a single row for each product simplified the calculation.) First of

all, you have to calculate the sales amount for the model, which is a group of products, by

using the same ModelSalesAmount calculated column you used in the “Calculating Ratio

and Percentage” section, earlier in this chapter:

ModelSalesAmount = CALCULATE( SUM( Sales[SalesAmount] ),

ALLEXCEPT( Sales, Sales[Model] ) )

If you look carefully at the preceding formula, you will see a similar statement in the definition

of the ModelRankTotal calculated column:

ModelRankTotal = COUNTROWS( FILTER( ALL( Sales[Model] ),

CALCULATE( VALUES( Sales[ModelSalesAmount] ),

ALLEXCEPT( Sales, Sales[Model]) )

> Sales[ModelSalesAmount])

) + 1