• 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
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Running Total different criteria|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Running Total different criteria|Power Query|Excel Forum|My Online Training Hub
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 QueryRunning Total different criteria
sp_PrintTopic sp_TopicIcon
Running Total different criteria
Avatar
Johan Vandeweijer
Member
Members
Level 0
Forum Posts: 15
Member Since:
March 3, 2021
sp_UserOfflineSmall Offline
1
August 12, 2022 - 11:09 pm
sp_Permalink sp_Print

I wanted to get the running total per material, but it also has to consider the release number.

For example, I have a material number M0171L010, different release numbers 267, 268, 269, etc.

Like below I would like to see it, but don't know what the reason is that I don't get it. I added a file as example.

Rel nr   Cum.RecQty                           wk27      wk28        wk 29      wk30      wk31         wk32          ....            wk38        wk39  

267      549932          Scheduled qty    4200       4060        6720        0            3500         0                0               9380         1400   

                                Running total    554132    558192    564912     564912   568412      568412       568412      577792      579192      

268      570092          Scheduld qty      0             0              0             0            0               0                0              6720         1400

                                Running total    570092    570092    570092     570092   570092      570092       570092      576812      578212  

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
2
August 13, 2022 - 2:02 am
sp_Permalink sp_Print

Hi Johan,

You pasted several lines of text. Difficult to "translate" this into useful data. Can you upload a file? Press "Attachments" below the box where you enter your response and then select a file and press "Start upload".

Riny

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 205
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
3
August 13, 2022 - 2:22 am
sp_Permalink sp_Print

Hi Johan!

While waiting for your example file, here is an article on how to do running totals in PQ. 

Avatar
Johan Vandeweijer
Member
Members
Level 0
Forum Posts: 15
Member Since:
March 3, 2021
sp_UserOfflineSmall Offline
4
August 13, 2022 - 3:17 am
sp_Permalink sp_Print

Hey Riny,

Sorry, I though that I added the file.

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
5
August 13, 2022 - 5:45 pm
sp_Permalink sp_Print

Thanks for the file. It took a while until I figured out what you were trying to do and  concluded you needed to build the pivot table with the second waterfall report as a basis. Needed to add a unique key to both waterfalls so that I could establish a relationship between the two. Started to tinker with your pivot table but screwed it up. Sorry! See if the pt below "yours" achieves what you need. I.e. the slicercs and the pt starting in row 28. If not, come back.

Riny

Avatar
Johan Vandeweijer
Member
Members
Level 0
Forum Posts: 15
Member Since:
March 3, 2021
sp_UserOfflineSmall Offline
6
August 15, 2022 - 4:20 pm
sp_Permalink sp_Print

Hello Riny, thanks, this is indeed what I want. It's to compare the releases from the customer.

But it's strange, now you added the Key, that my pivot table isn't given the same result as you.

Do I need to create maybe a table of material number and a table of release number, and link it to both files?

Because I saw that you used the material number and release number now from waterfallReport2?

And or do I need to create as well a table of the "KEY" as well and link to both files? Will this be a proper way to work, or is this not needed?

Now I had only 3 examples in the file, but I will have 50, or even more then 100 materials in the file. 

But not all will have for example release number 270, for some they can start with release number 1 for release date July 27th.

Again, thanks a lot for your support

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
7
August 15, 2022 - 5:48 pm
sp_Permalink sp_Print

Hi Johan,

Difficult to judge what needs to be done and what not. I took your data and guessed that my pivot table was what you wanted. I couldn't arrive to it other than by creating unique material keys and genera. If it works for three examples, it should work for 50 as well. but, I'm not sure that I totally understand what you are doing.

In general terms you need to separate transactional data (fact tables) from "general" data (dimension tables). So, perhaps yes it's best to create a table for all unique Materials codes with their characteristics. And a table for all Release numbers. together with the Date table, these will be on the 1-side of the relationships with the fact table "Waterfall", which will be on the many-side of the relationship.

Then, in the pivot table all row and column fields come from the dimension tables and all values from the fact table(s).

Redone the file as I noticed that I had violated these rules myself. But again, not sure if this will work for you on a larger scale. Try and let me know if it doesn't and why you think so.

 

Riny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
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.