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
Search JabSto ::

Custom Search