Convert data in Crore, Lakhs in Quicksight KPIs

Question & Answer
, ,
1

Hi,
I am working for an Indian Client where they want to see data points in Indian numeric system i.e Crore, lakhs instead of Millions or Billions
Have done something similar in Power BI

Result =
SWITCH (
TRUE(),
sales >= 10000000, ROUND ( DIVIDE ( sales, 10000000 ), 2 ) & “Cr”,
sales >= 100000, ROUND ( DIVIDE ( sales, 100000 ), 2 ) & “L”,
sales >= 1000, ROUND ( DIVIDE ( sales, 1000 ), 2 ) & “K” ,sales)

But facing challenge in replicating the same in Quicksight since ifelse condition is numeric while its output is string hence below written KPI doesnt work.

ifelse(
Sales>=10000000, concat(toString(round(Sales/10000000.0,2)),’ Cr’)
,Sales>=100000, concat(toString(round(Sales/100000.0,2)),’ L’)
,Sales>=1000, concat(toString(round(Sales/1000.0,2)),’ K’)
,toString(0))

Any suggestion will be appreciated.
Thanks
Shweta

2

Hi @shweta,
When you want to use static Strings within a calculated function, they have to be quoted using ". If you change your logic to the following, the error should disappear:

ifelse(
Sales>=10000000, concat(toString(round(Sales/10000000.0,2))," Cr")
,Sales>=100000, concat(toString(round(Sales/100000.0,2))," L")
,Sales>=1000, concat(toString(round(Sales/1000.0,2))," K")
,toString(0))

I hope my solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking his answer as a “Solution”.

1 Like
3

Hi @Thomas
Thanks for taking out time to revert.

Please not Sales is an aggregated value ie. sum(amount)

  • As per your recommendation, we have tried using “” but its not working.
    Giving error as attached
    image

Thanks!

4

@Thomas looking for urgent support if possible. This is one of the mandatory point of our project.

5

Hi @shweta,
I assume that you tried to use the visual type KPI to display the result of your calculated field (please let me know if this assumption is inaccurate). The KPI visual works on top of numeric values. If dimensional fields are selected, the COUNT aggregate function is applied to create numeric values. Also in your case, the result of your calculated field is a string and no numeric value and, hence, the visual type KPI does no longer work to visualize the result of it. Instead you could simply use for example visual type Table and remove all header information and non-desired formatting from it to simply display the aggregated value and title within the visual.

I hope my solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking his answer as a “Solution”.