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 ...
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

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