Hi All, I posted this on a different forum but I believe this is the appropriate place for this question. I have 2 tables that are top and bottom 5 revenues for a specific region. I have 4 regions and they are tied to a button through the usage of a variable. I've got the top 5 working fine for all 4 regions. But when I modify my syntax to get the bottom 5 row values only one of the regions do not yield any data while the other 3 are working fine (literally just putting a '-' negative sign in front of my syntax to get the bottom 5 rows. I am lost at this point and really would like some feedback.
BOTTOM 5 syntax -
Aggr(
if($(vBot5) = 1,
If(Rank(TOTAL -Sum({<Region = {'USA'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])) < 6, Sum({<Region = {'USA'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])),
if($(vBot5) = 2,
If(Rank(TOTAL -Sum({<Region = {'China'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])) < 6, Sum({<Region = {'China'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])),
if($(vBot5) = 3,
If(Rank(TOTAL -Sum({<Region = {'Brazil'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])) < 6, Sum({<Region = {'Brazil'}, [Status] = {'Ready', 'Partial Ready'}>}[Revenue])),
if($(vBot5) = 4,
If(Rank(TOTAL -Sum({<Region = {'Canada'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])) < 6, Sum({<Region = {'Canada'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue]))
)
)
)
)
, [Account], [Account_Name])
As mentioned, for the regions China, Brazil, Canada my code returns 5 rows of negative numbers for revenue which is absolutely correct. But for some reason, USA (my first button) does not yield any results when in fact it should.
