Getting around a necessary circular reference

[jolee217]

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.

 

[jolee217]

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.

 

[jolee217]

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.

 

[horseyride]

If I follow, this should work in PowerQuery

Query:Query1

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

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

(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

 

[horseyride]

More generically, if you plan to have different vendor names rather than just "Vendor" then this version would be what you needed

Query1

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

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

(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

(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