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 hidden or 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 hidden or 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 hidden or 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 hidden or 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