Percentage Calculation using multiple tables columns -DAX Function

Hi OrreBI ,

When creating the Category Group column in Power Query, it becomes a static column in the data model, which means the relationships and filters applied dynamically in DAX are not aware of the grouping logic. However, when the column is created in DAX, the calculation respects dynamic filtering, allowing the ALLSELECTED() function to work correctly. To ensure this, use a calculated column in DAX rather than relying on Power Query.

'Table-3'[Category Group] = 
SWITCH(
    TRUE(),
    'Table-3'[Category] IN { "Category -1", "Category -2" }, "Group A",
    'Table-3'[Category] IN { "Category -6", "Category -8" }, "Other",
    "Uncategorized"
)

In the case of the donut chart or pie chart, percentages are showing as 100% when drilling down because the filtering logic does not properly consider both Category Group and Outcome in the denominator. A better approach is to write a single DAX formula that dynamically adapts to different filters and calculates the percentage correctly.

Category Group % =
VAR TotalHours = SUM('Table-2'[Hours])
VAR FilteredTotal = CALCULATE(
    SUM('Table-2'[Hours]),
    REMOVEFILTERS('Table-3'[Outcome], 'Table-3'[Category Group], 'Table-1'[Category]) 
)
RETURN DIVIDE(TotalHours, FilteredTotal, 0)

This measure ensures that when filtering on one field, such as Category Group or Outcome, the denominator remains consistent and includes the correct total. The use of REMOVEFILTERS() instead of ALLSELECTED() prevents unwanted interactions when drilling down, ensuring accurate percentage calculations across different levels of aggregation. Additionally, reducing bi-directional filtering where possible can help avoid circular dependencies, improving the reliability of the model.

Best regards,