Charticulator - Number Format "G" for Billion

Data Visualizations
1

Good morning,

I used Charticulator to create a simple KPI Sheet.
image
The result is exactly what I need but I do not like the way the number format is working.
I found that charticulator uses the d3 number format (https://github.com/d3/d3-format#format).

My question:
Do you know how I can change the number format so it shows “B” for Billion instead of “G”.
This is my current format:

${sum(`Measure 1 TY`)}{,.3s}

Thank you :slight_smile:

EDIT:
I found this online (https://github.com/d3/d3-format/issues/71) but I do not understand where it needs to be added:
siFormat(number).replace(‘G’, ‘B’).replace(‘k’, ‘K’);

2

@JarrettM @MudassirAli @dm-p

1 Like
3

The linked solution is JavaScript code, which does a string replace on the output, but unfortunately Charticulator’s expression language does not allow JavaScript to be executed (especially now it’s certified).

There’s a couple of ways around this:

  1. Create a measure that provides a formatted text value for your numeric measure (using FORMAT or similar), add that to the dataset, and then use this instead of the raw numeric value.

  2. (my preferred approach) It’s probably more correct to normalise your units to the same denominator for fair comparison between years, particularly with KPIs. I would personally choose millions and change the title to “Value Purchased (M)”.

    Because Charticulator expressions support simple arithmetic, you could then modify your expression as follows:

    ${sum(`Measure 1 TY`) / 1000000}{.0f}

    For billions (assuming 2 decimal places needed):

    ${sum(`Measure 1 TY`) / 1000000000}{.2f}

    Or, you could supply a measure using DIVIDE to similar effect and this would work too.

    If you want the unit after the number rather than in the title, you can add the “M” text after the expression as follows, e.g.:

    ${sum(`Measure 1 TY`) / 1000000}{.0f}M

    You can also prepend text beforehand in a similar way (but if you want to use a ‘$’ symbol you have to prefix with a backslash character to escape it, e.g.:

    \$${sum(`Measure 1 TY`) / 1000000}{.0f}M

Not sure if this helps, but it’s how I would approach it.

Regards,

Daniel

4 Likes
4

Hi Daniel,
Thank you very much for your detailed response!
I like the idea of having the same denominator in all numbers displayed.
The only problem I see when setting all numbers to Million is when looking at a smaller customer, the denominator does not change dynamically, eg. when the Measure is in below 100k it still shows Million but thousend (k) would be better.
Will see if DAX can help with that.

I really like the way you explain charticulator and deneb in videos :slight_smile: Thank you very much for your great work!!

5

Hello @Reporting, just following up if the responses above help you solve your inquiry?

We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.