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

    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           

      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.