KPI to say How many individuals are below target percentage (say 50%)

[jiwaniakbiar]

I have 2 tables.

One table provides city-wise Targets as below:

Country	State	City	Target
India	Telangana	Hyderabad	5
India	Telangana	Secunderabad	2
India	Maharashtra	Bombay	2
Canada	Ontario	Toronto	400
Canada	Ontario	Missisauga	200

S.No	City	Status	Date
1	Hyderabad	Complete	20-Feb-19
2	Hyderabad	Complete	21-Feb-19
3	Hyderabad	Partial	21-Feb-19
4	Bombay	Complete	22-Feb-19
5	Bombay	Complete	22-Feb-19

I want to show a KPI which says # of centers whose completion percentage is < 50%

so here it should say 4 because only bombay is complete 100%.

[Channa]

if Status completed you want to show as 4

because for bomaby target is 2..

=sum({<City={'Bombay'},State={'Maharashtra'},Status={'Complete'}>} Target)

=count({<City={'Bombay'},State={'Maharashtra'},Status={'Complete'}>} Status)

[jiwaniakbiar]

Thanks but this will not help as I cannot hardcode city. Plus I am looking for count of citys where target is < 50% achieved

So basically I am looking at combining two steps

1. Calculate percentage completion for each city. This will be Count(Status=Complete)/Target for each city

2. Count of all the entries below 50% in step 1 above

[Channa]

see the attach

it has expression and QVF

[jiwaniakbiar]

Hi Channa,

Thanks for sharing the file. This is displaying a table. I am looking for a KPI.  I have attached my attempt to articulate.

[Channa]

=count({<Status={'Complete'},City={'Bombay'}>} Status)/sum({<Status={'Complete'},City={'Bombay'}>} Target)

it is for bombay