Log In
Register
Home
March 3, 2021
Offline
Dear,
I created an ABC XZY analyzes, which is great when you work only with one production plant. But it's not working when you have more production plants.
I created one example. In the real example, I have 6 plants, which I'm downloading as map. In this example I put two plants in as sheet.
What I did first, was to sort the value from lowest to highest in power query. Then I added an index table.
This I loaded to the data model.
In the data model, I added a column: Cummulative Value =sumx(filter(Append1;Append1[Index.1]<=Earlier(Append1[Index.1]));[Total Value])
Then I added a second column: Cum%=append1[Cummulative Value]/sum(append1[Total Value])
Then I added a third column: ABC = SWITCH(TRUE(); Append1[Cum%]<=0,7;"A";Append1[Cum%]<=0,9;"B";"C")
And a fourth column: XYZ =IF(Append1[Rnge of coverage qty]>=0 && Append1[Rnge of coverage qty]<11; "X"; IF(Append1[Rnge of coverage qty]>=11 &&Append1[Rnge of coverage qty]<21;"Y"; "Z"))
Then I loaded it to a pivot table.
It's working fine, if I don't take care off the plant. But I want to have the ABC is working per plant, and not overall.
Can somebody tells me how I need to do this? In case I also want to do this per material controller, can this be done as well?
Thanks,
Johan
July 16, 2010
Offline
Hi Johan,
I'm a bit fuzzy on what you're actually trying to do. The classification of the data into XYZ and ABC should be done in Power Query. I think this Grouped Running Total in Power Query tutorial will point you in the right direction.
Mynda
March 3, 2021
Offline
Hey Mynda, thanks for your quick answer.
Indeed I need the running total per plant. So I should use the grouped running total.
I try to included this in my example, but I get some errors.
I added the file again, maybe you can have a quick look and tell me what I did wrong?
I hope when I fix this, that I can do the ABC on each plant.
That each plant are showing A = 70 %, B= 20 % and C= 10 %. On this way I know the parts which we have to handle to drop the stock value.
Kind regards,
Johan
October 5, 2010
Offline
Hi Johan,
The Grouped Running Total function requires 2 parameters but you were only supplying one. You need to supply the list of values to sum and the list by which you are doing the grouping, in this case the Plant column.
BufferedValues = List.Buffer(#"Sorted Rows"[Total Value]),
BufferedGroup = List.Buffer(#"Sorted Rows"[Plant]),
GRT = Table.FromList(fxGroupedRunningTotal(BufferedValues,BufferedGroup),Splitter.SplitByNothing(), {"GRT"}),
Before calling the function I made sure to sort by Plant and then by Total Value.
You can also do a Grouped RT by MRP Controller. I assumed you'd want this sorted by Plant and then by MRP Controller?
Check the attached file for working results for both scenarios. The queries are still loading to the Data Model but I'm not sure you actually need that?
Regards
Phil
March 3, 2021
Offline
Thanks a lot Philip, this is already helping a lot. And to be honest, it's complicated, but it's working :-).
Is it also possible to show a column with the max per plant our material controller? Or is it better to do this in Power Pivot?
Because the max off running total per plant or material controller, I will use to calculate Cum %.
Cum% = "Total value" (per "Material") / Max "GroupedRunningTotal"
And then I will use this to calculate the ABC per plant or material controller:
ABC = if (cum% < 0,07; "A"; if (cum% > 0,09; "B"; "C")).
My original file has 6 plants and 30.300 lines.
I use this ABC XYZ analyzes very often. In this case I can concentrate only on the parts which gives me the biggest benifit.
Again thanks again for your big support.
1 Guest(s)
