Number format: Million and kilos integrating together in one expression

[rdsuperlike]

I want to integrate the below two expressions into one expression.

What I want is if < 1M it should give in k, but if more than 1M it should give in M.

Current expression are as below:

Below expression results into 0.0M if result is less than a million

=text(Num(SUM(QTY)/1000000, '#.#M'))

Below expression results into 3087.3k even when it is more than million

=text(Num(SUM(QTY)/1000, '#.#k'))

Ny Help is appreciated.

Thanks

[lbunnell]

This worked in a straight table for me, but not in a bar chart which views the 1.24 million as smaller than 43.71 thousand. Anyone know how to make this work for Bar Charts?