count in pivot table

[Anonymous]

Hi,

I have a pivot table whre i need to count the KPI's basd on target

I dont want target as a dimension in a chart how ever i need the count value based on target column

if KPI sum ie NUm/denom is >= target then i need to count the target values.

Could anyone please help me on this?

I dont want target as a dimension in a chart how ever i need the count value based on target column

[sunny_talwar]

Where are you getting 2.98 from? Can you point out in the below screenshot?

[Anonymous]

Hi sunny sorry it is 2.948 thht is for childcare less than Target , 100.3 for control > target ..... for those respective areas

i need this count

[sunny_talwar]

So, in terms of a numerical output, what is your desired output here?

[Anonymous]

i am not sure of accurate amount....  may be 5 or 7

i need to figure out that.. but my intention is to get the numerics..

do you find any way?

[sunny_talwar]

So let me clarify this

Here we have all the child care numbers less than Target so we will count 3 for Area1?

2.95 < 90

50.35 < 91

0.99 < 95

Area1 Count of Child Care = 3?

[Anonymous]

Yes that is correct...

[Anonymous]

however we dnt need that Target in dimensions but for understanding i have created this.. this target value comparison has to be make in expression...

[sunny_talwar · Accepted solution]

Try this:

=Sum(Aggr(If(

Sum(Numerator)

/

Sum(Denominator) <= Target, 1, 0), AreaID, Target, KPI_Category))

[sunny_talwar]

Yup, as you see above, Target is not one of the dimensions