• 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

Dashboard for Warehouse|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Dashboard for Warehouse|Dashboards & Charts|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 ForumDashboards & ChartsDashboard for Warehouse
sp_PrintTopic sp_TopicIcon
Dashboard for Warehouse
Avatar
Justin Redua
Member
Members
Level 0
Forum Posts: 22
Member Since:
February 18, 2022
sp_UserOfflineSmall Offline
1
March 26, 2022 - 1:10 pm
sp_Permalink sp_Print

Dea All,

 

Hi im using microsoft 365, im using windows and have some problem here.

I have data from a fabric warehouse.
In the data there are several sheets. The sheet indicates the location of the fabric storage.
1. How do I make a summary every day / week or month how many kilograms / yards of fabric that goes in and out? included the balance
2. What is an easier way to see when the fabric arrives, which row has space to store the fabric?

I don't know which is the best way to use dashboard, power query or power pivot.

Hope my explanation is clear.

Thankyou.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 26, 2022 - 9:55 pm
sp_Permalink sp_Print

Hi Justin,

Welcome to our forum. Please upload a sample file so we can see the format of your data.

Mynda

Avatar
Justin Redua
Member
Members
Level 0
Forum Posts: 22
Member Since:
February 18, 2022
sp_UserOfflineSmall Offline
3
March 28, 2022 - 8:34 pm
sp_Permalink sp_Print

Hi Mynda,

I send the attachment file. allow me to explain a little bit.

the warehouse have lines of Fabric box to storage the fabric. 1 line have total 27 space to put the fabric and have 2 level.

the fabric location mention in row H in excel file.Picture.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Picture.png (67 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 29, 2022 - 11:29 am
sp_Permalink sp_Print

Hi Justin,

Thanks for sharing your sample file. The data in the file is not clean enough to work with. e.g.:

  • Item numbers are inconsistently entered, some are text and some are numbers e.g. 60012609 is entered as text and separately as a number.
  • Date In column contains blanks and one cell (A407) has a date of 19th April 1900.
  • YDS In has #REF! Errors
  • The Color column has some entries with trailing spaces after the colour see Sheet A, cell F9 as an example.

There may be more issues.

In addition, it's not clear how much fabric fits into each box, to know how to calculate/visualise this.

I think if you can clean up the data (you can use Power Query for this), then you can create a running total with a PivotTable of fabric in and out in KGS/YDS that will give you an idea of what you have in stock. You can use Slicers to filter the PivotTable by colour, fabric code etc. 

I hope that points you in the right direction.

Mynda

Avatar
Justin Redua
Member
Members
Level 0
Forum Posts: 22
Member Since:
February 18, 2022
sp_UserOfflineSmall Offline
5
March 30, 2022 - 8:08 pm
sp_Permalink sp_Print

Hi Mynda

Thankyou for your support & help. now i'm fixing the data.

1. for Fabric Type FL & FT (on column D), 1 box max 12 qty (J column), for other Fabric Type 1 box max 30 qty. its possible to make visual or dashboard ?

2. I Have some data. how to make pivot from Fabric requirement kgs & yds minus kgs & yds in (from different sheet)

3. how to make pivot from 'In and Out' sheet with balance from in kgs in & yds in minus kgs out & yds out .

 

is there any better format for the form ? because if i make the sheet like 'Rows' sheet. its okay to have blank data base like that ?

its possible to combine 2 different sheet with different header ?

Justin R

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
April 1, 2022 - 9:57 am
sp_Permalink sp_Print

Hi Justin,

1. Yes, however I'm not sure what you're expecting to do with the max limits you mention.

2. If your data is split over multiple sheets, then you need to use Power Pivot and DAX measures to do the analysis you want.

If your data is in one table, then it's ok to have blanks in the in/out columns and might be easier for you to build your reports if you're not familiar with Power Pivot and DAX.

3. Looks like you've already done this. Now you just need to 'show values as' > 'Difference From' > Base field: Status and Base Item: Previous. You'll also need to change the order of the Status columns (Left click and drag) so that Out is the first column.

4. You can use Power Query to merge sheets with different headers.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Justin Redua
Member
Members
Level 0
Forum Posts: 22
Member Since:
February 18, 2022
sp_UserOfflineSmall Offline
7
April 1, 2022 - 2:25 pm
sp_Permalink sp_Print

Hi Mynda,

1. I need data for me to know what percentage or kilogram of warehouse capacity is already filled and if one fabric arrives, the user can see which row has enough space to store those fabric.

2. I'm quite familiar with Power Pivot since 3 months ago, I learned from the video you shared on YouTube, so I will try for this one especially for DAX measures

3. It's work for this. Thank you 

4. Okay I will try it.

 

Justin R

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sonja Mason, Alexandra Radu
Guest(s) 9
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
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

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