Need your help in ratio calculation

[mohammedald]

Hello how are you guys I hope you are doing well, this might seem similar to another post I made and yes you are right the thing is I missed something important and need your help again, I have a rather complicated measurement I want to make ( complicated  for me ) I have a Sales data set from a company and I want to make a win Ratio calculation before I show you the calculation I want to show you the dataset in excel just so that you understand the calculation

and just to clarify if the Deal Status is "Lost" it will be put in the Expected Price not the Final price this is what I didn't mention last time 

now for the calculation, it's 2 calculations the first one is basically 

( win value[ Final Price ] + lost value[ Expected Price ] ) = ( total ) --> ( win value / total ) = dollar winning ratio

here is an example ((( in the green )))

the second calculation is the 

( count of won deals + coun of lost deals ) = ( total ) --> ( count of won / total ) = Quantity winning ratio 

here is an example ((( in the green ))

Now my problem rises when I want to make this calculation automated in Power Bi or Excel I don't know how I can tell the DAX language to make a measurement where he takes the lost deals and count them + take the won deals and count them and then dived the count of the won deals by the total, and in the Value Calculation taking the total Expected Price for the lost deals only and then taking the Final Price for the won deals and then dividing the total of them 

((( here is a google drive link with a 2 min video explaining it if you didn't understand what I mean )))

https://drive.google.com/file/d/1KyQhvvJ9YuEf1JdwYTrBIgXdsfAvTOcb/view?usp=sharing

I will show you a picture of how I want it to be in the dashboard *Note that I do this manually every time there is a new deal in won or lost * 

I tried my best to solve it on my own and looked for a solution everywhere but the problem is none of the solutions have the same variables as mine ( multiple Deal Status, Count of the won deals and the lost deals, the value of won in Final Price and lost in Expected Price ) 

I know this is a lot but I will appreciate your help thank you in advance. 

here is the link for the Power Bi in Google Drive

https://drive.google.com/file/d/1Fo-36yiNSTEo9DjXElMztm_YrOV53SjY/view?usp=sharing

[Padycosmos]

The following work:

Winning Dollar Ratio = DIVIDE(CALCULATE(SUM('Test'[Final price]),Test[Deal Status]="Won" && YEAR(Test[Est Close Date])>=2023), CALCULATE(SUM('Test'[Final price])+SUM('Test'[Expected Price]),YEAR(Test[Est Close Date])>=2023 && (Test[Deal Status]="Won"|| Test[Deal Status]="Lost")))

Winning Quantity Ratio = DIVIDE(CALCULATE(COUNT(Test[Deal Status]),Test[Deal Status]="Won" && YEAR(Test[Est Close Date])>=2023), CALCULATE(COUNT(Test[Deal Status]),YEAR(Test[Est Close Date])>=2023 && (Test[Deal Status]="Won"|| Test[Deal Status]="Lost")))

Winning Quantity Ratio = DIVIDE(CALCULATE(COUNT(Test[Deal Status]),Test[Deal Status]="Won" && YEAR(Test[Est Close Date])>=2023), CALCULATE(COUNT(Test[Deal Status]),YEAR(Test[Est Close Date])>=2023 && (Test[Deal Status]="Won"|| Test[Deal Status]="Lost")))

[mohammedald]

Pady thank you so much you really are a lifesaver, could I just ask you for one more thing could you remove the Date filter I tried to do it myself but this message appears "Too few arguments were passed to the DIVIDE function. The minimum argument count for the function is 2."

[Padycosmos]

Please paste the formula that shows the error message

[mohammedald]

the names are changed because I used them in another data set 

Winning Quantity Ratio = DIVIDE(CALCULATE(COUNT('Main Data'[Deal Stage]),'Main Data'[Deal Stage]="Won", CALCULATE(COUNT('Main Data'[Deal Stage]),('Main Data'[Deal Stage]="Won"|| 'Main Data'[Deal Stage]="Lost")))) 

Winning Dollar Ratio = DIVIDE(CALCULATE(SUM('Main Data'[Final PO. Value]),'Main Data'[Deal Stage]="Won", CALCULATE(SUM('Main Data'[Final PO. Value])+SUM('Main Data'[Customer Expected Budget]),('Main Data'[Deal Stage]="Won"|| 'Main Data'[Deal Stage]="Lost"))))

[Padycosmos · Accepted solution]

A ')' was omitted at the end of the First CALCULATE(). I have included it below:

Winning Quantity Ratio = DIVIDE(CALCULATE(COUNT('Main Data'[Deal Stage]),'Main Data'[Deal Stage]="Won"), CALCULATE(COUNT('Main Data'[Deal Stage]),('Main Data'[Deal Stage]="Won"|| 'Main Data'[Deal Stage]="Lost")))) 

Winning Dollar Ratio = DIVIDE(CALCULATE(SUM('Main Data'[Final PO. Value]),'Main Data'[Deal Stage]="Won"), CALCULATE(SUM('Main Data'[Final PO. Value])+SUM('Main Data'[Customer Expected Budget]),('Main Data'[Deal Stage]="Won"|| 'Main Data'[Deal Stage]="Lost"))))

[mohammedald]

Thank you, Pady again 

[mohammedald]

I just wanted to remove the =>2023 clause