Microsoft Office Tutorials and References
In Depth Information
Calculating Ranking in Multiple Normalized Tables
The scenario of data normalized in multiple tables is identical to the one you saw in Figures
9-4 and 9-5 in the first part of this chapter: you have a Sales and a Products table. In this
scenario, you have a unique product for each row of the Products table and several sales
transactions for each product in the Sales table. You can find the complete example in
CH09-05-RankingMultipleTables.xlsx workbook on the companion DVD.
The ProductRank measure in this case refers to the Product column of the Products table:
ProductRank = IF( COUNTROWS( VALUES( Sales[SalesAmount] ) ) > 0,
COUNTROWS( FILTER( ALL( Products[Product] ),
CALCULATE( SUM( Sales[SalesAmount] ) )
> SUM( Sales[SalesAmount] ) )
) + 1,
BLANK() )
There are two other differences in the ProductRank measure for data in a single table that you
saw in the previous section. First, there is an initial IF call that returns BLANK in case there are no
rows in the Sales table for a given product. Second, the predicate of the FILTER call calculates the
value of the current product by using the SUM( Sales[SalesAmount] ) statement: because data
reside in a separate table, you no longer need to use the CALCULATE statement to apply the
filter context.
In this scenario, the ModelRank measure is pretty similar to the ProductRank one, with the only
difference that the FILTER uses ALL( Products[Model] ) instead of ALL( Products[Product] ) to
iterate over all the models instead of over all the products:
ModelRank = IF( COUNTROWS( VALUES( Sales[SalesAmount] ) ) > 0,
COUNTROWS( FILTER( ALL( Products[Model] ),
CALCULATE( SUM( Sales[SalesAmount] ) )
> SUM( Sales[SalesAmount] ) )
) + 1,
BLANK() )
The results of these two measures are visually identical to those you saw in Figures 9-10, 9-11,
and 9-12. The only relevant information here is that you have to adapt your DAX formula
according to the underlying data model.
Moreover, even in this scenario you can define calculated columns to get a fixed ranking
instead of a dynamic one. To define ProductRankTotal in the Products table, you must
create a ProductSalesAmount calculated column first:
ProductSalesAmount = CALCULATE( SUM( Sales[SalesAmount] ) )
Search JabSto ::




Custom Search