I have a straight table which has a single row per location, and then obviously some metrics per each location. I am trying to add a Rank column which ranks each location against a certain metric. If I create an expression as below;
=Aggr(Rank(Sum({1}[TY Net Sales Dollars])),Location)
This works great, and ensures that even if I make a selection which limits the number of locations visible in the straight table, the rank is still the rank across all locations.
The problem is, I don't want to rank on just TY Net Sales, I want to rank on YTD Change TY vs LY, for which I have an expression working correctly;
=(sum({$<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])
/ sum({$<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1
It is my understanding that this {1} in the first expression is what causes it to always rank across all locations, so my question is how can I keep this in my rank expression, while using the calculation in my second expression instead just "TY Net Sales Dollars"?
