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

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