1 Reply Latest reply on Nov 4, 2016 3:30 AM by Norbert Maijoor

# Return COUNT of a measure with several conditions.

I have a graph which shows the count of [ticket No] versus Month(closed date). Filtering the [status] to only "close", it displays the number of closed tickets versus month by which they were closed.

Now, I want to convert this count into percentage to calculate the KPI of each month, and it gets a little bit tricky.

The way it works is, to calculate the KPI, I need to pull the number of closed ticket of that particular month over number of open tickets up to that month( that month and prior).

For example:

to calculate the KPI of April, it would be like this ; No. of closed ticket in April x 100

No. of open ticket up to April(Jan, Feb, Mar)

My first idea was to use a calculated field to return the count of ticket which satisfy the condition where Month(closed date) = 4 and [status]="closed'. But I realized I also need to calculate the KPI for subsequent months. So, using  Month(closed date) = 4 is not quite feasible as the month of the closed date is fixed.

therefore, the formula for KPI that can be used for all months would be:

No. of closed ticket in a respective month x 100

No. of open ticket up to that respective month

thus, how do I create a calculated field that return count of ticket no with the condition of status = closed and only for that particular month.

I have another idea to use CASE for KPI calculation of every month. But I'm wondering if there is a better way to do it.