Hope you are doing great!
In below screenshot, I have Rank field, which needs to be corrected. Currently it is showing RowNo for each record. I want it to populate it based on below condition.
- It should start with 1 for each FGrp#, increment by one for each Sales value (Sales in descending order i.e. Highest sales in given region will listed first in the list)
- If there are same Sales values in given FGrp# then same Rank values needs to be assigned to both the records.
Please refer attached application, sample data and expected result sheet for your reference.
Looking forward to your affirmative response.
Aggr(Rank(Sum(Sales), 1), FGrp#, Key)
Where Key is created in the script like this:
AutoNumber(Ship&City&State&SysCust) as Key
(biff, embedded labels, table is Sheet1$);
Many Many thanks Sunny and Adam for becoming my saviors.
Solution provided by you worked well for single table model. However, It is killing me with dimensional model (I am implementing it with trial & error method). Attached is the sample application file.
Looking forward to hear from you.
Thank you Sunny. I will try it out.
However, while reviewing the earlier solution I found that Rank field is resetting the values for every FGrp# and if the Sales is same then it is retaining the same value; but while doing this it skips the intermediate value. In below given screenshot it is showing Rank values as 1, 4 & 6 where as it should show 1, 2 & 3.