[URGENT]Lableing currency with M and K

[#1]

I have a requirement, In a pivot table based on Product and region am populating the sales value of a slaesperon, I have to abbrivate the sales value i.e. currency data, if the sales value more than 10 lacs it should be labled with M rather than showing the full figure, the same applicable for thosands as well, if the data is less than 1 Million it should be labled with K.. Thanks in advance.

[#2]

You may check the chart property, udner [Number] tab, there is few options for you to define Million Symbol, Thousand Symbol and Billion Symbol.

[jonathandienst]

Hi

The automatic label scaling only applies to charts I think. For a table you could do this:

=If(Value > 1E6, Round(Value/1E6, 1) & 'M',

If(Value > 1E3, Round(Value/1E3, 1) & 'K',

     Value))

HTH

Jonathan

[#4]

Hi,

Since you like to present the value in pivot table, chart property is not applicable here, you can use condition in your expression like below:

if(figure < 1000000, figure/1000&' K', figure/1000000&' M')

Awlad

[Sokkorn · Accepted solution]

Hi,

You may try this expression

If(Sum(Sale)<1000000,Num(Sum(Sale)/1000,'##0K'),Num(Sum(Sale)/1000000,'##0M'))

Regards,

Sokkorn

[stevedark]

Hi,

What is also important here is that you go to the Number tab of the Properties for the Pivot table and set the format to Expression Default or the settings on there will overwrite the settings from the Num function.

Steve

[Sokkorn]

Oop Mr. Steve, I'm totally missed this point. Thanks for your idea .

Regards,

Sokkorn

[stevedark]

Also, a quick tip, I would set up variables in your load script for Thousand and Million, ie.

let v1M = 1000000;

let v1K = 1000;

And then use those in your expression:

If(Sum(Sale)<v1M,Num(Sum(Sale)/v1K,'##0.0K'),Num(Sum(Sale)/v1M,'##0.0M'))

Note also, in my version of Sokkorn's expression I have set the values to 1 dp, otherwise you will not be able to distinguish between 1,000,001 and 1,499,999 - this is probably quite significant!

Steve

[#9]

Thanks Sokkorn,  its working fine. Thanks alot Steve for ur wonderfull tip, I will incoporate the same methodoloy. in my script.