Filter on table and column

[shady_hamilton]

Hello, i have two tables, one that has the stock of each product and the second has a cloumn that has the names of the products purchased. each row of this column contain one product purchased, so if i want to know how many toys i purchased i count how many rows has "Toys" in them.

I want to create a varialbe that will give me the available stock from each product (stock - purchased) when i select the product name from a dropdown menu (a filter of the prduct name) 

what is the best way to create this please

[Omega]

Can you please share a sample excel/PBIx file for the data set and the expected result? Thanks!

[shady_hamilton]

Hello Khader,

I am attaching the example. the first sheet has the column of the purchases per day and the other sheet has the purchased stock per date.

I want to know how much stock left from each product by selecting the name of the item from a drop down menu, when selected it will tell me how much purchased and how much stock left.

many thanks,

[v-piga-msft]

Hi @shady_hamilton,

Maybe something mistake. We cannot see your sample data. Please upload your sample data and the desired output so that we could help further on it.

Best  Regards,

Cherry

[shady_hamilton]

sorry, not sure what happened, here is the sheets again, First Sheet (purchases) Date Purchases 01/01/2018 Toy 02/01/2018 Toy 03/01/2018 Pen 04/01/2018 Toy 05/01/2018 Book 06/01/2018 pencil 07/01/2018 Pen 08/01/2018 Toy 09/01/2018 pencil second sheet (Stock): Date Purchased Toy Pencil Book Pen 01/01/2018 10 5 6 15 02/01/2018 3 1 4 0 03/01/2018 4 0 4 0 04/01/2018 2 0 1 0

[Omega]

I believe you are trying to create the below tables. If so, how much quantity do you have in the purchase columns? 

[shady_hamilton]

Example

[shady_hamilton]

Hi Khader,

no, i have two tables, the first one will log the purchases per day/time.... the other one will log the stock we purchase, we top up the stock every now and then, so no specific amount or intervals.  these are two seperate tables

i want to know at a certain time how much stock i still have from one item by calculating the sum of the purchases of one item and deducting it from the total of the stock for that item. 

showing each item in one box in Power BI is easy, my issue is that i have a lot of items, so i want to have a dropdown filter, where i select the item and it will show me the remaining stock for that item.

[shady_hamilton]

Any help please?

[v-piga-msft]

Hi @shady_hamilton,

Do you want to create a dropdown list with values of Toy, pencil, book and pen?

If it is convenient, please share your desired output so that we could help further on it.

Best  Regards,

Cherry

[shady_hamilton]

Hi V-Piga-msft

Yes correct, the filter will be Book, Pen, Pencil and Toy.

as for the results i want to know how much Books left in stock after all the purchases and stock top up, so if i select Book for example, it will tell me you have so and so left in your stock for books...

[Omega]

If that's the case, then you need to apply a countrows for the first sheet where you will be counting the purchases. Then, take the difference of the sum of stocks from the second sheet. 

[shady_hamilton]

countrows is fine, I did that, my problem is the formula where you change that every time you change the filter, so when i select books in the filter the box showing the available stock should show me the books stock, if i select pens then the box should show me the Pens stock and so on...