3 Replies Latest reply on Oct 24, 2017 4:10 PM by Deepak Rai

# Count number of Green KPI for specific Metric

Hello Fellow Data People,

I am creating a scorecard, where the the hierarchy is as follows:

Division -Agency 1 -Ticket 1 100

-Ticket 2 25

-Ticket 3 25

.

.

.

here the average of the ticket value is the value of the agency 1(green, yellow or red) in the division. Similar is the case for agency 2, agency 3 etc.

There are 3 KPIs, Green, Yellow and Red and it is being measured on weekly basis.

Now I have to count the number of greens/week in all of the 3 agencies (agency 1- Green, agency 2- Green, agency 3- yellow) and perform the calculation:

Number of greens / total number of agencies, i.e as per above example 2/3 = 0.75 (75%)

But i am not able to get the value since I am taking the average of tickets, hence I cannot take the aggregation of the value already aggregated.

Any help would be really helpful.

I have also attached sample worksheet for reference.

Regards,

Abdul

• ###### 1. Re: Count number of Green KPI for specific Metric

Here it is, Little Tricky , but done, I used the data in view Supplied by you on The Greens in View. My Initial Thought was LOD, But it Could not go far and Broke.

So in above you have 1, 2 and 1 green Weekwise

So Created This View:

Created another Calc:

Got This:

Created a Filter to get the Last Value which is actually Needed per Week:

Drag it to Filter and set TRUE and Format to Percent.

That's it.

SET Calculation3 at The Level of Agency: Important

I Dragged Date to Detail too, but it Hardly makes any Difference, I think. Check attached

Looks  Easy Now

Thanks

Deepak

• ###### 2. Re: Count number of Green KPI for specific Metric

Wow! Thank You!

This was really useful. Really got my question answered on point !

One thing I would like to add for anyone who is going to implement this solution is that:

Please make sure you study every pill's calculation- for example FILTER is made to calculate downwards.

Genius man.

Thank You so much.

Regards,

Abdul

• ###### 3. Re: Count number of Green KPI for specific Metric

Thanks Abdul. Glad it Helped You

Deepak