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

 

View solution in original post

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors