I have a unique situation with a pivot table where I would like to compute the rank not amongst the values displayed but among the family of underlying values that roll up to the value in the dimension. Is this possible with good performance?
User will select an Item that is probably sold in multiple sales offices. We want to see what is the Rank of that product's sales for each SalesOffice and then underyling branches of the Office. So it may be that in office A the item had 25 in sales and hence rank 3 but in office B it had 145 in sales making it rank 1 for B but 80 in B1 giving rank 2 etc.
Looking for an efficient AGGR() with rank that doesn't take too much time.