After looking at a few similarish questions I figured I needed something more specific so asking here. I will start by explaining the situation:
The Setup
I have a Store which sells Cakes, Cookies and Wine. I have the weekly sales data of each product sorta like this:
| Product ID | Product Name | Quantity | Value | Week Ending |
|---|---|---|---|---|
| 1 | Ginderbread | 2 | £4 | 13/01/22 |
| 2 | Chocolate chip | 5 | £25 | 13/01/22 |
| 3 | Red Wine Bottle | 1 | £10 | 13/01/22 |
| 4 | Sponge Cake | 3 | £9 | 13/01/22 |
Currently every week's data is stored within the same table, with me using a Week filter to show only the week i'm interested in.
Using this Data I created PivotTables that shows the sales of each category, with the ability to drill down to show the specific products. Table looks something like this:
| Category | Quantity | Value |
|---|---|---|
| Cakes | 2 | £4 |
| Cookies | 7 | £29 |
| Wine | 1 | £10 |
The issue
I now want to stick in a new calculated column that shows the Value as a %. E.g The total value for the previous table was £43, so Cookies is about 67%. If I drill down, it would show the Chocolate Chip record as 80% and Gingerbread as 20%
I imagine doing this would be easier if each individual week's data was on a different table, but I got a lot of weeks and I also want to do tables showing the sales for over a period of time. Plus I don't know of a way to merge the "value" and "quantity" columns, etc instead of having 1 for each week being shown.
any advice would be appreciated
