# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  >  Count stock in another table based on filter

## bongielondy

I failed to create relationship between 2 tables that have multiple relationships. How can I count the stock that is in another table based on what a user filters from a pivot table. In the Power Pivot Data Model, the calculations are in Sheet1 while the stock is in Sheet2. The number of stock is 15. I use COUNTROWS function to count the models in the list. 

But when I filter by customer, model or region the stock number still remains 15. I need to get the correct stock number when I filter the stock by customer or region or model or by any 2 filters or all filters.

The report name is SReport.

----------


## Tom1986111

The Excel data model cant build many to many relationships so this is why you cant set up a relationship. You are missing a key field.  You can create a table with unique Names and another with unique regions, create the one to many relationships and pop these into filters on a powerpivot.

I've done this as below with customer names and it works:
pp.PNG

----------


## bongielondy

Thank you. I will create the customer, region and model tables and link each to the dimension and fact tables. Thanks so much

----------

