Change number value

[sunil-kumar5]

Hi All,

I have a bar chart showing value like 1.65M,12.34M,342.45K,150.90K however the requirement is to show the value as

1M,12M,342K,150K

All the number after point should be removed and K and M should come with exact value.

Please advise

Thanks

[chaorenzhu]

if([your_measure]/1000000>=1,floor([your_measure]/1000000) & 'M',floor([your_measure]/1000)&'K')

[anat]

=If(sum(Amount)/1000000000 > 1,Num(sum(Amount)/1000000000,'$#,###B'), Num(sum(Amount)/1000000,'$#,###M'))

[sunil-kumar5]

Thanks for your response,

Actually my expression contains multiple IF condition and does not fit in the above solution, Is there anything we can change into format pattern of number to achieve this?

[chaorenzhu]

Can you share an example of your expression to help us better understand why it does not fit

[sunil-kumar5]

Hi,

here is a below measure to meet the requirement, even I have tried it with variable but no success and showed same result.

=SUM(if([Quote Active Status] <> ('InActive'),if(Status<>'Rejected',
[Quote- Potential, INR/T (excluding transportation cost)]*[Quote-Volume (in Tons)])))/
sum(if([Quote Active Status] <> ('InActive'),if(Status<>'Rejected',
[Quote-Volume (in Tons)])))

Thanks

[chaorenzhu]

you can use set expression:

SUM({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote- Potential, INR/T (excluding transportation cost)]*[Quote-Volume (in Tons)])/
sum({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote-Volume (in Tons)])

[sunil-kumar5]

Hi, 

May be I was not able to make this clear to all of you as I need the exact number with M and K.

like if the value returns 12.6k that should reflect 12k only also if value is 13.77M or 13.23k the value should reflect  as 13M and 13K only.

Please advise

[chaorenzhu]

use floor(), see the expression in my first comment

[sunil-kumar5]

I tried that but it does not give any value in bar chart.

if(SUM({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote- Potential, INR/T (excluding transportation cost)]*[Quote-Volume (in Tons)])/
sum({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote-Volume (in Tons)])/1000000>=1,floor(SUM({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote- Potential, INR/T (excluding transportation cost)]*[Quote-Volume (in Tons)])/
sum({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote-Volume (in Tons)])/1000000) & 'M',floor(SUM({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote- Potential, INR/T (excluding transportation cost)]*[Quote-Volume (in Tons)])/
sum({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote-Volume (in Tons)])/1000)&'K')

Even Create a variable for this for the expression named GAV but getting the same result.

if($(GAV)>=1000000,Floor($(GAV)/1000000) & 'M',Floor($(GAV)/1000)&'K')