Aggregating two dimension values with same Rank

[mrthomasshelby]

Hello,

I have a table that ranks vendors based on a measure 'Variance to Estimate' as in the image below:

Everything works fine but say if two Vendors quote the same price and their variance to estimate is the same, the values get aggregated and the Vendor names also don't get populated. For example in the table above, for Package Labour Category 1, Vendor O and Vendor J quoted the same prices and hence their variance to estimate is the same. So, the values are getting aggregated and the Vendor names are also not populating. How to fix this issue? I have tried looking into other arguments in the Rank function like mode and fmt which I heard could help me out but so far I wasn't able to figure out how to use them here. I'm attaching the QV file for your reference. Thanks in advance!

stalwar1‌ kaushik.solanki‌ tresesco‌

[ogautier62 · Accepted solution]

Hi

to distinguish ties may be two  ideas :

use rank(......) & VendorID   to agregate

or use : num(rank(sum(sales),4)) there's no tie

regards

[mrthomasshelby]

Thanks Olivier for the response. I'm already aggregating on rank and Vendor ID. Your 2nd idea works fine but it's kind of misrepresenting the data I guess. Worst case, I can use this forumla I guess. Thanks again Olivier. Appreciate it.

[mrthomasshelby]

Hi Olivier. I have used your second idea along with the fmt argument value 1(Low Value on all rows). I guess this will work. Thanks a lot for the help again!