Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate performance

Dear all,

 I've been trying to calculate performances of my time without success.. please help!

I have a data series like this:

 

Case created datePerformance of closed case
2-Jan-18On time
9-Jan-18Late
16-Jan-18Late
23-Jan-18On time
30-Jan-18On time
6-Feb-18On time
13-Feb-18On time
20-Feb-18On time
27-Feb-18Late
6-Mar-18Late
13-Mar-18On time
20-Mar-18Late
27-Mar-18Late
3-Apr-18On time
10-Apr-18On time
17-Apr-18On time
24-Apr-18Late
1-May-18On time

 

I'd like to create 2 visuals:

 

1- A gauge with % of cases closed on time (total)

2- An histogram having for X-axis the month/year, and for Y-axis % of cases closed on time for that month, i.e. a trend line showing whether my team is improving or getting worse.

 

Thanks a lot!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago
    Measure 13 = 
VAR __onTime = COUNTX(FILTER('Table13',[Performance of closed case]="On time"),[Performance of closed case])
VAR __all = COUNTX(ALL('Table13'),[Performance of closed case])
RETURN DIVIDE(__onTime,__all,0)

    See Page 6 of the attached file.

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago
    Measure 13 = 
VAR __onTime = COUNTX(FILTER('Table13',[Performance of closed case]="On time"),[Performance of closed case])
VAR __all = COUNTX(ALL('Table13'),[Performance of closed case])
RETURN DIVIDE(__onTime,__all,0)

    See Page 6 of the attached file.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the quick reply. Unfortunately this doesn't work with my file. See below. My file has a lot more columns and several different date columns. Would that be a problem? Should I create a new table withextracting just these 2 columns (if so, how do I do that)? Thanks a lot.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Its showing as 0 because the measure needs to formatted as Percentage from the Modelling tab

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks a lot for the prompt reply, however this doesn't seem to work in my file. See below. I've repeated exactly your steps.

      Obviously in my file there are a lot more columns and more than one date column. Can that be a problem? Thank you.

       

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    I test with Greg_Deckler's file, it runs correctly on my site, for your scenario shown in the pictures above, i suggest you go to "Modeling"->"Data type", change whole number to decimal number

                          "Format", change whole number to percentage

     

     

    Base on your example data, i make a test as below

     

    1. create a calendar date table with columns [Date],[year],[month], then manage relationships with the data table  ([Case created date] <->[Date])

     

    2.create columns and measures in "Sheet3"

    calculated column

    month no = MONTH([Case created date])

    Measures

    % of cases closed on time =
    CALCULATE (
        COUNT ( Sheet3[Performance of closed case] ),
        FILTER ( ALL ( Sheet3 ), [Performance of closed case] = "On time" )
    )
        / CALCULATE ( COUNT ( Sheet3[Performance of closed case] ), ALL ( Sheet3 ) )
    % per month =
CALCULATE (
    COUNT ( Sheet3[Performance of closed case] ),
    FILTER (
        ALL ( Sheet3 ),
        [month no] = MAX ( [month no] )
            && [Performance of closed case] = "On time"
    )
)
    / CALCULATE (
        COUNT ( Sheet3[Performance of closed case] ),
        FILTER ( ALL ( Sheet3 ), [month no] = MAX ( [month no] ) )
    )

    add [% of cases closed on time] in the gauge visual

    add [% per month] in the Value field of a line chart, calendar date[month no] in the X-axis field.

     

     

    Best Regards

    Maggie