Forum Discussion
Calculating project % complete based on average category time to completion
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])
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........
4 Replies
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
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
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........
