I would create a new field that leverages your aggregated field as so:
Your Field (Pos. or Neg. Bullion)
Then drag this field onto shape and set all TRUE Values to our upward arrow and all FALSE Values to your Downward arrow.
Keep in mind this will assign a downward arrow for a value of zero. So to take care of this try a different formula like below
Your Field (Pos. Neg. or no change Bullion)
IF [Your Field] = 0 THEN 0
ELSEIF [Your Field] > 0 THEN 1
Make the above field into a dimension so it doesn't aggregate then simply assign a 0 shape to some type of no change arrow, a 1 to the upward arrow and a -1 to a downward arrow.
As stated earlier, I have only one value at a given point of time and also this is the only text in the entire worksheet. The worksheet doesn't have anything else. i.e. Let's suppose say I am taking Avg(My Field). Now this Avg can either be positive or negative and it is the only value which I am showing on my sheet. I don't have anything else on my sheet so my question is in this case how do I get it to change?
Deepa can you please attach a sample workbook? I think I can better explain by building it for you.
You will have to have two fields here, one will be your value and other will be shape. If you want it to show both of them along each other.
If you just have to show shapes then only one field on shape will suffice.
but in both the cases if you want a shape then you have to compare it to something where you can say its -ve or +ve.
for that you have to create formula as suggested by Davis.
Once you create that you will pick and choose Down/Up filled arrows, give Green color to Up arrow and Red to Down arrow (or as you desire).