Log In
Register
Home
February 18, 2022
Offline
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.
See Answer
July 16, 2010
Offline
Hi Justin,
Welcome to our forum. Please upload a sample file so we can see the format of your data.
Mynda
February 18, 2022
Offline
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.
July 16, 2010
Offline
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
February 18, 2022
Offline
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
July 16, 2010
Offline
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
Answers Post
February 18, 2022
Offline
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
1 Guest(s)
