cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hadill
Helper I
Helper I

Win Rate

Hi there!

In my data query I have a table with the 'Sales Information', includes the following columns:

 

Deal value

Deal status

500

Booked

1000

Abounded

400

Booked

1500

Lost

2000

Lost

1500

Abounded

4000

signed

 

In the report view, I already created a table that includes the sum of the values for ‘Deal Status’ by every quarter…

but I still want to add the win rate to the table for every quarter exactly as follow:

 

 

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%

 

Win rate calculation = 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].

 

What do you think the best way to apply this?

 

I hope it is easy for you to help.

Thanks in advance.

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Hadill ,

 

Create a calculated column like below.

rate = 
var _win = CALCULATE(SUM('Table'[Deal value]),FILTER(ALLEXCEPT('Table','Table'[quarter]),'Table'[Deal status]="Booked"))
var _total = CALCULATE(SUM('Table'[Deal value]),FILTER(ALLEXCEPT('Table','Table'[quarter]),'Table'[Deal status]="Booked"||'Table'[Deal status]="Lost"))
return
_win/_total

 

vjaywmsft_0-1649181520138.png

Then create a new table:

Table 2 = 
var _table1 = SELECTCOLUMNS('Table',"deal status",'Table'[Deal status],"deal value",'Table'[Deal value],"quarter",[quarter])
var _table2 = distinct(SELECTCOLUMNS('Table',"deal status","win_rate","deal value",'Table'[rate],"quarter",[quarter]))
return
UNION(_table1,_table2)

vjaywmsft_1-1649181555605.png

Then use this table to create matrix.

vjaywmsft_2-1649181659984.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

8 REPLIES 8
Hadill
Helper I
Helper I

Hi @v-jayw-msft ,

 

i wolud like to thank you for answering my query perfictly, 

 

But I need some additional help over here. 

 

I added an additional colomn to the final table, just to link it with the fact table. 

 

But i am getting the following error:

 

A circular dependency was detected.

have you ever experinced this kind of issues?

do you have any idea how can i solve it?

 

Thanks in advance.

v-jayw-msft
Community Support
Community Support

Hi @Hadill ,

 

Create a calculated column like below.

rate = 
var _win = CALCULATE(SUM('Table'[Deal value]),FILTER(ALLEXCEPT('Table','Table'[quarter]),'Table'[Deal status]="Booked"))
var _total = CALCULATE(SUM('Table'[Deal value]),FILTER(ALLEXCEPT('Table','Table'[quarter]),'Table'[Deal status]="Booked"||'Table'[Deal status]="Lost"))
return
_win/_total

 

vjaywmsft_0-1649181520138.png

Then create a new table:

Table 2 = 
var _table1 = SELECTCOLUMNS('Table',"deal status",'Table'[Deal status],"deal value",'Table'[Deal value],"quarter",[quarter])
var _table2 = distinct(SELECTCOLUMNS('Table',"deal status","win_rate","deal value",'Table'[rate],"quarter",[quarter]))
return
UNION(_table1,_table2)

vjaywmsft_1-1649181555605.png

Then use this table to create matrix.

vjaywmsft_2-1649181659984.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi @v-jayw-msft ,

 

Thanks a lot for your reply, it seems very close !

 

The first table created perfectly,  but when I come to the second table, it brings Error data with the folloing message:

 

Hadill_0-1650280611609.png

Hadill_1-1650280920087.png

 

Please note that I copied the same function as yours.

 

thanks again, and I look forward to receiving your reply.

 

Hi @v-jayw-msft !

 

Did you get any solutin for my problem?

 

Thanks a lot!

Hi @Hadill ,

 

Please split the second table into two tables.
table1 = SELECTCOLUMNS(...
table2 = distinct(...
Then check which table occurred the error.

It looks like there are some differences between your raw data and the sample data.

I didn't get this error when using the data you provided above so that I could not reproduce the scenario.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@Hadill , You need have measure for booked , lost , others and win %

Win Ratio =

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

 

You can use Show on row in  matrix visual

 

 

Or you can show win % in Grand Total

 

If (isinscope(Table[Status]) ,Sum(Table[Deal Amount] ) , [Win Ratio])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

@amitchandak 


I think the second option cannot be applied in my matrix case.


the first solution is applicable, but unfortunately it brings the grand total value for all the years and quarters.


I need to show the data with quarter regards.


I hope you can help in this

@Hadill , In the first option you have all the measures and those should follow the filter on columns and in slicer.

You will have total on column

 

In second case you will replace the total column

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors