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.