I am struggling with a calculation where I need to show "Total Sales" for the entire year, even when a user selects a specific month in a slicer. I’ve tried using the ALL function, but it removes every filter, including the ones I want to keep (like the Region filter). Is there a way to write a measure that behaves correctly both inside and outside the visual filter context? Specifically, I want to calculate a percentage of the total, so I need a denominator that stays constant regardless of the month filter.
3 answers
To control how a measure behaves across different filter contexts, you must master the CALCULATE function combined with "Filter Modifiers." If you want to ignore a specific filter (like the Month) while keeping others (like the Region), you should use ALL( 'Date'[Month] ) as a filter argument inside your CALCULATE.
For example:
Total Sales (Ignoring Month) = CALCULATE([Total Sales], ALL('Date'[Month]))
This tells Power BI: "Calculate sales, but if there is a filter coming from the Month column, ignore it." This allows your measure to show the annual total even when a visual is filtered down to a single month, which is exactly what you need for a "Percentage of Total" calculation.
Have you considered using ALLSELECTED instead of ALL? If a user filters the report to show only three specific regions, ALL will still calculate the total for every region in the database, whereas ALLSELECTED will calculate the total for just those three regions—wouldn't that be more accurate for a dynamic dashboard?
If you find yourself needing to keep multiple filters but clear one or two, ALLEXCEPT is your best friend. CALCULATE([Sales], ALLEXCEPT(Sales, Sales[Region])) clears every filter except the Region.
Thanks Nancy! I used ALLEXCEPT for a product category report last week and it simplified my DAX code significantly. It's much cleaner than listing out ten ALL statements!
Steven makes a great distinction! ALL is "Hardcoded" (ignores everything), while ALLSELECTED is "User-Aware" (respects filters outside the visual but ignores filters inside the visual). For a professional Business Analysis report, ALLSELECTED is usually the better choice for the denominator because it allows the user to define the "universe" of data they are looking at via slicers, while still allowing individual bars in a chart to represent a percentage of that specific selection.