Reference current row value in Filter expression

[SJRJEJ]

Hi , 

I seem to be struggling with a  problem in PowerBI.

 I will try to explain the issue with the Product and Sales table example .

In my report , I have a drop down filter for Product and I have a Sales table from which I want to display the sales for the Product and also the total sales for the Product Category under which the Item belongs 

 PRODUCT TABLE

ProductCategory Item

Book	B1
Book	B2
Clothing	Dress
Furniture	Table
Book	B3
Clothing	Shirt
Furniture	Chairs

SALES TABLE

Item     Sales    ProdCat

B1	100	Book
Dress	20	Clothing
B2	200	Book
Table	10	Furniture
Shirt	30	Clothing
B3	20	Book
Chairs	60	Furniture

 When I hardcode a particular Product Category , the DAX works :

ProductSales = CALCULATE(sum(Sales[Sales]),FILTER(ALL(Sales), Sales[ProdCat] = "Book"))

In the Above DAX , I want to dynamically reference the Product Category of the item selected in the drop down list box .

I tried using EARLIER function to reference the current value of Sales[ProdCat] but I got the below error .

How do I override the Filter Context and also apply filter based on the current value of the row?

Any advice would be greatly appreciated.

[wdx223_Daniel · Accepted solution]

=VAR _prdcat=VALUES(Product[ProductCategory]) RETURN CALCULATE(SUM(Sales[Sales]),FILTER(ALL(Product,Product[ProductCategory] IN _prdcat))

[SJRJEJ]

Thank you so very much !!!! That worked .

Very grateful for your help .