topic Re: Win Rate in DAX Commands and Tips

Win Rate

Hadill — Wed, 23 Mar 2022 08:51:21 GMT

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

Re: Win Rate

amitchandak — Wed, 23 Mar 2022 09:40:25 GMT

Hadill , where are these values

won deals value/ won deals value+ lost deals

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

Re: Win Rate

Hadill — Wed, 23 Mar 2022 12:00:04 GMT

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

Re: Win Rate

amitchandak — Thu, 24 Mar 2022 03:10:56 GMT

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

Re: Win Rate

Hadill — Thu, 24 Mar 2022 06:53:11 GMT

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.

Re: Win Rate

amitchandak — Mon, 28 Mar 2022 06:26:12 GMT

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

Or use winrate in grand total using isinscope

Re: Win Rate

v-angzheng-msft — Tue, 29 Mar 2022 03:06:17 GMT

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.

Sample (dummy dataset) data as text, use the table tool in the editing bar
Expected output from sample data
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.