Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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"))))
The following work:
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |