Simple Stock Level Chart

[Limited2022]

I need help creating a chart that shows 12 months, where I can enter the data at the beginning of every month that shows 3 customers. I want to show for EACH customer, the REQUIRED amount of stock, the number of MISSING for each customer and the total REQUIRED or all 3 customers customers and how many total MISSING. A trend line is also desired.

[AliGW]

Welcome to the forum.

Show us what your source data looks like, please.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[Limited2022]

Thank you for the quick response AliGW. I've attached what I started but cant get it to do what I need. Each customer should have its own section of data but this one shows all customers bundled in one section. The chart on the right is the preferred style because the one on the left is too hard to decipher.

[JeteMc]

Perhaps the following, as modeled in the attached file, will help:
1. Use the following power query code to convert the data from the matrix on the Stock Levels sheet into a proper data set (see A11:D11 and down on Sheet1):

Please Login or Register  to view this content.

Note that A1:I1 on Sheet1 was originally produced using: =A2&"|"&A3 which Excel changed to text when the range was converted to a table.
Note that after the code was applied date formatting was then applied to the Date column in the green/white table.
2. Produce a pivot table from the green/white table
3. Produce a pivot chart from the pivot table
Let us know if you have any questions.