Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mohammedald
Helper I
Helper I

Need your help in ratio calculation

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

Screenshot 2023-03-05 190308.jpg

 

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 )))

Screenshot 2023-03-05 190548.jpg

 

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 ))

Screenshot 2023-03-05 190841.jpg

 

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 * 

Screenshot 2023-03-05 192149.jpg

 

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

1 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"))))

View solution in original post

7 REPLIES 7
Padycosmos
Solution Sage
Solution Sage

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")))

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."

Please paste the formula that shows the error message

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"))))

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"))))

Thank you, Pady again 

I just wanted to remove the =>2023 clause 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors