Dynamics Win Rate Measure

[irenelitw629]

Hi All,

I want to calculate win rate from two tables - 1) Sales table and 2) Pipeline table.

The win rate calculation formula= Sales amount / Original Pipeline.

In each table I have Dynamic measure to classify whether an account is large deal or small deal - the classification will change based on the product selected.

Pipeline Table

Account	Dynamic Pipe NNACV	Dynamic Pipe SD/LD
City	180,363	Small deal
Coia	3,017,522	Large Deal
iref	1,960,052	Large Deal

Sales Table

Account	Dynamic Sales NNACV	Dynamic Sales SD/LD
Coia	1,100,316	Large Deal
iref	1,138,494	Large Deal
City	103,765	Small deal

Now the question is how to calculate large deal/ small deal win rate in DAX measure?

In above example,

Large deal win rate should be= (1.1m+1.13m)/ (3.01m+1.96m)=~44.9%

Run Rate win rate should be=103k/180k=~57%

The win rate should also change when product filter applied.

Here is sample file. It might be easier to see sample data. Thanks for your advice in advance.

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

Irene

[irenelitw629]

Thank you Amit! You are the best!

[amitchandak · Accepted solution]

@irenelitw629 , You need to have common dimensions like Account, Dynamic Pipe SD/LD, and date and analyze data with those

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw