How to show the % Cumulative Planned at a time interval for Subsets and not Overall when I filter

[SKY-07]

My data in Excel

I have my data: project activities which are spread over a timeline. The activities are grouped by another column called WBS.

Each activity has different start and finish dates within the timeline.

I calculated the cumulative % planned progress for all the activities to determine the project's planned progress at a selected date within the timeline.

Now I want to determine the % planned progress for each group of activities under a WBS over their individual timeline but this will be achieved when I filter by the WBS or even filter by Activities.

Example: the project overall cumulative % progress is 45%: **bleep** planned units till selected date/overall Units

but WBS_1 under the project is 90% complete

WBS_2 is 70%

WBS_3 is 20%

etc.

[v-zhangti · Accepted solution]

Hi, @SKY-07 

You can try the following methods.

Select the first four columns-Unpivot Other columns:

Rename the date column.

overall Units = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[WBS Name],'Table'[Activity Name]),[Spreadsheet Field]="Budgeted Units"))

Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[WBS Name],'Table'[Activity Name]),[Spreadsheet Field]="Actual Units"&&[Date]<=MAX('Date'[Date])))

Measure % = DIVIDE([Measure],[overall Units])

Is this the result you expect? Please see the attached document. If this fails to solve your problem, please advise what output you would expect based on the data you have provided.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SKY-07]

@v-zhangti  thank you so so so so so so muccchhhhhhh!!!!!!!

It was just what I needed.

I wish I could like the solution 100 times!

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[SKY-07]

Done as requested

[lbendlin]

I still see only screenshots?

[SKY-07]

Project

WBS Name

Activity Name

Spreadsheet Field

11/12/2023

11/19/2023

11/26/2023

12/3/2023

12/10/2023

#########

#########

#########

1/7/2024

1/14/2024

1/21/2024

1/28/2024

2/4/2024

2/11/2024

2/18/2024

2/25/2024

3/3/2024

3/10/2024

3/17/2024

3/24/2024

3/31/2024

4/7/2024

4/14/2024

4/21/2024

NEWPROJ

FEED

FEED Design

Budgeted Units

10

10

10

10

NEWPROJ

FEED

FEED Design

Actual Units

5

NEWPROJ

DED

DED Design

Budgeted Units

13

13

13

NEWPROJ

DED

DED Design

Actual Units

3

NEWPROJ

Procurement

Procurement Activity

Budgeted Units

8

40

40

40

40

40

32

NEWPROJ

Procurement

Procurement Activity

Actual Units

NEWPROJ

Const

Construction 1

Budgeted Units

8

40

40

40

40

40

40

40

40

32

NEWPROJ

Const

Construction 1

Actual Units

NEWPROJ

Const

Construction 2

Budgeted Units

8

40

40

40

40

40

40

40

32

NEWPROJ

Const

Construction 2

Actual Units