0

I am working on a SSRS report which requires a chart to show vertical axis in 'M' or 'K' like 0, 2M, 4M. I got a custom formatting formula which adds an 'M' or 'K' with numbers - 

=Switch(Fields!TotalSpendCurrent.Value < 1000, "0.#", Fields!TotalSpendCurrent.Value < 1000000, "#,.#K", true, "#,,M")

But with this I am getting 'M' or 'K' as minimum value not 0.

enter image description here

Current Output Screen

enter image description here

Pedram
  • 6,256
  • 10
  • 65
  • 87
AmiV
  • 1
  • 2
  • Can you show us some data! Or you can use Format function to apply on condition base! – Pedram Oct 14 '16 at 06:51
  • You can assume any data which is more than 1000000 and I need to represent it on chart's vertical axis starting as 0, 2M, 4M. For example - you have Total Spend with maximum value of 16000000 and Month for X axis from June to Dec. – AmiV Oct 14 '16 at 06:55
  • So if `Fields!TotalSpendCurrent.Value` is less than 1000 then you want to show `0` right? not `M` – Pedram Oct 14 '16 at 06:57
  • Yes, that is what I am trying to get. – AmiV Oct 14 '16 at 06:57
  • Seems you are very new to SSRS, can you please provide current actual output screen! – Pedram Oct 14 '16 at 07:10
  • Added screen shot of the chart, hope this will help. – AmiV Oct 14 '16 at 07:20
  • please check my below answer and update me! – Pedram Oct 14 '16 at 07:20

1 Answers1

-1

Try this,

=Switch(CInt(Fields!TotalSpendCurrent.Value) < 1000, Format(Fields!TotalSpendCurrent.Value,"0.#"), 
CInt(Fields!TotalSpendCurrent.Value) < 1000000, Format(Fields!TotalSpendCurrent.Value,"#,.#K"), 
true, Format(Fields!TotalSpendCurrent.Value,"#,,M"))

Let me if it works or not!

Pedram
  • 6,256
  • 10
  • 65
  • 87
  • No, now it showing only one number with M or K in all scales - 1M, 1M, 1M – AmiV Oct 14 '16 at 07:31
  • see it will show you the whatever the values we have set to `Fields!TotalSpendCurrent.Value` - so please check all the values or if possible please show here – Pedram Oct 14 '16 at 07:39
  • Values are correct as they are starting from 0 to Mills but, this isn't working. – AmiV Oct 14 '16 at 09:12