cancel
Showing results for
Did you mean:
Highlighted
Helper II

## 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
Highlighted
Resolver II

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

5 REPLIES 5
Highlighted
Resolver II

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

Highlighted
Helper II

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

Highlighted
Super User I

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

Proud to be a Super User!

Highlighted
Frequent Visitor

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

Highlighted
Resolver II

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

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors