Column that compares a row value with an absolute value based on another column

[markyochoa]

This probably isn't too complicated but I'm not sure how to do this. Here are the relevant measures/columns/tables:

Example Table1:

DealNumber	Owner	DaysOpen
123	Mark	35
124	John	40
125	Mark	50
126	John	125

Measures (already created):

AbsoluteAVGD2C = Gives me the absolute value of the Owner's average deal days2close. It ignores any date filters.

I want to create a column that will compare the DaysOpen column against that Owner's AbsoluteAVGD2C value so I can see if the deal has been open too long based on the person.

So for instance, given Mark and John's ABS AVG of 90 and 120 respectively, I should get this:

Example Table2:

DealNumber	Owner	DaysOpen	Status
123	Mark	35	Good
124	John	40	Good
125	Mark	50	Good
126	John	125	TooLong

The general logic is probably something like IF(DaysOpen < AbsoluteAVGD2C, "Good", "TooLong"), but I can't figure out how to make sure the AbsoluteAVGD2C measure in this foruma is specific to the owner. 

[v-yanjiang-msft]

Hi @markyochoa ,

Has your problem been solved by @rohit_singh 's solution? If so, would you mind accept his reply as solution?

If not, please feel free to let me your needs.

Best Regards,
Community Support Team _ kalyj

[rohit_singh · Accepted solution]

Hi @markyochoa ,

Assuming you have already created the measure to calculate average, please try this :

Status =

var _avg = [AbsoluteAVGD2C]

RETURN

IF(SUM(AvgDaysOpen[DaysOpen]) < _avg, "Good", "TooLong")

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[markyochoa]

Thanks! Sorry for the very late reply.