Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Advocate II
Advocate II

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


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

Not applicable

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

View solution in original post

Helper I
Helper I

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])
    SUM('iwdba s_bgw'[salesamount]),
    's_bgw'[week] = TFW
Not applicable

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

Not applicable

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

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

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

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

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


Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors