Can Related and Filter be used in the same formula to filter a value?

[LBala]

Hi all;

I have a simple power pivot report with two tables (Table1 and Table2 below) connected by the field "Item".
I need to create a calculated column in the Table2 called "StoreB_Quantity" that shows the quantity the store B has of a certain product. I tried using the RELATED and FILTER functions together but could not put both working to show the results listed in the Table2.

How would the formula be under StoreB_Quantity below to show those results?

Thanks.


Table1:

Store	Item	Quantity
A	Banana	2
B	Banana	5
C	Banana	8
D	Banana	1
A	Melon	0
B	Melon	3
C	Melon	6
D	Melon	4
A	Apple	12
B	Apple	7
C	Apple	9
D	Apple	2

<tbody>
</tbody>

Table2:

Item	StoreB_Quantity
Banana	5
Melon	3
Apple	7

<tbody>
</tbody>

 

[Matt Allington]

I need to create a calculated column in the Table2 called "StoreB_Quantity" that shows the quantity the store B has of a certain product.

I don't recommend you do that. Read my article here Calculated Columns vs Measures in DAX - Excelerator BI

If you explain what your final desired outcome is, then I can give you some advice on how to go about it.

 

[LBala]

What I need is a formula for the calculated column StoreB_Quantity in the Table2 that looks up the Table1 and gives me the the quantity of each item Store B has.


Basically, the formula needs to filter only the Store B in the Table 1.

 

[Matt Allington]

What I need is a formula for the calculated column StoreB_Quantity in the Table2 that looks up the Table1 and gives me the the quantity of each item Store B has.

That is what you think you need, but it is not what you need. You have interpreted the problem and come up with a solution that is not ideal. This solution will give you a new column in your first table like this

Store	Item	Quantity	StoreB
A	Banana	2	5
B	Banana	5	5
C	Banana	8	5
D	Banana	1	5
A	Melon	0	3
B	Melon	3	3
C	Melon	6	3
D	Melon	4	3
A	Apple	12	7
B	Apple	7	7
C	Apple	9	7
D	Apple	2	7

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

So now 3 out of the 4 rows for each item have an unrelated value (storeB stock) next to it in a column.

So my question is, what are you trying to do here as your final outcome. If you can explain that, I would be happy to advise you on how to do it correctly.