cancel
Showing results 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

Helper I

## Win Rate

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?

6 REPLIES 6
Community Support

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.

Super User

@Hadill , where are these values

won deals value/ won deals value+ lost deals

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

Helper I

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

Super User

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

Helper I

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.

Super User

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

Or use winrate in grand total using isinscope

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors