# Summary Number with 2 metrics needing commas and rounding

Member

Hi,

I am trying to make a Summary Number for a card with a calculated field that will look like this:

1,121 Loans | \$211,100,123

or even better, if possible:

1,121 Loans | \$211.1M

The first number could be anywhere from 5 to 500,000. The second currency number could be anywhere from \$10,000 to \$1 Billion dollars.

My current formula is:

Concat(COUNT(`LoanAmount`), ' Loans | \$',
Round(SUM(`LoanAmount`),0))

The result looks like this:

1121 Loans | \$211100123

How can I get the desired format in there?

• Coach

Hi @AJ2020

Since Domo doesn't support formatting strings yet you'll need to do it manually. You can utilize a beastmode with CONCAT and a bunch of substring manipulation like this to solve it (this is up to 12 digits / Billions but can be modified to go higher)

(Untested but should give you the general framework)

`CONCAT(CASE WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 12 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 7, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 10, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 11 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 6, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 9, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 10 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 5, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 8, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 9 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 7, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 8 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 6, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 7 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 5, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 6 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 5 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 4 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) <= 3 THEN      ROUND(SUM(`Amount`), 0)WHEN SUM(`Amount`) IS NULL THEN 0ELSE ''END, 'Loans | \$', CASE WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 12 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 10, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 11 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 6, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 9, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 10 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 5, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 9 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 8 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 6, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 7 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 5, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 6 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 5 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 4 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) <= 3 THEN      ROUND(SUM(`Revenue`), 0)WHEN SUM(`Revenue`) IS NULL THEN 0ELSE 'ERROR'END)`
**Did this solve your problem? Accept it as a solution!**

• Coach

Hi @AJ2020

Since Domo doesn't support formatting strings yet you'll need to do it manually. You can utilize a beastmode with CONCAT and a bunch of substring manipulation like this to solve it (this is up to 12 digits / Billions but can be modified to go higher)

(Untested but should give you the general framework)

`CONCAT(CASE WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 12 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 7, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 10, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 11 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 6, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 9, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 10 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 5, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 8, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 9 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 7, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 8 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 6, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 7 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 5, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 6 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 5 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 4 THEN	CONCAT(      SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3)    )WHEN LENGTH(ROUND(SUM(`Amount`), 0)) <= 3 THEN      ROUND(SUM(`Amount`), 0)WHEN SUM(`Amount`) IS NULL THEN 0ELSE ''END, 'Loans | \$', CASE WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 12 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 10, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 11 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 6, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 9, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 10 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 5, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 9 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 8 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 6, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 7 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 5, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 6 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 5 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 4 THEN        CONCAT(      SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',      SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3)    )WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) <= 3 THEN      ROUND(SUM(`Revenue`), 0)WHEN SUM(`Revenue`) IS NULL THEN 0ELSE 'ERROR'END)`