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

    Return COUNT of a measure with several conditions.

    Muhammad Faris bin Azaman

      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.