How to use rank in complicated crosstab based on an specific measure

Check list of all posts

Sometimes, it is requested to show rank based on a specific measure in complicated crosstab, such as YTD for a specific product. This document illustrates a generic idea to handle it. Please see screenshot below,

Region will be ranked with number based on current year sales via web order method. The idea demonstrated here can be extended to rank based on multiple measures. Please download report specification (version 10.2) for review if needed

Create a crosstab below with nested node and measures


Create query item Revenue and rank below



RevenueOfWebLastyear:

total(   if ([Order method type]='Web' and [Year]=maximum([Year] for report))

then ([Sales (query)].[Sales].[Revenue])

else(0)

for [Region])

Please note that Aggregate Function is Average

rank
rank ([RevenueOfWebLastyear])

This definition has achieved the result as below, which makes RevenueOfWebLastyear, or rank criteria as a property of region. Because the number is same, we should not experience any discontinued rank, or running count.