Power BI Inventory Management Dashboard
HTML-код
- Опубликовано: 30 сен 2024
- Tutorial on Power BI, Learn How to build an Inventory Dashboard.
It includes ABC Inventory Classification, XYZ Classification, Inventory Turnover Ratio, Calculation of Safety Stock & Reorder points, Stock Status Classification, ABC Pareto Chart & Demand Forecasting on Power BI. It is extremely useful for Warehouse/ In-plant Inventory Managers to effectively control the Inventory levels and also maintain the Service Levels.
Power BI file & Source Data Excel: drive.google.c...
About me:
I am a student at NITIE Mumbai, batch of 2019-2021. NITIE (National Institute of Industrial Engineering) is a leading academic institution in generating world class supply chain and operations professionals. It is also popularly known as "The Mecca of Supply chain".
Link: www.nitie.ac.in
#inventory #dashboard #powerBi
To Connect with me:
Email: hemu161095@gmail.com
![How To Manage Supply Chain Data With Power BI [2023 Update]](/img/1.gif)
Question/ Problem : I've been trying to setup formula to calculate annual sales quantity but the formula doesn't show number data?
Ans: because the oldest date in the past order sheet is 2020, and now is 2022. and that's why when u input -365, there is nothing appears because there is no data for 2021. Try Putting -1287 (For exact data on date 23/12/2022) or any other no depending on when ur solving the problem (if ur just learning to create dashboard then it dosent matter whether ur data matches or not with the video)
Thank you!
Thank you bro
Trying this in 2024 and it worked. Thanks for putting up this solution.
It has been 2 years since upload, but Thank you for sharing this knowledge.
Hi, I've been trying to setup formula to calculate annual sales quantity but the formula doesn't show number data?
Hi Hemanth Boddapu Can I contact you DM for 2 question regarding this BI session? Please let me know
Hi Hemanth, like other people I would also love to see more power BI webinars. I find that you explain it very well.
Hi there, nice tutorial, but i still not understand about cummulative share coloum , can you explain more ?
Thanks a ton, please upload more such project like videos, God bless you.
Great Presentation! I had a question: When replicating your example, I got stuck when creating the column "Weeks Demand" (at ruclips.net/video/hGOxNYpXkLU/видео.html). When I add the FILTER function for 'Past Orders' table (in line 3), it cannot locate the 'Weekly Demand Sheet'[SKU ID] (in line 4), as well as 'Weekly Demand Sheet'[Week Date] (in lines 5 and 6). My guess is that these are filtered out as they are not included in the 'Past Orders' table. What will be the way around it?
hi Hemanth, thanks for your great report format. but i have a little issue about XYZ's logic. why do you use 0.2*max(CV RANK) or 0.5*max(CV rank). it seems you only simply group like ABC rule. and X should be CV value 0~25% but not CV bank's 20%. this is my comments.
Great Work Hemanth...It will be awesome if you keep on sharing this type of content on weekly basis
Thanks a lot Hemanth for the insights of Using Power BI for Inventory management, well I would like to inquire what the SKU IDs stand for, could they be standing for the different wholesalers that come in to request for the goods????
Great presentation Hemanth, love it :)
Thankyou
My Annual sale quantity is not showing same value as yours..did everything step by step as you did ..
Did you solve it? I have the same problem
Hey hemanth, Very insightful video..I have a question on this...what is the lead time indicating here?
Tq Thejaswi, The lead time indicates the replenishment lead time.. could be either production lead time or procurement lead time
This was fantastic. Thanks very much!
Some comments: for dividing, you can use the Divide(numerator,denominator) function. Also, for the Rank, I used RankX because I was getting weird results: CV RAnk = RANKX(tblInventoryFact,tblInventoryFact[CV],,ASC,Dense)
Very nice!
Hi hemant can you please let me know how to calculate cumulative forecast
Hey Hemant, great content and very insightful, please enlighten us with such good content going ahead..also could you increase the frequency of sharing the videos looking forward.. thank you
Great Hemanth, it is a very nice tutorial. Can you upload such relevant tutorial for small retail shops and manufacturing units👏🙏
Thankyou Ken.. i would love to make content on retail and manufacturing.. i am looking for some use cases to connect to some real problems.. let me know if you have any
Helpful Presentation .. need help to create the same but getting error so Is to possible to connect to resolve the issue
Thank you very much for this very useful video! Regards :)
Simple but useful, Many thanks for your share
If you want the annual sales quantity to work used 43996 instead of today in the DAX formula.
U just skipped the video
This is very helpful! Thanks! Just a question on the formulas you used on each metric. Are they standard in the industry?
That's a good question. For Most industries the formulae are similar in nature if not the same. For ABC XYZ calculations, the cutoff values might vary, while Safety stock and reorder points will change based on many other factors based on the type of product and company.
Good video and very well presented. Thanks for this work..
Hey thanks for the tutorial. I was following along but how do you change the row when you're writing the formula? like you constantly change the row. I tried tab, shift, and everything but nothing works.
did you get it how to do it?
Great work Hemanth, nice daschboard for scm management
Can you work with me on a personal file?
Thanks Hemanth. This video is great.
Good stuff @hemanth.... thx for sharing
Dear Hemanth, very nice tutorial, but could you please redo all that using only measures instead of new columns? That´s because the performance will be quite better. Using columns with a big data will becomes almost impossible, too lazy, Could you do that?
Some of the calculations are giving some trouble when used a measure instead of columns. Especially the categorisation of ABC and FSN. Anyways, let me try again.
@@HEMANTHHEMU86 I tried exactly the way you did but I have 5700 sku to control at fact table. The dashboard becames to heavy
@@HEMANTHHEMU86 and thank you very much to answer
@@HEMANTHHEMU86HI
did you try these with measures?
hi hemanth while I using annual revenue command after && it is showing blanks value as result what might be the problem, whether I should put date instead of -365
i have the same problem as well
@@andreastulus4351 hi andreas I solved it later just use function generate series and put new dates
@@mohitpande2006 can you provide the syntax
@@mohitpande2006 please provide the syntax
Very well explained. Very helpful for inventory management and domain knowledge..
Very helpful Hemant good job.
Hi Hemanth, great video illustrating Power BI use cases in the field of inventory management. I have one small question, how can we do a dynamic ABC and XYZ analysis such that the classes update based on selection on the date slicers? It'd be very helpful if you could make a video on the same. Thank you so much for such value content. Keep it Up!
Annual sale quantity = CALCULATE(
SUM('Past Orders'[Order Quantity]),
FILTER('Past Orders',
'Past Orders'[SKU ID]=Stock[SKU ID] &&
'Past Orders'[Order Date]>=TODAY()-365))
This formula above returns a blank column without any value in it, Can anyone help on this ?
I have the same case, pls help with this formula!
because the oldest date in the past order sheet is 2020, and now is 2022. and that's why when u input -365, there is nothing appears because there is no data for 2021.
try this
Annual sales quantity =
CALCULATE(SUM('Past Orders'[Order Quantity]),
FILTER('Past Orders',
'Past Orders'[SKU ID] = Stock[SKU ID] &&
'Past Orders'[Order Date] >= MAX('Past Orders'[Order Date]) - 365))
Its worked thanks buddy ❤
Hello, how connect with you
Very nice
Looking forward to more of your Power BI videos focused on supply chain management topics. Thanks for this video on inventory management!
I have sales and purchases for different products from different stores(shops) as orders table in Table and now I want to do Inventory analysis.
How to you get the current stock available and what is sku_id?
Hi everybody, i have a doubt. Why is necesary to use a CV RANK to find the XYZ Category ? I think is just needed the CV column and then a IF to asing XYZ category as well the ABC.
Awesome Explanation
Good Dashboard
Can we derive same type of Dashboard for Stocks of raw material which will be used for Manufacturing?
When i write the "Annual Sale Quantity" column it creates no values at all. The problem seems to be the >= sign. Any solutions?
At 5:37 I cant bring stock[SKU ID] does anyone else have same problem ?
Fantastic tutorial. I was able to create my own dashboard and love it. Thank you so much!
You are great my friend
Wow nice work, appreciate it.
Very Useful! this is what i need for quite a while!
Brilliant presentation Hemanth
How to load data from the ERP or link ERP to Power bi?
hiya, the excel sheet and link doesnt seem to work, can you upload this again? thanks!
Excellent demonstration of an Inventory Management Dashboard. Flow was easy to follow and process was crystal clear. Thanks a ton.
Thankyou sir
Amazing presentations with very useful KPI's. Thanks
Hlo.. I had a doubt. While link with ERP, is there any ways to auto updates of data?
How did you get the Sales Amount on the Weekly Demand Table?
Thanks for sharing the video but now when I have the downloaded the pbix file it shows different metrics in visualization . Is it bcoz of that you have selected last year data using today formula
If we transform data using power bi query window rather than from tables , what will be likely result?
You should not calculate "Revenue % " in backend . This should be handled in Front end. As if you apply filter on data (slice and dice) this will lead to wrong results.
Great work Hemanth... Really informative.. Thank you for this... 👍
Sir Great Video 🙌🙌
this get the product price from the "stock" file.. what if the products have different prices and the prices is in the order file (where it should be imo)
guess I figured it out:
Annual Revenue=sumx(filter('past orders','past orders'[sku id]=stock[sku id] && 'past orders'[order date]=today()-365),'past order'[orderprice])
It would need the orderfile to have the column "orderprice" with the customers total order price per item in it..
@@fosben
Can I ask?
The data model that you used here, is it relational/transactional database or data model with star schema relationship?
@@norpriest521 same as video, but with the orderprice moved to the orderfile
where is description box to download inventory dashboard file
What is the formula for calculating max holding?
Nicely done.. will try it out on my own now!
Great job with this video! Thank you!
very nice session
Can you help me to connect powerbi with mongodb
Great content. Clear and concise. Many Thanks.
fully packed content in a very short video, thanks alot!
Can you please share the file link?
drive.google.com/file/d/15uoiFP5t3eV5SRrkv42vdwu8klxENbx2/view
Could you please tell me the dataset source?
One of the best videos that I have ever seen!
Can you please share with us the two excel files (Inventory + SKU) data for preparing the dashboard
Hi @HEMANTHHEMU86 When I am entering the calculate function for Annual Sales Quantity it is not recognizing Stock[SKu ID] in the filter subsection. I have also tried copying it from the pbi sample file but still it is giving error.
Very informative... Thanks 👍
Very insightful !!! You nicely covered the basics of inventory management as well as dashboard designing.
Annual Sales Quantity is not working it's giving me empty space? can you reply
You can use the following formula to eliminate the black error:
AnnualSaleQuantity =
VAR LatestDate = MAX('Past Orders'[Order Date])
RETURN
CALCULATE(
SUM('Past Orders'[Order Quantity]),
FILTER(
'Past Orders',
'Past Orders'[SKU ID] = Stock[SKU ID] &&
'Past Orders'[Order Date] >= (LatestDate - 365) &&
'Past Orders'[Order Date]
Hi Hemanth, this was posted some time ago but really has been a great help to me as a newbie to Power BI. Thank you so much for sharing
Pretty cool Hemanth!
Great work. Can we have the dataset 🙏
drive.google.com/file/d/15uoiFP5t3eV5SRrkv42vdwu8klxENbx2/view
Hie @@HEMANTHHEMU86 got to prepare a dashboard i was following every step but i dont know why even after trying for so many times my formula was not working and it is showing some error at 16:45
how can we do this in FY
Thank you so much
When I applied the formula in power query my annual sales quantity column is blank. Do anyone have any suggestions what should I do in that case?
Appreciate your help.
same probelem you got your issue resolved?
i put today function - 940 days instead of 365
Where can i find the source data for this? It would be amazing if you could share the source data.
There's a link in the description for the file. You'll find the data in it
7 : 52 what was that
Hi. Thanks for the video. However when we download the data and try doing it in our systems, we are unable to fetch the Annual Sale Quantity. The entire column is blank.
Kindly suggest
This is because you might be putting -365 and because of that it is trying to get data for the past year lets say if you are doing it in 2022 then it is trying to get info of 2021 which is not present in the data set instead try -1451, for today's date
Its is still not working and alots of errors are showing what should i must do
Annual Sale Quantity1 =
CALCULATE(
SUM('Past Orders'[Order Quantity]),
FILTER(
'Past Orders',
'Past Orders'[SKU ID] = 'Stock'[SKU ID] &&
'Past Orders'[Order Date] >= MAX('Past Orders'[Order Date]) - 365
)
)
you are really smart!
Good Day Hemanth. I have noticed there is a discrepancy between "SKUs to Reorder" which is "258" and "Stock Status Below Reorder Point" which is "257". Shouldn't these two figures be equal? Kindly look into it and advise accordingly.
Thanks
Great Work Brother 😮
Perfect 👌
Good
nice job . I have a question, why do you use so many calculated columns instead of measures
Works either way. I personally feel comfortable with columns when there are multiple tables getting linked.
@@HEMANTHHEMU86 Also there is a difference between the measure and calculated column. We cannot interchange them. This is discussed by SQLBI Marco & Alberto. I like Calculated columns over measure in some areas where eg: Stock on hand may have SKU which do not have sales or demand due to legacy. Nicely presented.
Hi i want to ask, did you already sort the sku from the higest revenue share to the lowest revenue share before you put cummulative column?
No, that never works on powerbi because there is no row number. The formula i have inserted will take care of that
@@HEMANTHHEMU86 in 9:16 could you explaim what does this formula mean? Revenue share > Earlier[ Revenue Share]
@@helenarchengles4402 it's a filter to consider only the rows where the revenue share is greater than that specific row revenue share..
@@HEMANTHHEMU86 so that formula act as sort revenue share from higest to lowest right?
@@helenarchengles4402 exactly
english subtitle, or speak normal English, very difficult to understand , too fast and not loud and clear