1 Reply Latest reply on Nov 19, 2016 2:18 PM by Yuriy Fal

# 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               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.

No. of open ticket up to that respective 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.

• ###### 1. Re: Return COUNT of a measure with several conditions.

You may want to have both Open Date and Close Date as a single Date Dimension.

When connecting to Text / Excel, you can Pivot your Date columns into a single Dim.

After that, everything is straightforward -- using a Running Total Table Calculation.

Please find the attached as an example using Sample Superstore datasource.

Hope it could help.

Yours,

Yuri