9 Replies Latest reply on Feb 23, 2018 4:46 AM by Tim Dines

# Dynamic Formatting

I have read on the forums that there is no way to have dynamic/custom formatting, but this is something that would really benefit my dashboard.

Is anyone able to help me with implementing something like:

if [Revenue] < 1000 then [Revenue]

elseif [Revenue] >= 1000 and [Revenue] < 1000000 then left([Revenue],2) + 'k'

elseif [Revenue] >= 1000000 and [Revenue] < 1000000000 then left([Revenue],2) + 'm'

elseif [Revenue] > 1000000000 then left([Revenue]) + 'b'

else [Revenue]

end

Which would give me the below example results:

 Product Price Qty Sold Revenue What I want Pencil 10 120 1,200 1k Scissors 60 5 300 300 Paper 30 1600 48,000 48k Pen 40 250600 10,024,000 10m Paper clip 3 5 15 15 Printer 6000 170000 1,020,000,000 1b

Please also find attached a 10.5 workbook to help illustrate my example.

• ###### 1. Re: Dynamic Formatting

Hi Gareth,

I think what you have suggested can be implemented using a Calculated Field. All you have to do in the THEN clause is to convert this value to a String value so that you can append 'K' or 'B' etc. You can use the STR() function to achieve it.

Also there is another way to show revenues but you will get to choose 1 option from the list. Please refer below screenshot:

-Abhilash

1 of 2 people found this helpful
• ###### 2. Re: Dynamic Formatting

Hi Gareth,

I took a look at your workbook and figure it out like this! If this is the solution you are looking for, here is how you can do it;

Firstly, to format your numbers as K(thousand), M(Million) , B(Billion) you can use custom formating! But before you do that, some calculated fields must be done. Here is the example for K and i think you can figure it out how to do other ones by following K example.

After you create calculated fields for K,M and B ( can go on..), you can drag them to Text pane and format it !

Here is the final screenshot! And you can find the solution workbook below. Have a good day!

Best Regards,

Yasin.

1 of 2 people found this helpful
• ###### 3. Re: Dynamic Formatting

Hi, I need it all performed in one calculated field not multiple different fields with the usual formatting applied to each. Any idea how to do this?

• ###### 4. Re: Dynamic Formatting

Hi, Yes, I had tried converting to STR but I get an error (see attached).

I need it all performed in one calculated field not multiple different fields with the usual formatting applied to each.

• ###### 5. Re: Dynamic Formatting

Hi Gareth,

All you got to do additionally is just format the field using LEFT() function.

-Abhilash

1 of 1 people found this helpful
• ###### 6. Re: Dynamic Formatting

Check this one! You will create K,B,M string field but find the answer with one IF computation.

I named it concat, and try to follow instructions as you said.

1 of 1 people found this helpful
• ###### 7. Re: Dynamic Formatting

You can delete [K value],[M value] and [B value] and rather just put 'K' , 'M', 'B' at the end of sentences.  To sum, just one calculated field is all you need!

1 of 1 people found this helpful
• ###### 8. Re: Dynamic Formatting

Hi Gareth,

I strongly urge you to closely refer the screenshot that I posted earlier.

Things incorrect in your calculated field:

1. All THEN clauses need to have STR() function. You have not used it in the 1st condition and the ELSE condition

2. Also in the 2nd last condition you have not spelled it correctly i.e. SRT instead of STR()

Refer to the calculated field definition below and replace Sales by Revenue:

IF  SUM([Sales]) < 1000 then STR(SUM([Sales]))

ELSEIF SUM([Sales]) >= 1000 and SUM([Sales]) < 1000000 then (STR(SUM([Sales]))) + 'K'

ELSEIF SUM([Sales]) >= 1000000 and SUM([Sales]) < 1000000000 then (STR(SUM([Sales]))) + 'M'

ELSEIF SUM([Sales]) > 1000000000 then (STR(SUM([Sales]))) + 'B'

else STR(SUM([Sales]))

end

-Abhilash

2 of 2 people found this helpful
• ###### 9. Re: Dynamic Formatting

That will work if you convert the first line [REVENUE] to a string and the one in the else line to a string as well.

1 of 1 people found this helpful