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

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.