There are (as always with Tableau!) a few ways to go about this...and depending on your 'real world' version we may need a different method....however the following is one way
So the issue here is that we need vendor in the VizLoD in order for the RANK to perform (meaning we get a mark per State/Vendor combination), and also where sales for Apple are zero it still gets a RANK...so we need to only return a single RANK (for Apple), where Apple has sales
So the first thing I did was create your RANK field, but to only return a single value for Apple
[Rank_Dense - Apple]
IF SUM(ZN(IIF([Vendor]='Apple',[Total Dollars],NULL)))=0 THEN NULL
ELSEIF ATTR([Vendor]) = 'Apple' THEN RANK_DENSE(ZN(SUM([Total Dollars]))) END
we can then use this to do our colouring
IF attr([Vendor])<>'Apple' THEN 'White'
ELSEIF SUM(ZN(IIF([Vendor]='Apple',[Total Dollars],NULL)))=0 THEN 'Gray'
ELSEIF attr([Vendor])='Apple' AND [Rank_Dense - Apple] = 1 THEN 'Yellow'
ELSEIF attr([Vendor])='Apple' AND [Rank_Dense - Apple] = 2 THEN 'Green'
ELSEIF attr([Vendor])='Apple' AND [Rank_Dense - Apple] = 3 THEN 'Red'
I've also set the RANK for the other Vendors to white.
In all cases I've set the RANK to compute using = 'Vendor' (so we get a RANK for each Vendor per state)
Hopefully this makes sense, (I've added this to your Table sheet, which might help you see what's going on), but if not please post back
Colouring on RANK.twbx 61.4 KB
Thank You so much for your help,but i am still unable to achieve it. In the real data there are 43 Service Areas out of which Apple is present in only 13, in the attached pic consider first row as Samsung second as Apple and third as Motorola,so when Apple totaldollars are green then that particular Service Area should be green in color so in my case Texas should be green in color Arizona Yellow and California as Red.
when i am using ur formula its giving NUll Grey & White.
Thank you Simon,i got what i was looking for.