Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
reric4
Frequent Visitor

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)

 

Table1.JPG

 

 

 

 

 

 

 

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

 

Table2.JPG

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

View solution in original post

4 REPLIES 4
v-diye-msft
Community Support
Community Support

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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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

reric4
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.