Win Rate

[Hadill]

Hi there!

I have a table with the 'Sales Information', includes the follwing columns:

[Deal Status] -> Active, Signed, Abondend, Booked, Lost.

[Deal value] -> Numeric Data.

I want to add a new column [Win Rate] applying the logic of: Win Rate = won deals value/ won deals value+ lost deals value

Which function should I use for this and how should I write it?

Thanks a lot in advance

[v-angzheng-msft]

Hi, @Hadill 

Please consider sharing sample data with date column and date by quarter column and desired output.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Hadill , where are these values

won deals value/ won deals value+ lost deals

Can you share sample data and sample output in table format?

[Hadill]

Hi @amitchandak , thanks for replying.

By Won Deals value i mean the sum of the deal values that corresponds Booked status.

And the same for Lost Deals value, corresponds Lost status.

So it should be : win rate= sum of [deal value] for the booked [deal status]/sum of [deal value] for the booked [deal status]+sum of [deal value] for the Lost [deal status].

Example of the columns: 

Deal value	Deal status
500	Booked
1000	Abounded
400	Booked
1500	Lost
2000	Lost
1500	Abounded
4000	signed

I hope it is more clear now

[amitchandak]

@Hadill , Try a measure like

Divide( Calculate(Sum(Table[Deal Amount] ), Filter(Table, Table[Status] ="Booked")) ,Calculate(Sum(Table[Deal Amount] ), Filter(Table, Table[Status] in{"Booked","Lost"} )))

[Hadill]

Great!  it looks better now.

But I missed to mention that every deal has a specific date, and I need the data to be appered on the report by quarter!

The following example is exsactly what I want to show in my report:

	Q1	Q2	Q3	Q4
Booked	10	12	11	14
Signed	1	2	3	4
Lost	14	15	16	18
Abounded	1	2	3	4
Win Rate	42%	44%	41%	44%

It is like a summary for the current sales situation and alongside the win rate, all in one matrix.

*Please note that my table already includes a column called [Date by Quater]

Thanks a lot for your helping.

[amitchandak]

@Hadill , Create those as measure and show on row like Booked, lost and Win % etc

Or use winrate in grand total using isinscope