Displaying Numbers in Words

[Anonymous]

Hi, 

I'm looking at the way of displaying numbers as words using DAX.

I have a basic sum DAX measure and I want the display to be shown in words so example would be if the total is 100 then I would like it to show " one hundred" if the total adds up to 10.5 then I want it to display "eleven". 

Is there a way to display numbers from the measure like that? 

[lbendlin]

Attached to message 13

[JAD22]

Hi, 

I have been asked to produce something very similar, but also include the decimals as part of the text string. How would I be able to augment the dax measure code below to include the decimal values.

i.e. £1001.99

converts to One Thousand and One Pounds and Ninety Nine Pence 

[lbendlin]

shouldn't that be "One Thousand One Pound and Ninety Nine Pence " ?

You will need to explain the (british) english rules a bit more.

[JAD22]

Yes, that is correct typo on my part. I've listed out below a list of examples which follow the convention used in English I think you mean by rules. If the unit value is 1 we use the singular Pound, once pound value is greater than 1 we use the plural (Pounds). Anything to the right of the decimal point is always pence. If I've mis-interpreted your ask please let me know.

Some simple rules:

£0.99 = Ninety Nine Pence

£1.00 = One Pound

£1.99 = One Pound and Ninety Nine Pence

£10.00 = Ten Pounds

£100.00 = One Hundred Pounds

£101.00 = One Hundred and One Pounds
£110.00 = One Hundred and Ten Pounds

£1000.00 = One Thousand Pounds

£1001.00 = One Thousand and One Pounds

£1,010.00 = One Thousand and Ten Pounds

£1,100.00 = One Thousand One Hundred Pounds

£10,000.00 = Ten Thousand Pounds

£10,001.00 = Ten Thousand and One Pounds

£10,010.00 = Ten Thousand and Ten Pounds

£10,100.00 = Ten Thousand One Hundred Pounds

£10,101.00 = Ten Thousand One Hundred and One Pounds

£10,110.00 = Ten Thousand One Hundred and Ten Pounds

£100,000.00 = One Hundred Thousand Pounds

£100,001.00 = One Hundred Thousand and One Pounds

£100,010.00 = One Hundred Thousand and Ten Pounds

£100,100.00 = One Hundred Thousand and One Hundred Pounds

£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds

£101,000.00 = One Hundred and One Thousand Pounds

£101,001.00 = One Hundred and One Thousand and One Pounds

£101,010.00 = One Hundred and One Thousand and Ten Pounds

£101,110.00 = One Hundred and One Thousand One Hundred and Ten Pounds

£110,000.00 = One Hundred and Ten Thousand Pounds

£1,000,000.00 = One Million Pounds

£1,000,001.00 = One Million and One Pounds

£1,000,010.00 = One Million and Ten Pounds

£1,000,100.00 = One Million and One Hundred Pounds

£1,000,110.00 = One Million One Hundred and Ten Pounds

£1,001,110.00 = One Million One Thousand One Hundred and Ten Pounds

£1,010,110.00 = One Million Ten Thousand One Hundred and Ten Pounds

£1,110,110.00 = One Million One Hundred and Ten Thousand One Hundred and Ten Pounds

Regards

JAD22

[lbendlin]

Sorry but this part

£100,100.00 = One Hundred Thousand and One Hundred Pounds

£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds

makes no sense. Should be either - or.  Can we compromise on

£100,100.00 = One Hundred Thousand One Hundred Pounds

£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds

?

[JAD22]

Hi @lbendlin,

I accept the compromise statements.

[lbendlin]

Please try the PBIX I attached with a sample of your expected values, and let me know if the code breaks somewhere.

[JAD22]

Hi Ibendlin,

I've done some testing on the Dax, and everything works perfectly. This is a fantastic. It has saved a considerable amount of resource time and effort as well as completely removing the human error factor.
Thank you.

[lbendlin]

@JAD22 thank you for the nice challenge. I learned a lot from it.

[JAD22]

Hi Ibendlin,

I can't see the .pbx file you attached, can you sent it again so I can test it for you.

[Anonymous]

@Anonymous 

Here's What I tried to implement this using a measure.

Currently this handles upto 9999. 

EX = 
//Currently Handles Upto 9999


//Number to be converted into words. Replace this with SELECTEDVALUE or as desired.
VAR Number = SELECTEDVALUE(T[Value])//115

VAR NumberToText = "" & Number

VAR NoOfDigits =
    LEN ( NumberToText )

VAR Ones =
    SELECTCOLUMNS (
        GENERATESERIES ( 0, 9, 1 ),
        "Ones Digit", [Value],
        "Ones Digit in Words", SWITCH (
            [Value],
            1, "One",
            2, "Two",
            3, "Three",
            4, "Four",
            5, "Five",
            6, "Six",
            7, "Seven",
            8, "Eight",
            9, "Nine"
        )
    )
VAR OneTens =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, 9, 1 ),
        "One Tens Digit", [Value],
        "One Tens Words", SWITCH (
            [Value],
            1, "Eleven",
            2, "Twelve",
            3, "Thirteen",
            4, "Fourteen",
            5, "Fifteen",
            6, "Sixteen",
            7, "Seventeen",
            8, "Eighteen",
            9, "Nineteen"
        )
    )
VAR Tens =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, 9, 1 ),
        "Tens Place", [Value],
        "Tens Words", SWITCH (
            [Value],
            1, "Ten",
            2, "Twenty",
            3, "Thirty",
            4, "Fourty",
            5, "Fifty",
            6, "Sixty",
            7, "Seventy",
            8, "Eighty",
            9, "Ninety"
        )
    )
VAR PlaceValue =
    SELECTCOLUMNS (
        GENERATESERIES ( 3, 6, 1 ),
        "Place", [Value],
        "Place Value", SWITCH ( [Value], 3, "Hundred", 4, "Thousand")
    )


VAR X =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, NoOfDigits, 1 ),
        "Index", [Value],
        "Dec",
        VAR N =
            INT ( Number / INT ( 1 & REPT ( "0", [Value] - 1 ) ) )
        RETURN
            MOD ( N, 10 )
    )
VAR numberMod100 = MOD(Number, 100)
VAR Y =
    ADDCOLUMNS (
        X,
        "Text", SWITCH (
            TRUE (),
            [Index] = 1, SWITCH (
                TRUE (),
                numberMod100 > 10
                    && numberMod100 < 20, MAXX ( FILTER ( OneTens, [One Tens Digit] = [Dec] ), [One Tens Words] ),    
                MAXX ( FILTER ( Ones, [Ones Digit] = [Dec] ), [Ones Digit in Words] )
            ),
            [Index] = 2, SWITCH (
                TRUE (),
                numberMod100 > 10
                    && numberMod100 < 20, "",
                MAXX ( FILTER ( Tens, [Tens Place] = [Dec] ), [Tens Words] )
            ),
            // Handles for 1-9 and beyond index 2
            IF (
                [Dec] = 0,
                "",
                // get the Word representation of current Digit
                MAXX ( FILTER ( Ones, [Ones Digit] = [Dec] ), [Ones Digit in Words] )
                // Concatenate the current digit with its place value.
                 & " " & MAXX ( FILTER ( PlaceValue, [Place] = [Index] ), [Place Value] )
            )
        )
    )
RETURN
    CONCATENATEX(Y, [Text], " ", [Index], DESC)

This can be extended to handle every natural number  with few more lines of DAX.

Hope you get the idea.

Hope @lbendlin can help on this. I don't know if there's a better way than this.

Thanks

[lbendlin]

For simplified English you only need to solve for the last group of three digits, in a 1-2 pattern. Any other larger digit places are just repetition, with "thousand", "million" etc slapped on. 

At least that's my theory...

[lbendlin]

---

Here's my version for the last three digits. "ParameterNumber[ParameterNumber value]"  is the measure that you want to convert. Nothing is returned for Zero.

Spelt = 
var t = "000" & format(ParameterNumber[ParameterNumber Value],"#")
var s = right(t,1)
var ss = switch(s,"1","one","2","two","3","three","4","four","5","five","6","six","7","seven","8","eight","9","nine","")
var sd = right(t,2)
var sds = switch(sd,"01","one","02","two","03","three","04","four","05","five","06","six","07","seven","08","eight","09","nine","10","ten"
                 ,"11","eleven","12","twelve","13","thirteen","14","fourteen","15","fifteen","16","sixteen","17","seventeen"
                 ,"18","eighteen","19","nineteen","")
var t1 = left(t,len(t)-1)
var d = right(t1,1)
var ds = switch(d,"2","twen","3","thir","4","four","5","fif","6","six","7","seven","8","eigh","9","nine","")
var dd = switch(d,"0","","1","",ds & "ty ")
var t2 = left(t1,len(t1)-1)
var h = right(t2,1)
var hs = switch(h,"1","one","2","two","3","three","4","four","5","five","6","six","7","seven","8","eight","9","nine","")
var hd = switch(h,"0","",hs & " hundred ")
return hd & dd & if(d<"2",sds,ss) 

[lbendlin · Accepted solution]

Here is a more concise version that can also be easily extended to more digit triplets, since the lookup table will be the same for each of the triplets.

Spelt2 = 
var l = {("1","one","eleven","ten"),("2","two","twelve","twenty"),("3","three","thirteen","thirty"),
("4","four","fourteen","fourty"),("5","five","fifteen","fifty"),("6","six","sixteen","sixty"),
("7","seven","seventeen","seventy"),("8","eight","eighteen","eighty"),("9","nine","nineteen","ninety")}
var t = "0" & format(ParameterNumber[ParameterNumber Value],"#")
var s = right(t,1)
var d = left(right(t,2),1)
var h = left(right(t,3),1)
var sd = if(d="1",concatenatex(filter(l,[Value1]=s),[Value3]),concatenatex(filter(l,[Value1]=s),[Value2]))
var dd = if(d>"1" || d & s = "10",concatenatex(filter(l,[Value1]=d),[Value4]) & " ")
var hd = if(h>"0",concatenatex(filter(l,[Value1]=h),[Value2]) & " hundred ")
return hd & dd & sd 

Here's the version for up to six digits:

Spelt2 = 
var l = {("1","one","eleven","ten"),("2","two","twelve","twenty"),("3","three","thirteen","thirty"),
("4","four","fourteen","fourty"),("5","five","fifteen","fifty"),("6","six","sixteen","sixty"),
("7","seven","seventeen","seventy"),("8","eight","eighteen","eighty"),("9","nine","nineteen","ninety")}
var t = "0" & format(ParameterNumber[ParameterNumber Value],"#")
var s = right(t,1)
var d = left(right(t,2),1)
var h = left(right(t,3),1)
var ss = if(d="1",concatenatex(filter(l,[Value1]=s),[Value3]),concatenatex(filter(l,[Value1]=s),[Value2]))
var dd = if(d>"1" || d & s = "10",concatenatex(filter(l,[Value1]=d),[Value4]) & " ")
var hd = if(h>"0",concatenatex(filter(l,[Value1]=h),[Value2]) & " hundred ")
var s2 = left(right(t,4),1)
var d2 = left(right(t,5),1)
var h2 = left(right(t,6),1)
var ss2 = if(d2="1",concatenatex(filter(l,[Value1]=s2),[Value3]),concatenatex(filter(l,[Value1]=s2),[Value2])) 
var dd2 = if(d2>"1" || d2 & s2 = "10",concatenatex(filter(l,[Value1]=d2),[Value4]) & " ")
var hd2 = if(h2>"0",concatenatex(filter(l,[Value1]=h2),[Value2]) & " hundred ")
return if(ss2>"",hd2 & dd2 & ss2 & " thousand ") & hd & dd & ss 

In action:

[Anonymous]

Hi Sir,

We have decimals also, we want to convert amount to words along with decimals based on CCY

ex. INR 50.25 = Fifty rupees and twenty five paisa

USD 50.25 = Fifty dollars and twenty five cent

INR 50000.25 = Fifty thousands twenty five paisa

INR 565786.50 = Five lakh sixty five thousand seven  hundred eight six fifty paisa

this should be inline with CCY like lakh, crore for INR .. million, billion for USD (as applicable)

Please help me the code. 

[lbendlin]

@Anonymous please provide more samples, especially with crore and lakh sized values. Please indicate if "and" is required before the paisa.

Attached is aproposal to handle the Indian number format. Please validate.

[Anonymous]

Yes "and" is required before paisa.

Please find the below samples.

5,75,786.56 -- Five lakh seventy five thousand seven hundred eighty six Rupees and fifty-six Paisa

89,75,88,789.25 --- Eighty Nine core seventy five lakhs eighty eight thousand seven hundred eighty nine Rupees and twenty five paisa.

I will validate the PBIX and confirm you sir.

[Anonymous]

Examples :-

5,75,786.56 -- Five lakh seventy five thousand seven hundred eighty six Rupees and fifty-six Paisa

89,75,88,789.25 --- Eighty Nine core seventy five lakhs eighty eight thousand seven hundred eighty nine Rupees and twenty five paisa.

USD 1,025,300.25 = One Million Twenty Five Thousand Three Hundred Dollars and Twenty-Five Cents

INR 10,25,300.25 =  Ten Lakh Twenty Five Thousand Three Hundred Rupees and Twenty-Five Paisa Only

[lbendlin]

There are a couple of nuances but in general this should work.  You can parameterize the currency name if you want.

Spelt2 IN = 
var l = {("1","One","Eleven","Ten"),("2","Two","Twelve","Twenty"),("3","Three","Thirteen","Thirty"),("4","Four","Fourteen","Fourty"),("5","Five","Fifteen","Fifty"),("6","Six","Sixteen","Sixty"),("7","Seven","Seventeen","Seventy"),("8","Eight","Eighteen","Eighty"),("9","Nine","Nineteen","Ninety")}
var t = "0" & format(int([Value]),"#")
var p = format([Value]-int([Value]),".##") & "00"
-- paisa
var sp0 = right(left(p,3),1) var dp0 = right(left(p,2),1)
var ssp0 = if(dp0="1",concatenatex(filter(l,[Value1]=sp0),[Value3]),concatenatex(filter(l,[Value1]=sp0),[Value2]))
var ddp0 = if(dp0>"1" || dp0 & sp0 = "10",concatenatex(filter(l,[Value1]=dp0),[Value4]) & " ")
var paisa = if([Value]>=1," and ") & ddp0 & ssp0 & " Paisa"
-- singles triplet
var s0 = right(t,1) var d0 = left(right(t,2),1) var h0 = left(right(t,3),1)
var ss0 = if(d0="1",concatenatex(filter(l,[Value1]=s0),[Value3]),concatenatex(filter(l,[Value1]=s0),[Value2]))
var dd0 = if(d0>"1" || d0 & s0 = "10",concatenatex(filter(l,[Value1]=d0),[Value4]) & " ")
var hh0 = if(h0>"0",concatenatex(filter(l,[Value1]=h0),[Value2]) & " Hundred ")
var singles = hh0 & if(hh0>"" && dd0 & ss0>"","and ") & dd0 & ss0
-- thousands duplet
var s1 = left(right(t,4),1) var d1 = left(right(t,5),1) 
var ss1 = if(d1="1",concatenatex(filter(l,[Value1]=s1),[Value3]),concatenatex(filter(l,[Value1]=s1),[Value2])) 
var dd1 = if(d1>"1" || d1 & s1 = "10",concatenatex(filter(l,[Value1]=d1),[Value4]) & " ")
var thousands = dd1 & ss1 & if(dd1 & ss1 >""," Thousand ")
-- lakh duplet
var s2 = left(right(t,6),1) var d2 = left(right(t,7),1) 
var ss2 = if(d2="1",concatenatex(filter(l,[Value1]=s2),[Value3]),concatenatex(filter(l,[Value1]=s2),[Value2])) 
var dd2 = if(d2>"1" || d2 & s2 = "10",concatenatex(filter(l,[Value1]=d2),[Value4]) & " ")
var lakh = dd2 & ss2 & if( dd2 & ss2>""," Lakh ")
-- crore duplet
var s3 = left(right(t,8),1) var d3 = left(right(t,9),1) 
var ss3 = if(d3="1",concatenatex(filter(l,[Value1]=s3),[Value3]),concatenatex(filter(l,[Value1]=s3),[Value2])) 
var dd3 = if(d3>"1" || d3 & s3 = "10",concatenatex(filter(l,[Value1]=d3),[Value4]) & " ")
var crore = dd3 & ss3 & if( dd3 & ss3>""," Crore ")
-- extra fillers
var sa = if(crore & lakh & thousands >"" && h0="0" && dd0 & ss0 >"", " and ")
return if(int([Value])>0, trim(crore & lakh & thousands & sa & singles ) & " Rupee" & if([Value]>=2,"s")) & if(p<>".00",paisa)

[Anonymous]

Very thankful Sir for helping with this code.