5 Replies Latest reply on Sep 10, 2018 9:23 AM by Joe Oppelt

# Different number formatting for a calculated field used as measure

Hi All,

I have a scenario where  a calculated Field has to shows different number formatting according to dimension value,

In the below table :

IF Type=Sales then  [AMT]/[BP AMT]   (Number Formatting % with 2 dec points)

elseif type=Stock then [AMT]-[BP AMT]  (Number formatting standard with 2 dec points)

the calculated Field is done. I  have no idea how to achieve the number formatting .

If i try to convert into STR and append with % it becomes Discrete and I cant use in Measure values.

• ###### 1. Re: Different number formatting for a calculated field used as measure

Do you still need help with this?

(Note to self:  V10.5 here)

• ###### 2. Re: Different number formatting for a calculated field used as measure

yes it would be great if you can help me with this.

Thanks a lot for looking into it

• ###### 3. Re: Different number formatting for a calculated field used as measure

HI,

You can create two fields "sales"and "stock"and format it accordingly:

1. Sales : IF [Type]="Sales" then  [AMT]/[BP Value] END

2. Stock : IF ([Type])='Stock' then [AMT]-[BP Value] END

• ###### 4. Re: Different number formatting for a calculated field used as measure

Dear Meenu,

thanks, for looking into it ,but if you see in my sheet im trying to show .

During Business plan what is the Sales Amt & Stock Amt

Particular month Forecast Sales Amt & Stock Amt

Forecast Sales Amt / BP Sales Amt  similarly Forecast Stock amt -BP Stock Amt

the calculated field is ready if u download my workbook you can see it. only thing i need is different formatting

if Type=Sales then %

if type =Stock then standard format with 2 decimal

• ###### 5. Re: Different number formatting for a calculated field used as measure

See attached.

Because you want separate formatting, you'll have to make it a string calc.  See [Calc vs BP Display].

I added it to the text box, so you can see how it works.

But because it's a string calc, we can't add it to the measure values shelf.

On copy (1) I changed the format of the text box to look like you see.  That's one way to handle it.

On copy (2) I used a trick we have to format individual measures.  Just put bogus sums on the sheet, and then individually display each item.  This allows you to mix and match different formats (shapes for one column, text for another, bars for a third, etc.)

The one thing I could not get to work correctly was flipping the direction of the label for each axis.  (Yes, each has a separate axis.)

The real key here is that you're going to have to format the special calc as a string.