Dear all,
I have the following challenge. I work at a plant nursery where we grow plants with a production span of around 6 months until they are ready to be sold. We have several customers we produce plants for on a weekly basis (so for example every week 500 of the same variety), and most plant varieties are sold to multiple customers. We do our best to controll our production, but since we produce life plants, some may grow slower or quicker or perish due to several factors. Therefore it is needed to have good insight in the expected availibilty per week.
So I have a dataset with 5 tables, 3 for dimensions and 2 with facts. The dimensions exist of 1 table with article numbers, the second with clients and a third is a date-table. The facts consist of 1 table with the produced lots with their expected delivery week and a second table with the sales demand. The 2 tables have no direct relation, only via the dimension tables. I already created a matrix visual with a total overview per variety, but now I like to create an overview where a user can filter on a customer name and see the expected shortages/surplus per variety per week. Since the production is made to stock there is not yet a direct relation between the demand and the produced lot, so I need a calculation that does something like this: ((total plants expected to be available / total plants demand ) -1) * plants demanded by filtered costumer, e.g. 4000 produced, where total demand = 5000 and demand for filtered customer = 500 which will show me ((4000/5000) -1) * 500 = -100. This calculation works, but the part where I strugle with now, is that in my matrix I will see all the production availability for all the varieties. So how can I include there a filter that will make the matrix show only the by this customer demanded varieties?
For example, if we can make something work that just look like this I would be very happy already:
| Item | Week1 | Week2 | Week3 | Total |
| A | -100 | 500 | -300 | 100 |
| B | 250 | 50 | -200 | 100 |
| C | 10 | 20 | -40 | -10 |
| Total | 160 | 570 | -540 | 190 |
I'm sure I'm not the first with this kind of question, but I could not find an answer yet by myself so I hope someone in this community can point me in the right direction, many thanks in advance!
Kind Regards, Emiel
