Skip to content

Instantly share code, notes, and snippets.

@Hugoberry
Last active March 3, 2023 18:00
Show Gist options
  • Save Hugoberry/1aaee5a08195deb87b23bd1151f3ef00 to your computer and use it in GitHub Desktop.
Save Hugoberry/1aaee5a08195deb87b23bd1151f3ef00 to your computer and use it in GitHub Desktop.
A DAX measure definition that formats the Revenue numbers in a wordly way. It spells out numbers 1,812,343,434,567,812 like to `one quadrillion eight hundred and twelve trillion three hundred and forty-three billion four hundred and thirty-five million five hundred and sixty-eight thousand eight hundred and twelve`
Better Revenue_ =
VAR scale =
LEN ( [Revenue] )
RETURN
CONCATENATEX (
ADDCOLUMNS (
ADDCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 0, scale / 3 ),
"Factors",
ROUND (
MOD ( [Revenue], POWER ( 1000, [Value] + 1 ) )
/ POWER ( 1000, [Value] ),
0
)
),
"X",
SWITCH (
TRUE (),
[Factors] = BLANK (), "",
LEN ( [Factors] ) = 1, LOOKUPVALUE ( DigitWords[Word], DigitWords[Digit], [Factors] ),
LEN ( [Factors] ) = 2,
IF (
[Factors] < 20,
LOOKUPVALUE ( NumberWords[Word], NumberWords[Number], [Factors] ),
CONCATENATE (
LOOKUPVALUE (
NumberWords[Word],
NumberWords[Number], [Factors] - MOD ( [Factors], 10 )
),
IF (
MOD ( [Factors], 10 ) = 0,
"",
CONCATENATE (
"-",
LOOKUPVALUE ( DigitWords[Word], DigitWords[Digit], MOD ( [Factors], 10 ) )
)
)
)
),
LEN ( [Factors] ) = 3,
CONCATENATE (
CONCATENATE (
LOOKUPVALUE ( DigitWords[Word], DigitWords[Digit], QUOTIENT ( [Factors], 100 ) ),
" hundred "
),
IF (
MOD ( [Factors], 100 ) <> 0,
CONCATENATE (
"and ",
IF (
MOD ( [Factors], 100 ) < 20,
LOOKUPVALUE ( NumberWords[Word], NumberWords[Number], MOD ( [Factors], 100 ) ),
CONCATENATE (
LOOKUPVALUE (
NumberWords[Word],
NumberWords[Number], MOD ( [Factors], 100 ) - MOD ( [Factors], 10 )
),
IF (
MOD ( [Factors], 10 ) = 0,
"",
CONCATENATE (
"-",
LOOKUPVALUE ( DigitWords[Word], DigitWords[Digit], MOD ( [Factors], 10 ) )
)
)
)
)
)
)
)
)
),
"Y",
CONCATENATE (
[X],
" " & LOOKUPVALUE ( Magnitude[Word], Magnitude[Index], [Value] )
)
),
[Y],
" ",
[Value], 0
)
DigitWords =
DATATABLE(
"Digit", INTEGER,
"Word", STRING,
{
{0, "zero"},
{1, "one"},
{2, "two"},
{3, "three"},
{4, "four"},
{5, "five"},
{6, "six"},
{7, "seven"},
{8, "eight"},
{9, "nine"}
}
)
Magnitude =
DATATABLE (
"Index", INTEGER,
"Word", STRING,
{
{ 1, "thousand" },
{ 2, "million" },
{ 3, "billion" },
{ 4, "trillion" },
{ 5, "quadrillion" },
{ 6, "quintillion" },
{ 7, "sextillion" },
{ 8, "septillion" },
{ 9, "octillion" },
{ 10, "nonillion" },
{ 11, "decillion" },
{ 12, "undecillion" },
{ 13, "duodecillion" },
{ 14, "tredecillion" },
{ 15, "quattuordecillion" },
{ 16, "quindecillion" },
{ 17, "sexdecillion" },
{ 18, "septdecillion" },
{ 19, "octdecillion" },
{ 20, "novemdecillion" },
{ 21, "vigintillion" }
}
)
NumberWords =
DATATABLE(
"Number", INTEGER,
"Word", STRING,
{
{10, "ten"},
{11, "eleven"},
{12, "twelve"},
{13, "thirteen"},
{14, "fourteen"},
{15, "fifteen"},
{16, "sixteen"},
{17, "seventeen"},
{18, "eighteen"},
{19, "nineteen"},
{20, "twenty"},
{30, "thirty"},
{40, "forty"},
{50, "fifty"},
{60, "sixty"},
{70, "seventy"},
{80, "eighty"},
{90, "ninety"}
}
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment