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
T-Shirt Design Challenge 2023

Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors