Microsoft Office Tutorials and References
In Depth Information
You can define a ProductRank measure using the following DAX expression:
ProductRank = COUNTROWS( FILTER( ALL( Sales[Product] ),
CALCULATE( SUM( Sales[SalesAmount] ) )
> CALCULATE( SUM( Sales[SalesAmount] ),
VALUES( Sales[Product] ) ) )
) + 1
The idea is to count how many products have a SalesAmount value higher than the current
product. (The formula computes the ranking for a single product, so it does not work if
many products are active in the PivotTable for the calculated cell.)
Explaining the ProductRank Measure
The first parameter passed to FILTER removes the filter on the Product column and makes
the FILTER function iterate over the list of all the products, regardless of other filters active
in the PivotTable. However, these filters determine whether the following filter expression
passed to the FILTER function calculates or does not calculate a value for each product.
The CALCULATE( SUM( Sales[SalesAmount] ) ) expression returns the value for the
product iterated by FILTER only if this product is visible in the current filters of the PivotTable.
For example, if there is a filter on the product model in the PivotTable, only products of
that model are calculated. Then a comparison is made against the SalesAmount of the
products that are active in the evaluated cell of the PivotTable.
You might wonder why an EARLIER function is not used here. The reason is that EARLIER
needs a previous row context, and in this case, there is no row context outside of the FILTER
call. In fact, you use EARLIER later for calculated columns. In this case, the SalesAmount
of the current product in the PivotTable is obtained by filtering the CALCULATE function
with the expression VALUES( Sales[Product] ), which is evaluated before the FILTER
function and returns the list of the products that are active for the evaluated cell. (In theory,
there should be just one of these.)
In Figure 9-10, you can see an example of the ProductRank measure in a PivotTable that
contains all the products sorted by SalesAmount in descending order.