Sum depending on % of milestones

[Anonymous]

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,

[v-gizhi-msft]

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:

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

[Anonymous]

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 · Accepted solution]

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:

Here is my changed pbix file:

pbix 

Best Regards,

Giotto Zhi

[Anonymous]

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]

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