Question

How to Calculate Dynamic Regional Sales Percentage in DAX?

I need a Power BI measure that calculates the percentage of total sales contributed by each region. The measure should dynamically adjust based on user-applied filters. What is the best approach using DAX functions like CALCULATE, ALL, or ALLSELECTED to ensure that the percentage updates correctly with filtering?

Answer 1

To compute the percentage of total sales per region while dynamically adjusting to filters, use this DAX measure:

RegionalSalesPercentage = 
DIVIDE(
    SUM(Sales[SalesAmount]), 
    CALCULATE(SUM(Sales[SalesAmount]), ALLSELECTED(Sales))
)

Explanation:

• SUM(Sales[SalesAmount]) calculates the total sales for the current region.
• CALCULATE(..., ALLSELECTED(Sales)) ensures the denominator reflects the filtered total sales based on slicers.
• DIVIDE prevents division errors if the total sales are zero.