cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gdps
Helper IV
Helper IV

Sum depending on % of milestones

Hello all!

 

Let me try to explain my problem...

 

I created a standard milestone list for projects. There are 11 milestones in total.

 

The milestone that most impacts is "(10) Capex Validation & Request". In my database I have a column that indicates whether or not the project has a Capex milestone.

 

In the example, Project2 does not have the Capex milestone.

 

However, not all projects go through each milestone.

 

In the example, Project3 does not have the milestone "(02) Portfolio Validation".

 

I would like to get a sum with the value of Net Sales from the projects that went through all the milestones, except the 11th. But how to differentiate between projects that go through the 10 milestones and projects that do not go through the 10 milestones?

 

In the example, I should get 230 as result.

 

https://drive.google.com/file/d/1hySgTKXn5rGervBa0CX1pHv-USW6aI7o/view?usp=sharing

 

Best Regards,

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try these measures:

Check = IF(MAX('Table 2'[Milestone Name])="(11) Launch",1,IF(CALCULATE(MAX('Table 2'[%]),FILTER('Table 2','Table 2'[Milestone Name]<>"(11) Launch"&&'Table 2'[Project #] in FILTERS('Table 2'[Project #])))=1,1,0))
Check-2 = IF(CALCULATE(COUNTROWS('Table 2'),FILTER(ALLSELECTED('Table 2'),[Check]=1&&'Table 2'[Project #] in DISTINCT('Table 2'[Project #])))=COUNTROWS('Table 2'),1,0)
Result = SUMX(DISTINCT('Table 1'[#]),CALCULATE(SUM('Table 1'[Net Sales])*[Check-2]))

Choose the [Result] measureas a Card visual, it shows:

1.PNG

Here is my changed pbix file:

pbix 

 

Best Regards,

Giotto Zhi

 

View solution in original post

5 REPLIES 5
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description and sample data, i think the result in this sample should be 270 not 230.

Because ID as 1,4,5 meets the logic and their total is 270.

If so, please try these two measures:

check = IF(CALCULATE(COUNT('Table 2'[Milestone Name]),FILTER('Table 2','Table 2'[Project #] in FILTERS('Table 2'[Project #])&&'Table 2'[Milestone Name]<>"(11) Launch"))=10,1,0)
Total = CALCULATE(SUM('Table 1'[Net Sales]),FILTER('Table 1','Table 1'[#] in FILTERS('Table 1'[#])&&[check]=1))

Choose [Total] measure as a Card visual, the result shows:

22.PNG

If i misunderstand your logic, please give more info about how did you calculate the result as 230?

Expect your reply!

 

Best Regards,

Giotto Zhi

Hello @v-gizhi-msft 

 

I will try to explain better.

 

To compute this "Conversion Rate", the projects must have all milestones from 01 to 10 in 100%.

 

Project1 has all milestones (between 01 and 10) at 100% - SUM

 

Project2 has all milestones (between 01 and 09) at 100% - SUM

  • Project2 doesn’t have the milestone 10, because it’s not necessary

 

Project3 has all milestones (between 01 and 10) at 100% - SUM

  • Project3 doesn’t have the milestone 02, because it’s not necessary

 

Project4 is still in milestone 07 - NO SUM

 

Project5 is still in milestone 01 - NO SUM

 

To make this calculation, I was using this formula.

 

final Sales = if( sumx(filter(milestones,milestones[Project ID] =projects[Project ID] && milestones[Milestone Name] in {" Milestone01","Milestone02","Milestone03","Milestone04","Milestone05","Milestone06","Milestone07","Milestone08","Milestone09","Milestone10"} ),milestones[%]) =1000, [Net Sales],0)

 

 

But it depends on the projects having ALL the milestones: S

 

Best Regards

v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try these measures:

Check = IF(MAX('Table 2'[Milestone Name])="(11) Launch",1,IF(CALCULATE(MAX('Table 2'[%]),FILTER('Table 2','Table 2'[Milestone Name]<>"(11) Launch"&&'Table 2'[Project #] in FILTERS('Table 2'[Project #])))=1,1,0))
Check-2 = IF(CALCULATE(COUNTROWS('Table 2'),FILTER(ALLSELECTED('Table 2'),[Check]=1&&'Table 2'[Project #] in DISTINCT('Table 2'[Project #])))=COUNTROWS('Table 2'),1,0)
Result = SUMX(DISTINCT('Table 1'[#]),CALCULATE(SUM('Table 1'[Net Sales])*[Check-2]))

Choose the [Result] measureas a Card visual, it shows:

1.PNG

Here is my changed pbix file:

pbix 

 

Best Regards,

Giotto Zhi

 

Thank you very much @v-gizhi-msft 

 

It worked perfectly.


But could you explain the logic behind the code to me?

 

Best Regards,

v-gizhi-msft
Community Support
Community Support

Hi,

 

The first [check] measure is to show the progress(Whether experiencing all milestones) for each project.

The second [check-2] measure is to find the project which experiencing all milestones.

The [result] measure is to find the corresponding value by [check-2] to sum the final result.

 

Best Regards,

Giotto Zhi

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors