CALCULATE(SUM(....),ALL(....)) doesn't ignore filter

[Mr_Glister]

Hi,

I have a data table ('SALES') which basically consists of two columns, Date and Volume HOG. 

Here is a glimpse. Notice that this is a table visual and I'm not showing all the rows but that the total is 47,408.

In the page I'm using I have a filter on the Date to show only the last 10 weeks. But I need a measure that returns always the unfiltered total of the column Volume HOG (=47,408) no matter what the date filter is.

So naturally I want to do : Volume HOG total= CALCULATE(SUM('SALES'[Volume HOG]), ALL('SALES'[Date]))

However, it doesn't work. The table visual correctly shows and sums up only the last 10 weeks but the measure I created, which should ignore and filters on the Date, returns the same result instead of 47,408. And I have no idea why... Can anybody help me?

[Mark_Timson]

Is there a way of doing this but then summing all by a specific group ? I have Filtered my table down by specific week and Branch I want a company total by the same group. When I use all it will just return the whole sum of the table I dont want that I want sum by group but for all branches.

Below is an example:

Group|SalesAmount| CompanyTotal

NNQ  | 2836            | 10025

Branch Sales Company TY = 
VAR TFW = SELECTEDVALUE('s_bgw'[week])
RETURN 
CALCULATE
(
    SUM('iwdba s_bgw'[salesamount]),
    ALL('s_bgw'),
    's_bgw'[week] = TFW
)

[Anonymous]

Try using the ALLEXCEPT call. Here you can add in the fields that you don't want the filter removed on (week and branch).

[Anonymous · Accepted solution]

Instead of ALL('SALES'[Date]) can you try ALL('SALES'))?

[Mr_Glister]

Simple as that! Thanks a lot! But can you tell me why this works or why my formula didn't?

[vanessafvg]

@Mr_Glister i think its because of the filter context on the date.  

[plansis]

ALL('SALES') removes ANY filter on the SALES table

so it looks like it works in this example but it does not in general.