Forum Discussion

reric4's avatar
reric4
Frequent Visitor
7 years ago
Solved

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-Completio...
Need Help
  • shawne's avatar
    shawne
    7 years ago

    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........

shawne's avatar
shawne
Resolver I
7 years ago

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's avatar
reric4
Frequent Visitor
7 years ago

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