8 Replies Latest reply on Sep 19, 2016 7:19 AM by Mark Fagan

# Rounding down if <=68.49% to 68% and up if >=68.50% to 69%

I have a calculated field to validate if a store percentage met or exceeded the district managers percentage then I applying green color if it did and blue if it did not.

Store             =     68.80%

D Manger     =      69.47%

So both percentages should be calculating at 69%

How can I get tableau to account for the rounding this to meet this requirement?

I have looked through the forums and found floor/ceiling but cannot seem to find what I need to produce the color .

Thanks in advance for any direction!

-Mark

• ###### 1. Re: Rounding down if <=68.49% to 68% and up if >=68.50% to 69%

Hey Mark,

Maybe I'm overlooking something, but wouldn't the standard ROUND() function do what you need?

(Edit: Or do you need something like this? IF AVG([Sales]) >= 68.5 THEN 'green' ELSE 'blue' END  )

• ###### 2. Re: Rounding down if <=68.49% to 68% and up if >=68.50% to 69%

Maybe:

IF [District Manager Percentage] - [Store Percentage] > 0 THEN 'Blue' ELSE 'Green' END

Or am I missing something too.

--Shawn

• ###### 3. Re: Rounding down if <=68.49% to 68% and up if >=68.50% to 69%

So to sum up,

Make rounding calcs like this:

ROUND([whatever field you want to round], 0)  // round to zero decimal places.

Then use that calc in place of your original field.

IF [Rounded Manager Value] >= [Rounded Store Value] THEN "Met" else "Did not Meet" END

Put the second calc on the color shelf and assign blue and green (or any other color) accordingly.

• ###### 4. Re: Rounding down if <=68.49% to 68% and up if >=68.50% to 69%

Hi Derrick,

Let me clarify a bit more.

1. When I applied ROUND, it was rounding all data to 100%

2. Store % calculated field gets me the 68.80% for the Store:

if [Order Count]<30 then 0 else [Store Percentage] END

3. Then I have a calculated field that identifies if the percentage was >= [District Manager Percentage] that I place on the Color mark:

IF [Store Percentage] >= [District Manager Percentage]

THEN "Met Goal"

ELSE "Below Goal"

END

The issue seems to be Tableau is not rounding the 68.80% up to 69% to have met the goal of the District Manager (69.47%) 69% thus the 68.80% remains blue as having not met the District Manager goal.  The Store Percentage and the District Manager need to be recognized as 69%.

Thanks,

-Mark

• ###### 5. Re: Rounding down if <=68.49% to 68% and up if >=68.50% to 69%

If it is rounding up to 100%, try ROUND([myField], 2). (2 gives it more 2 decimal places - since the percent is likely represented as .6869...)

• ###### 6. Re: Rounding down if <=68.49% to 68% and up if >=68.50% to 69%

I hate when that happens!