cancel
Showing results for
Did you mean:
Regular Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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

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

5 REPLIES 5

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

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

Regular Visitor

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

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

Super User I

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

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

Proud a to be a Datanaut!
Frequent Visitor

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

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
)```

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

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!