Percentage Calculation using multiple tables columns -DAX Function

[OrreBI]

Greetings,

 I have three table(s) in Semantic layer and have one-to-many with bi-directional relationship between tables and I have shown the sample DAX function the below I have used for my reports.

Table-1: has only one record per id

Table-2: can have many records and the grain is name and Id

Table-3:  can have many records and the grain is name, outcome

Table-1	Table -2	Bi-directional - One to Many
Table-1	Table-3	Bi-directional - One to Many

Outcome %= DIVIDE(SUM('Table-2'[Hours]) , CALCULATE( SUM('Table-2'[Hours]), ALLSELECTED('Table-3'[Outcome]) ) )

 Category Group %= DIVIDE(SUM('Table-2'[Hours]) , CALCULATE( SUM('Table-2'[Hours]), ALLSELECTED('Table-3'[Category Group]) ) )

 Question-1:   I have created data group column on category and when I use data group column percentage, its working as expected but if I create the column in Power query using category and use the column in percentage like the below, it’s not working as expected.  Any thoughts on this.

 Category Group %= DIVIDE(SUM('Table-2'[Hours]) , CALCULATE( SUM('Table-2'[Hours]), ALLSELECTED('Table-3'[Category Group]) ) )

 Question -2:

I have created Donut chart or pie chart or graph, its working for one specific column either category or outcome. If I drill down to Category Group from Outcome or from Category Group to Category,   it's not working as expected and showing the values as 100%.  I have changed the logic the below this but no luck 

Category Group %= DIVIDE(SUM('Table-2'[Hours])
, CALCULATE( SUM('Table-2'[Hours]), ALLSELECTED('Table-1'[Category Group])  , 'Table-1'[Category ])  ) )

Is there any way to write a single Dax measure to support all three combinations to create percentage metric.  I have shown the sample table the below for your quick reference.

Table-2 Name	Category	Category Grp	Outcome	Total Hours	Grand Total	Percentage	Outcome Percentage	Outcome Grp  Percentage
Name-1	Category -1	Category -1	Outcome -1	7	19	36.84211	= (7/19)*100	= 7/19 *100
Name-1	Category -2	Category -2	Outcome -2	4	19	21.05263	= (7/19) *100	=4/19*100
Name-1	Category -6	Other	Outcome -2	3	19	15.78947	= (7/19) *100	=8/19*100
Name-1	Category-8	Other	No Outcome	5	19	26.31579	=(5/19) *100	=8/19*100

Do you have any other recommendations ? Thanks you in advance for your help on this.

Table-2			Table-3			Table -1
ID	Table 2 Name		ID	Outcome		ID	name	Category	Category Group	Hours
1	Name -1		1	Outcome -1		1	name 1	Category -1	Category -1	5
2	Name -1		2	Outcome -1		2	name 2	Category -1	Category -1	2
5	Name -1		5	Outcome -2		3	name 3	Category -1	Category -1	4
8	Name-1		8	Outcome -2		4	name 4	Category -2	Category -2	3
10	Name-1		10	No Outcome		5	name 5	Category -2	Category -2	5
						6	name 6	Category -2	Category -2	5
						7	name 7	Category -5	Category -2	3
						8	name 8	Category -6	Other	2
						9	name 9	Category -7	Other	6
						10	name 10	Category -8	Other	6

[Anonymous]

Hi @OrreBI ,

If DataNinja777 's answer is correct, please mark his answer as a solution. If the problem persists, can you tell us more about the problem you are facing?

Best Regards

[DataNinja777 · Accepted solution]

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,