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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SKY-07
Frequent Visitor

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

Export to post.jpg

 

My data in ExcelMy 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.

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @SKY-07 

 

You can try the following methods.

Select the first four columns-Unpivot Other columns:

vzhangti_0-1701395997445.png

Rename the date column.

vzhangti_1-1701396130106.png

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])

vzhangti_2-1701396951760.png

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.

View solution in original post

6 REPLIES 6
v-zhangti
Community Support
Community Support

Hi, @SKY-07 

 

You can try the following methods.

Select the first four columns-Unpivot Other columns:

vzhangti_0-1701395997445.png

Rename the date column.

vzhangti_1-1701396130106.png

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])

vzhangti_2-1701396951760.png

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.

@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
Super User
Super User

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

Done as requested

I still see only screenshots?

ProjectWBS NameActivity NameSpreadsheet Field11/12/202311/19/202311/26/202312/3/202312/10/2023###########################1/7/20241/14/20241/21/20241/28/20242/4/20242/11/20242/18/20242/25/20243/3/20243/10/20243/17/20243/24/20243/31/20244/7/20244/14/20244/21/2024
NEWPROJFEEDFEED DesignBudgeted Units10101010                    
NEWPROJFEEDFEED DesignActual Units5                       
NEWPROJDEDDED DesignBudgeted Units    131313                 
NEWPROJDEDDED DesignActual Units 3                      
NEWPROJProcurementProcurement ActivityBudgeted Units        8404040404032         
NEWPROJProcurementProcurement ActivityActual Units                        
NEWPROJConstConstruction 1Budgeted Units              8404040404040404032
NEWPROJConstConstruction 1Actual Units                        
NEWPROJConstConstruction 2Budgeted Units              84040404040404032 
NEWPROJConstConstruction 2Actual Units                        

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors