Calculate not working when using same field in table and formula filter

[AndreaLorenzo]

Hello everybody,

I'm trying to do something very simple, but I can't make it work:  from a Sales table, I need to compare the revenue for every product category related to another one.

I've  created two colums:

    Total Sales= SUM(PBI_Sales[Revenue))

     H Revenue= CALCULATE([Total Sales]; PBI_Products[Category]="H")

I'm getting this table:

 

However, if I dont use Product Category in the table, it works and filters by sales of that product:

Is there something I'm missing? I've checked my table relantionships as well, and there's 1-N from Products to Sales, nothing weird.

Any help will be appreciated!

Kind regars

[m3tr01d]

@AndreaLorenzo From what table is coming the column "Prod Cat" on your table. Is it from the Product table?

[Greg_Deckler]

@AndreaLorenzo You are running into yet another reason I don't use CALCULATE. Basically, if you use CALCULATE with a straight filter clause (no FILTER function) then if the column you specify is already in the filter context, it replaces the filter context for that column. Otherwise, it adds to the filter context. 

CALCULATE function (DAX) - DAX | Microsoft Docs

It's hard to tell because I don't know your source data but it is probably something along those lines. As a general rule, if a function needs 50 blog articles to explain how it works, I tend to avoid it.

[AndreaLorenzo]

Thanks for the feedback, @Greg_Deckler . Calculate is a bit difficult to understand, yes.

I finally got it working, maybe is not the most elegant solution, but it will do... 

H_Sales= var _tbl = ALLSELECTED(PBI_Sales)

var _catFiler= "H"

RETURN SUMX(FILTER(_tbl;  PBI_Sales[CategoryID]=_catFilter); [Revenue])

Thanks!

[Greg_Deckler]

@AndreaLorenzo That's pretty much exactly how I would have done it.

[AndreaLorenzo]

Sorry, I forgot to say that my problem is that I don't know why the "H revenue" value is not showing for every row, only for the matching criteria product...