Problem Statement:
The sales team needs a report that shows each product's sales contribution as a percentage of total sales. They want this percentage to adjust dynamically based on any slicers applied, such as region, date, or salesperson, but still respect the filters set at the visual level.
Why ALLSELECTED is the Solution:
- In this scenario, ALLSELECTED {Syntax: ALL(<table> | <column>[, <column>[, <column>[,…]]]]) is well-suited because:
- It removes filters applied within the current visual or calculation but respects outer filters or slicer selections applied on the report page.
- This enables the calculation of a percentage contribution within a selected subset while accounting for user-driven filters on the report.
DAX Example:
To calculate each product's percentage of the total sales within the current filter context, you could use:
DAX:
Use Case: Product_Sales_%_of_Selected_Total = DIVIDE([Total_Sales], CALCULATE([Total_Sales], ALLSELECTED(Sales)))
This will:
- Take the Total Sales of each product and divide it by the total sales within the currently selected filters, including any slicers applied at the report level,
- Adjust dynamically based on any selection changes in the report’s slicers or filters.
Key Point:
ALLSELECTED is ideal when you want to create calculations that reflect all applied slicers and outer filters but exclude visual-level filters. It enables percentage or comparative calculations within a filtered subset, adapting dynamically to user selections.