Microsoft Office Tutorials and References
In Depth Information
As you can see, the Grand Total value is incorrect because more than one product is active in
the filter context, so the ranking formula does not work as expected. A possible solution is to
intercept that condition and return NULL instead, as in the following ProductRank definition
(we will not repeat this check in following measures, just for the sake of simplicity):
ProductRank = IF ( COUNTROWS (VALUES (Sales[Product])) > 1,
BLANK (),
COUNTROWS( FILTER( ALL( Sales[Product] ),
CALCULATE( SUM( Sales[SalesAmount] ) )
> CALCULATE( SUM( Sales[SalesAmount] ),
VALUES( Sales[Product] ) ) )
) + 1
)
A similar ModelRank measure can be defined by means of the following formula,
which is conceptually identical to ProductRank but references Sales[Model] instead of
Sales[Product]:
ModelRank = COUNTROWS( FILTER( ALL( Sales[Model] ),
CALCULATE( SUM( Sales[SalesAmount] ) )
> CALCULATE( SUM( Sales[SalesAmount] ),
VALUES( Sales[Model] ) ) )
) + 1
In Figure 9-12, you can see the ModelRank measure in action. In this case, product models in
the rows are sorted by name, but the ModelRank column displays the right ranking regardless
of the order of the rows.
FIguRE 9-12 Ranking for product models sorted alphabetically.
Search JabSto ::




Custom Search