Problem Statement:
The sales team wants to see the percentage contribution of each product's sales to the total sales for the company. When filtering by specific regions or time periods, they still want the overall total sales value to be unaffected, so the percentage calculation reflects each product's share of total sales without regional or date filters applied.
Why ALL is the Solution:
- In this scenario, ALL {Syntax: ALL(<table> | <column>[, <column>[, <column>[,…]]]]) is well-suited because:
- It allows you to ignore filters on specific columns (like Region or Date) or even on the entire table to calculate the total sales across all records.
- This provides a consistent baseline for percentage calculations by preserving the overall total sales amount, even when other filters are applied.
DAX Example:
To calculate each product’s percentage of the overall total sales, you could write a measure like:
DAX:
Use Case: Product_Sales_% = DIVIDE([Total_Sales], CALCULATE([Total_Sales], ALL(Sales)), 0)
This will:
- Divide each product's Total Sales by the unfiltered Total Sales across all products, regardless of active filters,
- Show the percentage contribution of each product to the total sales, independent of any other filters applied on Region, Date, or other dimensions.
Key Point:
ALL is a great fit when you need to ignore specific filters for calculating totals or base values, allowing you to make comparative calculations (like percentages) that require a fixed total or baseline.