Using Allexcept with filters

[PeterRodet]

Hi, 

I am not an experienced DAX-user and I have a quite basic question which should not be to difficult for the experts here 🙂

I use powerpivot for this exercice. I have a list with all sales data, including "sales invoice nr", "invoice date", "article number", "sales amount" and "margin" for each article. I have a related Date Table too to report on year, week, etc....

I want to have a basic pivot table with the total sales margin for each invoice number (disregarding the number of articles on each sales invoice). I used this function: 

mTotalSalesMargin:=CALCULATE(SUM(SalesData[Margin]);ALLEXCEPT(SalesData; SalesData[Nr doc.]))

But when I create a pivot table and set a filter on the year (based on the invoice date but coming from the Date Table), it does not filter the year. I tried to change my formula and add the "invoice date" to the ALLEXCEPT-function, but it does not change anything. 

Someone has any idea??

Thanks a lot.

[PeterRodet]

Thanks. But for some strange reason, the "removefilters"-function is not available in excel powerpivot?

[AlexisOlson]

REMOVEFILTERS is a newer function not yet available in Excel but you should be able to use ALL instead as they act the same inside of a CALCULATE.

[ValtteriN]

Hi,

ALLEXCEPT removes all filters except the ones you specify. This is why your year filter doesn't work. If you want to remove certain filters you might want to use e.g. REMOVEFILTERS.