Forum Discussion
Sum by Column while ignoring a Relationship
At the time of Sales the Mgr of Record is captured and in the future that Collection should be reported against that Mgr of Record, even if the Rep of Record transfers to another Mgr.
I can get the answer in a separate table (a sales only table using MgrTable.MgrName and a collections only table using CollectionsTable.MgrofRecord), however, because of the relationship with RepTable.CurrentMgr-to-MgrTable.MgrName I can't seem to get them on the same data table.
I've attempted measures and columns under both CollectionsTable and MgrTable.
After searching and attempting many of the solutions for ignoring relationships, or using lookupvalue... I am now asking the community.
I'm now prepared to say, "doh!" - Thank you in advance.
Thank you. I ended up finding this BI Consulting Pro video (https://www.youtube.com/watch?v=PzqML-U-lnY) and figured out a solution:
1) Created an inactive relationship between MgrTable.MgrName and CollectionsTable.MgrOfRecord
2) Then using USERELATIONSHIP
CollNet2CompedMgr = CALCULATE(sum(CollectionsTable[Coll (Net)]),USERELATIONSHIP(CollectionsTable[MgrOfRecord],MgrTable[MgrName]))Regards.
2 Replies
I would create a reference table with the transactionID and the Manager of record, and then add that table to your data model. Technically this would be the same as using "Manager of Record" instead of "Current Manager" in your visual. Have you tried that?
Thank you. I ended up finding this BI Consulting Pro video (https://www.youtube.com/watch?v=PzqML-U-lnY) and figured out a solution:
1) Created an inactive relationship between MgrTable.MgrName and CollectionsTable.MgrOfRecord
2) Then using USERELATIONSHIP
CollNet2CompedMgr = CALCULATE(sum(CollectionsTable[Coll (Net)]),USERELATIONSHIP(CollectionsTable[MgrOfRecord],MgrTable[MgrName]))Regards.
