topic Re: Calculate not working when using same field in table and formula filter in DAX Commands and Tips

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

AndreaLorenzo — Thu, 29 Jul 2021 12:42:31 GMT

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

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

AndreaLorenzo — Thu, 29 Jul 2021 12:45:03 GMT

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...

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

Greg_Deckler — Thu, 29 Jul 2021 12:48:55 GMT

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.

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

AndreaLorenzo — Fri, 30 Jul 2021 11:40:59 GMT

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!

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

Greg_Deckler — Fri, 30 Jul 2021 15:02:58 GMT

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

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

m3tr01d — Fri, 30 Jul 2021 16:08:30 GMT

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