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

  • 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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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