10 Replies Latest reply on May 7, 2018 10:13 AM by David Luhut

# Calculated Measure to dynamically 'change' number format between Number and Percentage

Hello,

I created a calculated measure named 'MTD Actual'.

Most of the figures are in numbers. However, there's 2 that's in percentages (Economic Vacancy  or  NOI Margin %).

How can I have the value show to either number or percentages?

I've attached 2 images where initially I have it format to 'Number', and thus any figures that are supposed to be percentages are shown as either 1 (anything that's greater than or equal to 50%) or 0 (anything that's less than 50%).

The default 'Automatic' format makes it worst.

Is there a solution to this?

• ###### 2. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

I saw this blog before. But I believe this is not applicable to me.

Reason being, the blog used 3 different measures, [Quantity], [Discount] and [Sales].

Whereas, my number comes from 1 measure [Local Actual].

From this [Local Actual], with conditions, I'm able to SUM based on conditions.

Conditions can be, inverse the negative value to be positive when it's Revenue etc, checking the dates so as to get the MTD and YTD figures etc.

• ###### 3. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

If you can isolate the measure values in to seperate calculations you should be able to use Ryan's blog.

Set up 3 calcs like this...

Measure1

SUM(If [Dimension] = 'Value'

Then [Measure]

End)

• ###### 4. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

I've re-read it many times and even try to replicate similar to what he has, but nothing came close to it,

Also, the blog uses a parameter where he lets user to select what user wants to see.

In my case, however, the 'categories' are static...it's always show as 'RevPAS', 'Revenue' etc.

Now I just want it to show ##.#% for 'RevPAS' and #,###,### for other categories.

Below is what I've done but to no avail

• ###### 5. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

Try this slight variation on Ryan's solution.

Create a calculation for each dimension value (a pain I know, but bare with me)

Then instead of using Measure Names, stack the individual measures in the Text of the Marks Card. Then click the marks card and put them right next to each other with no space.

You can then format the numbers individually and they will only appear on that dimensional value's row.

• ###### 6. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

VERY CLOSE!!!!!

Was able to do what you told me to, (Although I didn't do for all dimension). It display exactly what I want.

BUT, I realized that this can only have apply to 1 <Measure Name>, which in this case is the [MTD Actual]

Since I have other Measures besides MTD Actual, such as MTD Budget, MTD Variance (\$) and MTD Variance (%) etc. The number will repeat itself due to the 'Edit Label'.  Below is an attached screen shot where I've highlighted in green to show the 'repeated' value.

How do I tackle that?

• ###### 7. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

You need to create a placeholder calculation.

Drop that on your columns as many times as you need and you will have multiple options show up in the Marks Card. Then add each set of measures to the appropriate Placeholder, you can then format them as needed.

• ###### 8. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

EDIT

I was able to get what you meant and put it to the test.

Wondering if I can bring the bottom up to the top so that for each 'Entity', it'll list out all of the measures...which in this case is the [MTD Actual Placeholder] and [MTD Budget Placeholder].

And if it's possible, wonder if I can change the header name to just show as 'MTD Actual', while the Calculated Measure is still the same with 'Placeholder' at the end?

• ###### 9. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

The best thing to do will be to use text boxes to label the columns (you can't move them sadly).

So once you have it on the dashboard you can use a combination blanks and text boxes to line things up correctly.

• ###### 10. Re: Calculated Measure to dynamically 'change' number format between Number and Percentage

Thanks Mark!

I've learn something new again about Tableau.

Now I guess it's a decision of which way I'd like to represent my data.