Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Displaying Numbers in Words

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 wou...
  • lbendlin's avatar
    lbendlin
    6 years ago

    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:

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Very thankful Sir for helping with this code.