Forum Discussion

Hadill's avatar
Hadill
Helper I
4 years ago
Solved

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.

DAX Calculation
dax tip
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

     

    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)

    Then use this table to create matrix.

     

    Best Regards,

    Jay

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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/

    • Hadill's avatar
      Hadill
      Helper I
      4 years ago

      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

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

     

    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)

    Then use this table to create matrix.

     

    Best Regards,

    Jay

    • Hadill's avatar
      Hadill
      Helper I
      4 years ago

      Hi Anonymous ,

       

      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:

       

       

      Please note that I copied the same function as yours.

       

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

       

      • Hadill's avatar
        Hadill
        Helper I
        4 years ago

        Hi Anonymous !

         

        Did you get any solutin for my problem?

         

        Thanks a lot!

  • Hadill's avatar
    Hadill
    Helper I
    3 years ago

    Hi Anonymous ,

     

    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.