topic Re: How to show the % Cumulative Planned at a time interval for Subsets and not Overall when I filter in DAX Commands and Tips

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

SKY-07 — Mon, 27 Nov 2023 08:02:17 GMT

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.

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

lbendlin — Fri, 24 Nov 2023 19:03:41 GMT

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-Forum/ba-p/963216
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/1447523

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

SKY-07 — Mon, 27 Nov 2023 08:02:38 GMT

Done as requested

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

lbendlin — Mon, 27 Nov 2023 12:30:27 GMT

I still see only screenshots?

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

SKY-07 — Mon, 27 Nov 2023 13:40:15 GMT

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

NEWPROJ

FEED

FEED Design

Actual Units

NEWPROJ

DED

DED Design

Budgeted Units

NEWPROJ

DED

DED Design

Actual Units

NEWPROJ

Procurement

Procurement Activity

Budgeted Units

NEWPROJ

Procurement

Procurement Activity

Actual Units

NEWPROJ

Const

Construction 1

Budgeted Units

NEWPROJ

Const

Construction 1

Actual Units

NEWPROJ

Const

Construction 2

Budgeted Units

NEWPROJ

Const

Construction 2

Actual Units

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

v-zhangti — Fri, 01 Dec 2023 02:17:51 GMT

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.

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

SKY-07 — Fri, 01 Dec 2023 09:20:17 GMT

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!