need this as a %

[#1]

Anybody help us get the following expression to show as a % on a straight table?

100 - (Count (if (Ethnicity = 'NULL',[Client No]))/Count (Ethnicity))

the usual, '#.##' or changing number format doesn't work.

Chris

[sunny_talwar]

Try this:

Num(Count({<Ethnicity = {"=NullCount(Ethnicity) > 0"}>} [Client No])/Count(Ethnicity), '##.##%')

[#3]

Hi Sunny, Unfortunately that comes back with 0.00%

[sunny_talwar]

I think the issue is the numerator part. Can you try to see if NullCount(Ethnicity) give you the right number for the numerator part? If it does, try this:

Num(NullCount(Ethnicity)/Count(Ethnicity), '##.##%')

Else, sticking with your expression, may be this:

Num(100 - (Count (if (Ethnicity = 'NULL',[Client No]))/Count (Ethnicity)), '##.##%')

[MK_QSL]

Try this.

Num(1-COUNT(If(IsNull(Ethnicity) or Len(Trim(Ethnicity))=0 or Upper(Ethnicity) = 'NULL', [Client No]))/COUNT(TOTAL Ethnicity),'#,##0.0%')

[Kushal_Chawda]

try this

num(Count(if(lower(trim(Ethnicity))='null' or len(trim(Ethnicity))=0,[Client No])) / Count(Ethnicity),'#0.00%')

[#7]

Tried NullCount(Ethnicity) but it gives a result of 0 - this is wrong as you can see:

Num(100 - (Count (if (Ethnicity = 'NULL',[Client No]))/Count (Ethnicity)), '##.##%') works but creates the figure in the wrong format : 9996.77%

[sunny_talwar]

Ass pointed by Manish, you should be subtracting from 1 and not 100 here. His suggestion should work or try this:

Num(1 - (Count (if (Ethnicity = 'NULL',[Client No]))/Count (Ethnicity)), '##.##%')

[#9]

Yes this looks better. Its in a % percentage but need to tweak.

The calculation we need is 100% - Null - Refused (another category - see table below). The expression at the minute is only doing NULL

[Kushal_Chawda]

try this

Num(1-COUNT(If(match(lower(Ethnicity), 'null','refused'), [Client No]))/COUNT(TOTAL Ethnicity),'#,##0.0%')