Return Data in Same Table

Question

Hello,

I have a data table in powerpivot with doctor information and patient information which includes the patients referring doctor.

Cust Type	Customer ID	Customer Name	Doctor Typ	Referring Doctor ID	Referring Doctor Name	Referring Doctor ID
Doctor	00ABC Doctor	Dr John Smith	M.D.	N/A	N/A	=Doctor's Doc Type
Patient	00XYZ Patient	Mr. Patient	N/A	00ABC Doctor	Dr John Smith	Should = Referring Doc Type

Does any one know a simple formula that I could add as a calculated column to the referring doctor id field for patients?  This will allow me to roll up sales for a particular doctor type including their patients.

Thank you in advance for any help you could provide.

Answer 1

something like

=IF([Cust Type] = "Doctor", [Doctor Type], LOOKUPVALUE(MyTable[Doctor Type], MyTable[Customer ID], MyTable[Referring Doctor ID]))

so if the current row is of type Doctor, we can simply take the DoctorType of the current row
otherwises we have to lookup the DoctorType of the referring Docotor

hth,
gerhard

---

- www.pmOne.com -

Answer 2

Gerhard,

That worked perfectly!  Thank you very much.

Kevin