Measure: TAT for 80% of cases closed

[Avicric]

Hi All,
Please check the attached image need help to know if a measure can be created to snow the average time taken to close 80% of cases month on month...and other views...

Currently using the old school math is there a direct route

 

[EylesIT]

Avicric, I have a couple of questions about your problem.

Can you explain the data in the screenshot, please? In the screenshot, it shows 40.4% for (Apr-23, Row Label 1). What does "Row Label 1" mean?

In the screenshot it appears that the cells within a column highlighted in yellow add to exceed 80%. For Apr-23 the yellow cells are cells for Row Label 1 thru 5. Does this mean that in Apr-23 80% of cases were closed within "5"? What does "5" mean? I assume it is an ordinal unit of time such as minutes, hours, or days.

What is the desired output? In your question you are asking for the average time by month to close 80% of the cases within that month, which would be one number. But the screenshot shows several numbers per month. What is the correct number that you want for Apr-23?

[Avicric]

My bad

The image 

Rows = days taken

Columns = months

For April-23 it was 5 days

May-23 it was 6 days

And For Oct-23 it is 10 days

[EylesIT]

@Avicric, ok. So for your report, you want the output to be similar the image below, just one number that represents the TAT per month, and the months as columns in a matrix. Yes?

[Avicric]

Thats correct EylesIT...

I am able to get 80% closure count but when i try to calcelulate the TAT for this 80% the calulation is incorrect

[EylesIT]

@Avicric, here is my suggested solution.

In the absence of any test data or data file structures from you, I have created some test data, and some measures to do what I think you are looking for.

The first matric visual in my solution displays the cumulative % of cases closed within the number of days.

The next visual shows, by month, how many days after which the "80% case closed" threshold was exceeded

The next visual shows, by month, the average number of days to close a case for the 80% of cases.

The test data, measures, and visuals are all in the PBIX file in Dropbox

https://www.dropbox.com/scl/fi/emzwljdlazkd44zyzzpr3/Avicric-TAT.pbix?rlkey=90sb5imstpveqtaz6casse2o...

Is this what you are looking for?

[Avicric]

Hi EylesIT thanks,

I am to get the above calculations but thats not correct...

Like in the images posted by you

The visual which you have shown close TAT for 80% cases 

April-23=2.17 & May-23=4 is incorrect the correct TAT should show

April-23=6 & May-23=7

I have posted the measure i am using....in the above post

[Avicric]

Anyone with any suggestions....

Or even an excel formula will help

Thanks in advance

[EylesIT]

@Avicric, please can you post the DAX for the measures you currently have, and also a set of data (anonymised if required), or at least the structure of the table(s) involved.

[Avicric]

Hi EylesIT

I can tell how my data looks

Case ID(unique)

Date created - date and time

Date closed - date and time

Status = open/closed

TaT = closed - created in days

There are other headers like customer name, address, city and some properitary info

I need info for closed cases

I have created 2 calculated columns and 2 measures :

1) Rank = RANKX(FILTER('RawData', 'RawData'[Status] = "Closed"), 'RawData'[ClosureTime])

2) TotalCases = COUNTROWS(FILTER('RawData', 'RawaData'[Status] = "Closed"))
3) TargetCases = [TotalCases] * 0.8

4) 80% TaT:=CALCULATE( AVERAGE('RawData'[ClosureTime]), FILTER('RawData', 'RawData'[Status] = "Closed" && 'RawData'[Rank] <= [TargetCases]) )

I am getting similar results like the one you have created which is wrong as I think the closed cases TAT is not getting correctly considered

[FreemanZ]

hi @Avicric ,

Post Sample Data

UPDATE: @ImkeF wrote a fantastic article for the best way to post data to the forums: https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-.... 

[Avicric]

@FreemanZ i cannot share the data..

[EylesIT]

If you want other people to help, you should consider spending some time to create a set of data that you can share, along with the output you expect from the set of data.