Filter calculation by value in another column

[enorberg]

I have created a table that should look like this:

Sales rep	Deal closing rate
Person A	12%
Person B	10%
Person C	2%

I want to calculate the deal closing rate automatically, and filter by the sales rep.

The general closing rate calculation (not filter by a sales rep) looks like this:

DIVIDE

(CALCULATE(DISTINCTCOUNT(Company[companyid]),Company[Won deal]),

CALCULATE(DISTINCTCOUNT(Company[companyid]),Company[Created deal])

,0)

Essentially, it looks like how many unique companies have a won deal, and divides by the number of unique companies with a created deal.

Is it possible to automatically take the sales rep (e.g. Person A) and filter the calculation by this? 

[v-yingjl]

Hi @enorberg ,

What is your expected output? If you want to filter by person, just add a slicer in the report. Could you please consider sharing some sample data?

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[enorberg]

Hi @v-pn,

The expected output - as I tried and admittedly failed to demonstrate - is to display a deal closing rate based on that person's deals (companies).

DIVIDE

(CALCULATE(DISTINCTCOUNT(Company[companyid]),Company[Won deal]),

CALCULATE(DISTINCTCOUNT(Company[companyid]),Company[Created deal])

,0)

In the above calculation, it should be filtered by Company[Deal owner], so that it shows won deals, and created deals, where Person A is deal owner for example. Does that make more sense?

The filtering should be dynamic, e.g. it should read the name in the Sales rep column (for example: "Person A") and go, "right, Person A is the sales rep here, let' find all the companies where Person A is the deal owner, and figure out their closing rate".

[enorberg · Accepted solution]

I managed to solve this on my own - it was a relationshp problem. Once I had the correct relationship with the Comany table, the filtering happened on its own.