Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Conversion Rate over Milestones

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:

IDProject NameNet Sales
1Project120
2Project280

 

And a table with milestones:

Project IDMilestone Name%
1Milestone1

100

1Milestone2

100

1Milestone3

0

2Milestone1

50

2Milestone225
2Milestone30

 

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

 

Need Help
Show and Tell

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.

     

  • dax's avatar
    dax
    Community Support
    6 years ago

    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.