6 Replies Latest reply on Jul 22, 2016 1:03 PM by Dan Armstrong

Substitute string for measure in axis label (for certain values)?

Hi,

I have a bunch of bar charts with measures on the X axis and dimensions on the Y axis.

I've been asked to label the X axis as follows:

If the value is less than 1%, the label should say "<1%".

If the value is 1% or more, the label should be the value.

Tried doing this with a calculated field, but I can't mix numbers and strings.

Is there a solution to this? Thanks.

Dan

• 1. Re: Substitute string for measure in axis label (for certain values)?

Dan -

You could create 2 calcs and place both on label. Tableau doesn't plot null values so it should work as desired:

Less than 1%: if [Profit Ratio] < 0.01 then '<1%' end

More than 1%: if [Profit Ratio] > 0.01 then [Profit Ratio] end

Additionally, you can change the number format of the 'more' calc to show decimals if desired:

• 2. Re: Substitute string for measure in axis label (for certain values)?

Dear Pooja,

Thanks for your reply. This is interesting and useful and I'm sure I will use it in the future.

However, it wasn't quite what I was hoping to do.

I need to show my '< 1%' label on the X axis, not as a label on the marks. So the figures on my  X axis would be something like: <1%, 2%, 3%, 4%, etc.

Would you happen to know if there's a way to do this? A small sample workbook is attached.

Thank you, Pooja.

Dan

• 3. Re: Substitute string for measure in axis label (for certain values)?

What I really need is conditional custom axis formatting.

I can create a custom format for my X axis labels that is '<12345600%' but it's going to apply to all the numbers, i.e. <1%, <2%, <3%, etc.

I am hoping to apply it to 1% only, but don't see any way to do this.

• 4. Re: Substitute string for measure in axis label (for certain values)?

I tried this: Custom numbers format

Basically it's setting up a parameter of '<1%' and a reference line, with the reference line invisible so that only the '<1%' shows up. And then adding a floating text box on a dashboard for the rest of the numbers. It only works if the axis is fixed, of course.

It's a kluge. A long way from ideal. Any other ideas? There doesn't seem to be any way to introduce a formula or conditional formatting on an axis. I hope I'm wrong.

Dan

• 5. Re: Substitute string for measure in axis label (for certain values)?

I'm afraid I don't know how to accomplish what you are asking specifically, however, something that could accomplish the same effect, and be more visually obvious, would be to place the "Less Than 1%" calculated field on the color shelf.  This would color the categories that are less than 1% in a different color than the others.

• 6. Re: Substitute string for measure in axis label (for certain values)?

The underlying user story is the following: My clients insist on carrying all percentages out to two decimal places. Their concern with fewer decimals is that very small numbers (less than 1%) will round down to 0% and be indistinguishable from zero.

They want dashboard users to be able to tell the difference between "a very small number" and "zero" and as a result they carry out every number to two decimal places, even though it's not really necessary for the vast majority of them.

If I could create axis labels based on a calculated field that shows values of 5%, 10%, 15%, 20%, etc. with zero decimal places and values under 1% with two decimal places, that might satisfy them.