Have you ever wanted to display your currency values in terms of $K? If so, you’ve probably realized that this is not possible to accomplish using our custom d3 formatting options (the best we can do is return the SI units that match the actual size of the number, e.g., if a number is in the millions, it will return that number with an M next to it, rather than dividing the number by 1000 and attaching a K)…
but, good news, it is possible to display consistent $K units if you use our regex functions!
Ex. Say we want to display the [Sum of Revenue] column (in some grouping level) in $K units…
- Create a new column in the same grouping level with the following formula:
Concat("$", RegexpReplace(RegexpReplace(Text(Round([Sum of Revenue] / 1000, 0)), "^([0-9]{1,3}|)([0-9]{3}|)([0-9]{3}|)([0-9]{3})$", "\\1,\\2,\\3,\\4"), "(^|,)(,+)", "\\1"), "K")- Note: if your column of interest is in the base columns, apply this in a new base column.
- [Optional] - Hide the original column (in this example, I was interested in displaying [Sum of Revenue] in terms of $K, so I’ll hide that column). It will come in handy to keep the original column for later use if you want to visualize your data or sort your table by [Sum of Revenue].
If you wanted to display your data in terms of $M, you would just have to change the formula from step 1 to divide [Sum of Revenue] by 1000000 (instead of 1000) and attach units of “M” (instead of “K”).