Getting around a necessary circular reference

jolee217

New Member
Joined
Jun 29, 2017
Messages
11
I attempting to use PowerPivot to calculate our group standard cost across a number of plants globally. The trick is that often we have intercompany (inter-plant) materials whose standard cost is that of the selling plant's. The calculation of this is causing some circular references, despite me being able to do this in excel with IF statements. The data is simply far too large to process in excel.

Here is a sample of my data and what I'm trying to do. The end goal is to fill in IntercoPrice, P1 (plant1), p2, and p3


[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Plant-Mat[/TD]
[TD]Plant [/TD]
[TD]Material [/TD]
[TD]Vendor [/TD]
[TD]StdCost [/TD]
[TD]Intercompany [/TD]
[TD]IntercoPrice [/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[/TR]
[TR]
[TD]P1-m123[/TD]
[TD]P1[/TD]
[TD]m123[/TD]
[TD]P2[/TD]
[TD]1.00[/TD]
[TD]Yes[/TD]
[TD]1.75[/TD]
[TD]1.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2-m123[/TD]
[TD]P2[/TD]
[TD]m123[/TD]
[TD]P4[/TD]
[TD]1.25[/TD]
[TD]Yes[/TD]
[TD]1.75[/TD]
[TD][/TD]
[TD]1.75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P3-m123[/TD]
[TD]P3[/TD]
[TD]m123[/TD]
[TD]Vendor[/TD]
[TD]1.50[/TD]
[TD]No[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]1.50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P4-m123[/TD]
[TD]P4[/TD]
[TD]m123[/TD]
[TD]Vendor[/TD]
[TD]1.75[/TD]
[TD]No[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.75[/TD]
[/TR]
</tbody>[/TABLE]


GIVEN: Plant, Material, Vendor, StdCost

CALCULATED: Plant-Mat (primary key, concatenation to create unique identifier)
Intercompany (looks for plant names in the "Vendor" column)
IntercoPrice (uses a series of if statements to determine the vending plant and refers to the relative column)
=IF([Intercompany]="Yes", LOOKUPVALUE(IF([Vendor]="P1",[P1], IF([Vendor]="P2",[P2], IF([Vendor]="P3",[P3]))), [Plant-Mat], [Vendor]&"-"&[Material],0)

LOOKUPS: P1, P2, P3, P4
This uses a lookup on the concatenation of Vendor&"-"&Material to run against the Plant-Mat column and return the IntercoPrice
=IF([Intercompany]="Yes", LOOKUPVALUE([IntercoPrice], [Plant-Mat], [Vendor]&"-"&[Material]), [StdCost])

Issues:
1) The above creates a circular reference.
2) While I have included all these plants looking up an IntercoPrice when intercompany = yes, in reality I have several plants that do not follow this rule, hence the Interco price step. I need to be able to turn off and on the ability to lookup intercompany pricing. Removing this step would complicate things later on for me.
3) In the actual sheet there is 22 fields of given data, 23 plants, and 197,000 rows of data. I run through 2-10 conditions for each plant. The process for calculating intercompany pricing is the only one currently giving me a headache.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
REPHRASING THE ISSUES:

1) IntercoPrice formula creates an error - "Function LOOKUPVALUE expects a column reference as argument number 1" - in excel you would use INDIRECT, but that's not available.

2) Plant columns (P1...)
formula creates a circular reference - in excel the if's would filter out any issues, but in powerpivot it doesn't work that way.
 
Upvote 0
I was able to solve the first problem by adding an IF statement before the

IF([Vendor]="P1", LOOKUPVALUE([P1], [Plant-Mat Table], [Vendor]&"-"&[Material]), IF([Vendor]="P2".....

Now, only the following question remains:

2) Plant columns (P1...) formula creates a circular reference - in excel the if's would filter out any issues, but in powerpivot it doesn't work that way.
 
Upvote 0
If I follow, this should work in PowerQuery

Query:Query1
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Start = Table.TransformColumnTypes(Source,{{"Plant", type text}, {"Material", type text}, {"Vendor", type text}, {"StdCost", type number}})
in
    Start

Query:Query2
Code:
let
    Source = Query1,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each GetRelated([Vendor])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = "Vendor" then [Plant] else [Custom]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"Custom.1"},Query1,{"Plant"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"StdCost"}, {"StdCost.1"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Table1", "Plant", "Plant.1"),
    #"Sorted Rows" = Table.Sort(#"Duplicated Column",{{"Plant", Order.Ascending}}),
    #"Added Custom2" = Table.AddColumn(#"Sorted Rows", "IntercoPrice", each if [StdCost]=[StdCost.1] then 0 else [StdCost.1]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom2", List.Distinct(#"Added Custom2"[Plant]), "Plant.1", "StdCost.1", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom", "Custom.1"})
in
    #"Removed Columns"

Query: GetRelated
Code:
(ParameterName as text) =>
let
    Source = Table.FromList({[ Vendor = ParameterName]}, Record.FieldValues, {"Vendor"})  ,
    #"Merged Queries" = Table.NestedJoin(Source,{"Vendor"},Query1,{"Plant"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Vendor"}, {"Cycle"}),
Test = #"Expanded Table1"{0}[Cycle],
Test2 = if Test = "Vendor" then ParameterName else if Test = null then "Vendor" else Test,
Test3 = if (Test2 = "Vendor" or Test2 = ParameterName ) then Test2 else 
if @GetRelated(Test2) = "Vendor" then Test2 else @GetRelated(Test2)
in Test3

results will generate from Query2 based on excel Table1 which will be a table of columns [Plant, Material, Vendor, StdCost] with header row
 
Last edited:
Upvote 0
More generically, if you plan to have different vendor names rather than just "Vendor" then this version would be what you needed

Query1
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Start = Table.TransformColumnTypes(Source,{{"Plant", type text}, {"Material", type text}, {"Vendor", type text}, {"StdCost", type number}})
in  Start

Query 2
Code:
let
    Source = Query1,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each GetRelated([Vendor])),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom",{"Custom"},Query1,{"Vendor"},"Query1",JoinKind.LeftOuter),
    #"Expanded Query2" = Table.ExpandTableColumn(#"Merged Queries1", "Query1", {"StdCost"}, {"StdCost.1"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Query2", "Plant", "Plant.1"),
    #"Sorted Rows" = Table.Sort(#"Duplicated Column",{{"Plant", Order.Ascending}}),
    #"Added Custom2" = Table.AddColumn(#"Sorted Rows", "IntercoPrice", each if [StdCost]=[StdCost.1] then 0 else [StdCost.1]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom2", List.Distinct(#"Added Custom2"[Plant]), "Plant.1", "StdCost.1", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in  #"Removed Columns"

Function:IsPlant
Code:
(ParameterName as text) as logical =>
let
    Source = Query1 ,
    Test = Table.FromList({[ Vendor = ParameterName ]}, Record.FieldValues, {"Vendor"}),
    #"Merged Queries" = Table.NestedJoin(Test,{"Vendor"},Query1,{"Plant"},"Query1",JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Plant"}, {"Plant"}),
    Result = #"Expanded Query1"{0}[Plant],
    Result2 = if Result=null then false else true 
in Result2

Function GetRelated
Code:
(ParameterName as text) =>
let Source = Table.FromList({[ Vendor = ParameterName]}, Record.FieldValues, {"Vendor"})  ,
#"Merged Queries" = Table.NestedJoin(Source,{"Vendor"},Query1,{"Plant"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Vendor"}, {"Cycle"}),
Test = #"Expanded Table1"{0}[Cycle],
// Test2 = if Test=null then ParameterName else if IsPlant(Test) then GetRelated(Test) else Test
Test2 = if Test=null then ParameterName else if IsPlant(Test) then GetRelated(Test) else Test
in Test2
 
Upvote 0

Forum statistics

Threads
1,223,370
Messages
6,171,686
Members
452,418
Latest member
kennettz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top