Number formatting in Qlik sense for a Table

[kaushi2020]

Hi All, 

I have a table with columns. in the columns I am using below expression to derive a number.

//Expression before adding symbol %

if((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') ,Num(Sum([Jun-2023]),'##.00'))) > 1000000,
num((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') ,Num(Sum([Jun-2023]),'##.00')))/1000000, '#.##0,##M', ',', '.'),
if((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') ,Num(Sum([Jun-2023]),'##.00'))) > 1000,
num((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') ,Num(Sum([Jun-2023]),'##.00')))/1000, '#.##0,##K', ',', '.'),
num((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') ,Num(Sum([Jun-2023]),'##.00'))), '#.##0,##', ',', '.')))

what is does - If Column UNITS has a %, then it will divide the value with 100 and display.

Here when I add a % just as a concatenation (See below expression) the output is different. seems it is doing some calculation automatically.

//Expression post adding symbol %

if((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') &'%',Num(Sum([Jun-2023]),'##.00'))) > 1000000,
num((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') &'%',Num(Sum([Jun-2023]),'##.00')))/1000000, '#.##0,##M', ',', '.'),
if((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') &'%',Num(Sum([Jun-2023]),'##.00'))) > 1000,
num((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') &'%',Num(Sum([Jun-2023]),'##.00')))/1000, '#.##0,##K', ',', '.'),
num((if(Units='%',Num(Sum([Jun-2023]/100),'##.00') &'%',Num(Sum([Jun-2023]),'##.00'))), '#.##0,##', ',', '.')))

Number formatting is set to Auto here.

[vinieme12]

try below

Num(
if(Unit='%',Sum([Jun-2023]/100), Sum([Jun-2023]) )
,if(Unit='%','##.00%',pick(floor(log10(Sum([Jun-2023])) / 3)+1,'#,##0.##','#,##0.##K','#,##0.##M','#,##0.##B'))
)