cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mr_Glister Regular Visitor
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.

data table.JPG

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?

data table filtered.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
olesojg
Advisor

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

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

View solution in original post

5 REPLIES 5
olesojg
Advisor

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

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

View solution in original post

Mr_Glister Regular Visitor
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
Super User I

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

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Mark_Timson Frequent Visitor
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
)
 
olesojg
Advisor

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors