Last active
March 3, 2023 18:00
-
-
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`
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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"} | |
} | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" } | |
} | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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