cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
8 REPLIES 8
Helper I

Hi @v-jayw-msft ,

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?

Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Helper I

Hi @v-jayw-msft ,

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.

Helper I

Hi @v-jayw-msft !

Did you get any solutin for my problem?

Thanks a lot!

Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

@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/

Helper I

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

Super User

@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

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors