Calculate performance

[Anonymous]

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 date	Performance of closed case
2-Jan-18	On time
9-Jan-18	Late
16-Jan-18	Late
23-Jan-18	On time
30-Jan-18	On time
6-Feb-18	On time
13-Feb-18	On time
20-Feb-18	On time
27-Feb-18	Late
6-Mar-18	Late
13-Mar-18	On time
20-Mar-18	Late
27-Mar-18	Late
3-Apr-18	On time
10-Apr-18	On time
17-Apr-18	On time
24-Apr-18	Late
1-May-18	On 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!

[v-juanli-msft]

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

[Anonymous]

I've never done a histogram so no clue... but heres the rest

https://ufile.io/9gsli

[Anonymous]

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.

[LivioLanzo]

Hi @Anonymous

find the pbix here: https://1drv.ms/u/s!AiiWkkwHZChHjzXNttwJLwETVXxx

[Greg_Deckler · Accepted solution]

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]

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]

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