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
Super 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

Very thankful Sir for helping with this code.