Conversion Rate over Milestones

[Anonymous]

Hello all!

I want to create a measure to get the conversion rate of my projectos.

For that, I have a table with my projects:

ID	Project Name	Net Sales
1	Project1	20
2	Project2	80

And a table with milestones:

Project ID	Milestone Name	%
1	Milestone1	100
1	Milestone2	100
1	Milestone3	0
2	Milestone1	50
2	Milestone2	25
2	Milestone3	0

My measure should get sum of Net Sales of every project that have Milestone1 and Milestone2 completed (100%). Basically, in this example I should get the value 20.

Any ideas on how to do this?

Best regards

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Try this calculated column formula

=if(Data[Project ID]=1,if(CALCULATE(SUM(Data[%]),FILTER(Data,Data[Project ID]=EARLIER(Data[Project ID])&&(Data[Milestone Name]="Milestone1"||Data[Milestone Name]="Milestone2")))=2,"Complete","Incomplete"),if(CALCULATE(SUM(Data[%]),FILTER(Data,Data[Project ID]=EARLIER(Data[Project ID])&&Data[Milestone Name]="Milestone1"))=1,"Complete","Incomplete"))

Hope this helps.

[amitchandak]

create a new column in the project table like

final Sales = if( sumx(filter(milestones,milestones[Project ID] =projects[Project ID] && milestones[Milestone Name] in {"	Milestone1","	Milestone2"} ),milestones[%]) =200, [Net Sales],0)

[Anonymous]

Thank for your help @amitchandak, I managed to make it work.

But, can I complicate it a little?

Project1 needs to have Milestone1 and Milestone2 100% to enter in the sum.

However, project2 only needs Milestone1 100% to enter in the sum.

How can I make this kind of differentiation?