• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

ABC XYZ analyze|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / ABC XYZ analyze|Power Pivot|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotABC XYZ analyze
sp_PrintTopic sp_TopicIcon
ABC XYZ analyze
Avatar
Johan Vandeweijer
Member
Members
Level 0
Forum Posts: 15
Member Since:
March 3, 2021
sp_UserOfflineSmall Offline
1
March 3, 2021 - 10:00 pm
sp_Permalink sp_Print

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

 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 4, 2021 - 5:12 pm
sp_Permalink sp_Print

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

Avatar
Johan Vandeweijer
Member
Members
Level 0
Forum Posts: 15
Member Since:
March 3, 2021
sp_UserOfflineSmall Offline
3
March 5, 2021 - 4:43 am
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1529
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
March 5, 2021 - 3:49 pm
sp_Permalink sp_Print

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

Avatar
Johan Vandeweijer
Member
Members
Level 0
Forum Posts: 15
Member Since:
March 3, 2021
sp_UserOfflineSmall Offline
5
March 5, 2021 - 5:41 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Riny van Eekelen, Kumud Patel, Bright Asamoah
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Forum Stats:
Groups: 3
Forums: 24
Topics: 6360
Posts: 27812

 

Member Stats:
Guest Posters: 49
Members: 32340
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.