Average number of days per state + present in chronological order

[yanivshe]

I have simple lab repair data for customer orders:

1) I want to calculate the average days by state:

2) Also, I want to present it in chronological order (the order starts as "new" -> "work" -> "revision" -> "closed")

How can I present it?

[yanivshe]

I added the column to my SQL source with RANK() and DATEDIFF in seconds to get the desired solution.

Thank you for each for your help with this

[yanivshe]

I added column in my SQL source with RANK() and DATEDIFF in seconds to get the desired solution.

Thanks for everyone for your help with this

[Ashish_Mathur]

Hi,

What does your source dataset look like?  Share that.

[danextian]

Hi @yanivshe 

You may use AVERAGEX to get the desired result. Try this as a measure:

Averagex = 
AVERAGEX ( VALUES ( States[STATE] ), AVERAGE ( States[Value] ) )

For the sort, create a conditional column  in the query editor instead of using DAX to create a sort column. You will get an error if you sort a column by a calculated column that is dependent to the column being sorted like below.

Do something like below in the query editor.

[mahoneypat]

It looks like you already have your average cycle time.  To get them to sort in the desired order, add a calculated column to your table with a switch() formula like this:

Status Sort = Switch([Status], "new", 1, "work, 2, "revision", 3, "closed", 4)

Then highlight your Status column and choose the new columns as your Sort By Column.  This will make them show up in that order in your visuals.

If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
Regards,
Pat