Calculating project % complete based on average category time to completion

[reric4]

Hi all, 

Background:

I have a table that includes multiple Projects that roll up to different Categories.

I am needing to calculate the Project % complete based on the Average Time-to-Completion by Category. 

Desired result:  If a project is complete, result = 100% ; If a project is not complete, result = Duration / Avg Time-to-Completion for the related Category. Any help with coming up with this measure is much appreciated!

I've created the following example measures and tables below (also included the desired result):

Duration = IF(MAXX(Table,Table[EndDate]) = BLANK(), CALCULATE(DATEDIFF(MAXX(Table,Table[StartDate]),TODAY(),MONTH)), CALCULATE(DATEDIFF(MAXX(Table,Table[StartDate]),MAXX(Table,Table[EndDate]),MONTH)))

Time to Completion = DATEDIFF(MAXX(Table,Table[StartDate]),MAXX(Table, Table[EndDate]),MONTH)

Avg Time-to-Completion = AVERAGEX(SUMMARIZE('Table', Table[Project], "Avg Time-to-Completion", [Time to Completion]), [Time to Completion])

[v-diye-msft]

Hi @reric4 ,

I got your point, but can't replicate your data coz the measures you mentioned relates to the column [Startdate] and [Enddate], that would be preferred to share us your dummy pbix or simple worksheet via Onedrive/Sharepoint/Dropbox/just copy and paste here. 

Best regards,

Dina Ye

[reric4]

Thanks @v-diye-msft , 

I'd be happy to share the dummy pbix file.  Here is the dropbox link:

https://www.dropbox.com/s/8u0tyusa24uv1mi/Percent%20Complete%20Dummy%20Project.pbix?dl=0

Thanks again

[shawne · Accepted solution]

First:  Wrap your AverageX measure in a calculate and utilze the allexcept function like this: 

Avg Time-to-Completion =
CALCULATE (
AVERAGEX (
SUMMARIZE (
'Table',
'Table'[Project],
"Avg Time-to-Completion", [Time to Completion]
),
[Time to Completion]
),
ALLEXCEPT ( 'Table', 'Table'[Category ] )
)

Then create a conditional divide: 

% Complete (Desired Result) =
IF (
SELECTEDVALUE ( 'Table'[EndDate] ),
1,
DIVIDE ( [Duration], [Avg Time-to-Completion] )
)

The calculate allows you to add filter context.  The SELECTEDVALUE will basically flag for finished projects to get a result of 100% else divide by the duration into the result of the previous measure........

[reric4]

@shawne thanks for the help....works great!