Forum Discussion
Calculate not working when using same field in table and formula filter
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
5 Replies
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...
- Greg_Deckler
Community Champion
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.
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.
m3tr01dContinued Contributor
AndreaLorenzo From what table is coming the column "Prod Cat" on your table. Is it from the Product table?
