PowerPivot DAX measure Filtering

0
votes

I am starting to use all de PowerBI tools for a customer.

On the data they have three shops (1,2 and 4, named in the table "botiga") and diferent departments (named in the table "departament").

I need to calculate the sales (named "vendes" or Ventas[import]) of all the departments and all the shops but I have to exclude in the calculation the ones that correspond to the shop 1 and 2 and also correspond to the department 61. All the realated columns are in the same table named "Ventas"

I did it and it Works with this measure:

vendes sense carnisseria:=[Vendes]-SUMX(FILTER(Ventas;Ventas[Botiga]<>"4" && Ventas[Departament]="61");Ventas[Import])

Vendes:=sum(Ventas[Import])

But I am sure that theri is a better way to do it, I have tried to do like in excel when we do SUMIF but doesn't seems to work.

I use FILTER beacouse I want that the data filters used in the associated pivot table remain.

1
daxpowerbi

1 Answers

1
votes

Calculate is like a supercharged Sumif.

Try something like

Carnisseria:= [vendes] - Calculate([vendes];Ventas[Botiga]<> "4"; Ventas[Departament]="61")

I would also check that you want any shop that isn't #4 or if you want just shops #1 and #2. While that yields the same results right now, it may not in the future.