Power Query (Excel) + M Code | SUMIFS (Simple to Complex FIFO example)
HTML-код
- Опубликовано: 27 июл 2024
- This video will show you how to use Table.SelectRows() combination with other functions (equivalent to SUMIFS in excel). The example used in the video is FIFO inventory calculation, including simple and complex cases.
If you are looking for simple SUMIFS, grouped cumulative sum and more.
Example 1 and 2 use the same formula, but example 2 is applied in a complex case where additional functions are required.
Topic:
0:00 - Intro
Example 1: Simple Case
1:45 - Cell Reference Explained
2:16 - Table.SelectRows and use of custom function Explained
3:05 - List.Sum() + Table.SelectRows()
3:55 - List.Min
4:15 - Recursive function @
Example 2: Complex Case (Include cumulative sum)
5:21 - Example 2 explained
8:23 - Table.InsertRange()
9:30 - Table.Max()
12:19 - Wrap up
Workbook:
docs.google.com/spreadsheets/...
Great examples! Thank you for sharing!
I am Vietnamese, I love your content video
Thank you for your compliment.
Great examples! I think you should include more of these "life threatening" scenarios in your videos!! It is super useful!
Noted!! I'll do something like this as much as I can. This is thank to @Sumanth Sasidharan.
You are a genius 🙂 This is brilliant. Thank you for taking my suggestion and making this video. Hopefully people across in supply chain finance will find it useful. And with regards to feedback. Please zoom in, as it's hard to view sometimes.
Thank you for comment. I have noted down.
Amazing content...... Will be very useful for improving my Power Query and M coding skills. Thanks.
I'm glad you find it useful.
Thanks for this! It's very very useful! It's similar to SAP inventory audit.
I'm glad you find it useful 😊
Thank you for sharing 👌
Welcome 😊
Great video!
What if we add exit to outsourcing of a product? Which will be zero selling price. It will change cogs.
Thank you for your compliment. You can handle it as part of your input or change the calculation.
Tested your file. Works like a charm :)
If you follow presented way the formula written, you will find it everything is the same apart from the length of the formula (that's what make it scary). Please note that the provided solution is not sensitive to row order.
@@bsmart2gether408 yes that's really great. Being non row sensitive helps. I have another scenario.. which currently I am using DSUM function in excel. Not sure how to do in power query.
@Sumanth Sasidharan Both SUMIFS and DSUM are different in excel in the way of input the criteria. However, It is the same way in PQ (i.e. you use the operator (and / or) to specify the criteria) . What you can make it different is to create a criteria table in Excel then use the List.Accumulate function to multiple criteria. (i.e. Sum of multiple Product and each product have different criteria). I hope it helps :)
@@bsmart2gether408 Please make videos on list.accumulate and list.generate or nested let statement... Showcasing recursive features of PQ :)
@Sumanth Sasidharan Yes, I have plan to create a share all of my PQ knowledge through videos. Those you mentioned will be covered.
thanks again for posting, superb animation as usual
2 things if I may: to watch it you need a large screen and pause every few seconds just to read what it is displyed, transitions are probabbly too fast to follow, but it could be because I'm slow.
to my basic knowledge of PQ the equivalent of SUMIF could be explained with a less rich example? just saying
thanks again for your tuts, much appreciated
@Stefano Verugi,
Thank you so much for your feedback. I plan to do short and focus videos on formulas or techniques for the audience versus used cases. Used cases are good to demonstrate how multiple formulas apply to resolve the case where a short video will show specific examples and then link to use cases. I have noted down your feedback and greatly appreciated it. KT
Thanks for sample file workbook
easy to step by step follow youtube to be learn
thanks
Glad you n find it easy to follow 🙂
Hi, Also wanted to check. If this idea sounds workable. Group by inventory at commodity level with all rows. Create a function for the tables created by groupby. Function should create a Index column, running total(listfirstN). You can refer previous row using the index column. And get qty balance by subtracting running total-inventory balance. Will this work too ? As it might sound less complex and easy for the team to troubleshoot.
Inventory RT sold qty(16) - RT FIFO
5 5 11 5 take Inventory
6 11 5 6 take Inventory
10 21 -5 5 Inv +(soldqty-RT) (if negative)
That was what I did initially. Table.Group and custom function go within the functions. However, the complexity of formula is way beyond what presented in the video. GroupBy is definitely a good way to deal with it. By the way, we don't need to add index column if we have date.
This would work but considering the accounting cost recognition rule, it may be much complex than it is.
Could you pls share the Power Bi FIle Of this. what we need to do if purchase and Sale all are in One table with category Type?
Hi Praveen,
The solution designed in Excel. I'll move it to Power BI and share it whenever it is ready. You don't need to combine both in one table. If you are coming from using a slicer to switch them in Power BI, you can use field parameter to achieve it. It has better performance.
@@bsmart2gether408 Great thanks. actually am trying with your example.Unfortunelty didnt work out
Could you pls Help me the example of inventory aging in FIFO order and Cost is in Weighted Average cost. (All Data in one table and data source is cloud.Entry type differentiate purcahse+Positive Adjusmt and Sales and Negative ajdsmt)
aging slots are : 0-30DaysQty, Value, 31-60,Value...........
@@bsmart2gether408 In Slicer will put the Date . so taht we need to get all the aging slots and value in fifo order with weighted average cost
@@praveeng7066 Based on your descirption, my understanding is you want to calculate the balance stock aging and the balance stock value with the weighted cost method?The calculation is Avg.Purchase price x Quantity. Step one, maybe start be a custom to calculate a aging column (you can use IF statement with Date.Durations( Date.From(DateTime.LocalNow) - [Purchase Date] ) then do the calculation for weighted cost (Method should be similar and different is FIFO get the last one and Weighted get the average). The FIFO solutions is not sensitive to date, so you should not need to worry about the data order.
I hope the direction help.
@@bsmart2gether408 Thankyou so much for the response.Could you share ur mail id so that i can reponse in detail with sample data
in our data its from cloud and there is only posting date(without time) and entry type is there to filter purchase and sale. cost= total cost/total quantity (total of all the purchases) items should go from the inventory in fifo method. all data are in one table.
could you pls help me on this. it will be very much help for me.thankyou in advance