Forum Discussion
Calculating project % complete based on average category 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........
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........