Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
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"?
Thanks!
Chris
If I understood correctly, just replace the set identifier $ (i.e. the current selection set) with 1 (the full record set):
=(sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])
/ sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1
-->
=Aggr(Rank(
(sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])
/ sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1
),Location)