Identfying a Column Value to a related Value in same Column

[adcook]

All,

I have been struggling with how to best evaluate something in my organization. Currently when we sell to a customer, we may ship to multiple sub customers as part of the master purchase order. So in PowerBI I will have multiple PONumbers for multiple customers, but they are still part of one master PO. For example, we may have master PO#001 for the master customer, but then we put a letter after the sub orders like PO#001A, PO#001B and PO#001C for the subcustomers. I want a way to identify all the times in which an order has a master and sub POs attached. The kicker is that I also want to only identify it as being a part of a master PO if there are multiple customers in the PO. Sometimes we use that logic for delayed shipping to one vendor.

The way I worked it so far is that I first created a new calculated column off of PO that removed the far right character if it was not a master PO.

PONumberBase = if(ISBLANK([MasterPONumber]),iferror(Left([PONumber],LEN([PONumber])-1),""),[PONumber]

I then used the following formula in my table to create a calculated column that would get at I want, but it is telling me that I have a circular dependency.

MasterPOCheck= if(CALCULATE(DISTINCTCOUNT([CustomerInternalID]),Revenue[PONumberBase]=Earlier([PONumberBase]))>1,"Master PO","Single PO")

I may have explained this poorly, but any ideas on what I could do differantly?

Thanks!

Alex

[Eric_Zhang]

---

@adcook wrote:

All,

I have been struggling with how to best evaluate something in my organization. Currently when we sell to a customer, we may ship to multiple sub customers as part of the master purchase order. So in PowerBI I will have multiple PONumbers for multiple customers, but they are still part of one master PO. For example, we may have master PO#001 for the master customer, but then we put a letter after the sub orders like PO#001A, PO#001B and PO#001C for the subcustomers. I want a way to identify all the times in which an order has a master and sub POs attached. The kicker is that I also want to only identify it as being a part of a master PO if there are multiple customers in the PO. Sometimes we use that logic for delayed shipping to one vendor.

The way I worked it so far is that I first created a new calculated column off of PO that removed the far right character if it was not a master PO.

PONumberBase = if(ISBLANK([MasterPONumber]),iferror(Left([PONumber],LEN([PONumber])-1),""),[PONumber]

I then used the following formula in my table to create a calculated column that would get at I want, but it is telling me that I have a circular dependency.

MasterPOCheck= if(CALCULATE(DISTINCTCOUNT([CustomerInternalID]),Revenue[PONumberBase]=Earlier([PONumberBase]))>1,"Master PO","Single PO")

I may have explained this poorly, but any ideas on what I could do differantly?

Thanks!

Alex

---

@adcook

It sounds that you‘ll have to re-model your data. To have a better understanding on your idea, could you post some sample data? Even better you can share a pbix file with sample data.

[adcook · Accepted solution]

Thanks @Eric_Zhang. I actually was able to figure it out. I did the following adjustment to my second formula. 

MasterPOCheck= if(CALCULATE(DISTINCTCOUNT([CustomerInternalID]),filter(Revenue,[PONumberBase]=Earlier([PONumberBase]))>1,"Master PO","Single PO")

Thanks again.

Alex