Formatting Multiple Summary Numbers with Commas
Problem
To build summary numbers with multiple metrics, one needs to use the CONCAT operator, which converts the entire output to a string. This means that numbers won’t contain comma’s. In this case, a solution is needed to dynamically add comma’s to these numbers based on their length.
For example, in the Summary Number below the first number has no commas. The second number being a percentage is easier to handle because it is rounded to one decimal precision and never needs a comma because it never exceeds the hundreds.
Desired output:
Solution
 Find the Length of the Number
 Break it into appropriate comma blocks
 Concatenate commas between the blocks.
For example, a fourdigit number is in the thousands and would have two sections: the first number followed by a comma and then the last three numbers: [1],[234]
This can be handling using a combination of LENGTH() to get the overall length of the number, ROUND() to remove decimal precision and SUBSTRING() to pick out the position of the characters you want to place in each comma block. In addition, it will need to be handled using nested CASE logic to identify each case and handle it appropriately. Lastly, it will be tied together with another CONCAT().
Code
Here is a sample of code that demonstrates how to format one metric up to hundreds of millions  9 digits in length. Since you will have multiple summary numbers, you would need to do this for each numeric value in the summary number.
CASE
WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 9  9 digits = hundreds of millions
THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Measure Column`)),1,3),  first comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)), 3,3),  second comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)),6,3)  third comma block
)
WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 8  8 digits = tens of millions
THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Measure Column`)),1,3),  first comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)), 2,3),  second comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)),6,3)  third comma block
)
WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 7  7 digits = millions
THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Measure Column`)),1,1),  first comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)), 2,3),  second comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)),5,3)  third comma block
)
WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 6  6 digits = hundreds of thousands
THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Measure Column`)),1,3),  first comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)), 4,3)  second comma block
)
WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 5  5 digits = tens of thousands
THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Measure Column`)),1,2),  first comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)), 3,3)  second comma block
)
WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 4  4 digits = thousands
THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Measure Column`)),1,1),  first comma block
',',
SUBSTRING(ROUND(SUM(`Measure Column`)), 2,3)  second comma block
)
WHEN LENGTH(ROUND(SUM(`Measure Column`))) < 4  < 4 digits = hundreds or less
THEN ROUND(SUM(`Measure Column`))
END
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Best Answer

I ceated this beastmode solution for automatically adding commas to custom HTML summary numbers (I had never looked at the dojo before now and didn't realize a solution already existed) but found it to be easy to use and doesn't slow down the card loading (at least with the 1.5M row dataset I'm using it on), so I thought I'd share it here.
 Replace the `x` in this formula with whatever data you are trying to summarize.
 You can remove sections you don't need, like if you don't need trillions just delete everything between the trillions comment and the billions comment. It shouldn't be necessary, because this really doesn't add much processing time to the beastmode, but it's possible if you think it will help.
 You can also add additional sections, like if you need quadrillions, by just copying the trillions segment and adding 3 digits to each number (if it's 99.99 add 3 more nines in front of the decimal, if it's 1000 add 3 more zeros, etc).
CONCAT(
/*Trillions*/
CONCAT(
(CASE WHEN `x` > 99999999999999.99 AND MOD(`x`,1000000000000000) < 100000000000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9999999999999.99 AND MOD(`x`,1000000000000000) < 10000000000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 999999999999.99 THEN CONCAT(FLOOR(MOD(`x`,1000000000000000)/1000000000000),',') ELSE '' END)
)
/*Billions*/
,CONCAT(
(CASE WHEN `x` > 99999999999.99 AND MOD(`x`,1000000000000) < 100000000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9999999999.99 AND MOD(`x`,1000000000000) < 10000000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 999999999.99 THEN CONCAT(FLOOR(MOD(`x`,1000000000000)/1000000000),',') ELSE '' END)
)
/*Millions*/
,CONCAT(
(CASE WHEN `x` > 99999999.99 AND MOD(`x`,1000000000) < 100000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9999999.99 AND MOD(`x`,1000000000) < 10000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 999999.99 THEN CONCAT(FLOOR(MOD(`x`,1000000000)/1000000),',') ELSE '' END)
)
/*Thousands*/
,CONCAT(
(CASE WHEN `x` > 99999.99 AND MOD(`x`,1000000) < 100000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9999.99 AND MOD(`x`,1000000) < 10000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 999.99 THEN CONCAT(FLOOR(MOD(`x`,1000000)/1000),',') ELSE '' END)
)
/*Hundreds*/
,CONCAT(
(CASE WHEN `x` > 99.99 AND MOD(`x`,1000) < 100 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9.99 AND MOD(`x`,1000) < 10 THEN '0' ELSE '' END)
)
,FLOOR(MOD(`x`,1000))
)2
Answers

Great article thanks for sharing!
0 
You can use the same structure to abbreviate the Title instead of putting commas. When the number is in millions, the subtitle becomes way too long.
So instead of 1,230 it will say 1.2k or 1k depending on your case statement.0 
I ceated this beastmode solution for automatically adding commas to custom HTML summary numbers (I had never looked at the dojo before now and didn't realize a solution already existed) but found it to be easy to use and doesn't slow down the card loading (at least with the 1.5M row dataset I'm using it on), so I thought I'd share it here.
 Replace the `x` in this formula with whatever data you are trying to summarize.
 You can remove sections you don't need, like if you don't need trillions just delete everything between the trillions comment and the billions comment. It shouldn't be necessary, because this really doesn't add much processing time to the beastmode, but it's possible if you think it will help.
 You can also add additional sections, like if you need quadrillions, by just copying the trillions segment and adding 3 digits to each number (if it's 99.99 add 3 more nines in front of the decimal, if it's 1000 add 3 more zeros, etc).
CONCAT(
/*Trillions*/
CONCAT(
(CASE WHEN `x` > 99999999999999.99 AND MOD(`x`,1000000000000000) < 100000000000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9999999999999.99 AND MOD(`x`,1000000000000000) < 10000000000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 999999999999.99 THEN CONCAT(FLOOR(MOD(`x`,1000000000000000)/1000000000000),',') ELSE '' END)
)
/*Billions*/
,CONCAT(
(CASE WHEN `x` > 99999999999.99 AND MOD(`x`,1000000000000) < 100000000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9999999999.99 AND MOD(`x`,1000000000000) < 10000000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 999999999.99 THEN CONCAT(FLOOR(MOD(`x`,1000000000000)/1000000000),',') ELSE '' END)
)
/*Millions*/
,CONCAT(
(CASE WHEN `x` > 99999999.99 AND MOD(`x`,1000000000) < 100000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9999999.99 AND MOD(`x`,1000000000) < 10000000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 999999.99 THEN CONCAT(FLOOR(MOD(`x`,1000000000)/1000000),',') ELSE '' END)
)
/*Thousands*/
,CONCAT(
(CASE WHEN `x` > 99999.99 AND MOD(`x`,1000000) < 100000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9999.99 AND MOD(`x`,1000000) < 10000 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 999.99 THEN CONCAT(FLOOR(MOD(`x`,1000000)/1000),',') ELSE '' END)
)
/*Hundreds*/
,CONCAT(
(CASE WHEN `x` > 99.99 AND MOD(`x`,1000) < 100 THEN '0' ELSE '' END)
,(CASE WHEN `x` > 9.99 AND MOD(`x`,1000) < 10 THEN '0' ELSE '' END)
)
,FLOOR(MOD(`x`,1000))
)2 
Hello @avataraang ,
I love the work you've done with thee referenced Beast Mode. I'm happy to let you know we have just released Code Block apps in our Domo Appstore. These allow customers and Domo experts such as yourself, to publish code blocks like the ones you've created for distribution in the Appstore. The publish process for the code blocks is currently in an early beta. If you are interested in publishing your code blocks, let me know and we can enable it for you.Cheers,
Cody SmithDirector of Product, Domo
0 
Just found this solution, which worked very well. Thank you!
One thing to note if anyone comes across this to use it: There is a typo in DataJake's original solution that had me confused for a while.
For 8 digit strings, we are getting an extra digit due to this typo:
SUBSTRING(ROUND(SUM(`Measure Column`)),1,3),  first comma block
Which should instead be:
SUBSTRING(ROUND(SUM(`Measure Column`)),1,2),  first comma block
Hope this helps anyone who comes across this solution.
0
Categories
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 273 Workbench
 2 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 527 Datasets
 2.1K Magic ETL
 3K Visualize
 2.2K Charting
 443 Beast Mode
 23 Variables
 514 Automate
 115 Apps
 391 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 332 Distribute
 77 Domo Everywhere
 255 Scheduled Reports
 67 Manage
 67 Governance & Security
 1 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 68 Community Announcements
 4.8K Archive