DAX percentage over hierarchy - my column totals are not respecting the column filter context

alexandrast — Tue, 04 Apr 2023 15:29:39 GMT

I have a matrix with a row hierarchy - the parent field is Team Leader and the child is Agent. The values are [Number of Coachings] and the columns are [YearMonth]. All my data is in a single table so there are no data model relationships.

I have created a measure to capture the percentage of coachings over hierarchy - when it is an agent row I want the percentage of the total coachings of that agent, when it is a team leader I want the percentage of all the team leaders, but only for that YearMonth column. My formula is calculating the percentage of all columns and I cannot figure out how to modify it to make it functional.

The measure formula - (Variable X contains all my report slicers)

% Parent By Agent = 

VAR x = ALLEXCEPT(Query1, Query1[Team Leader],Query1[JobStatus], Query1[DepartmentGroup], Query1[DaysSinceHire], Query1[NewAgent_Current], Query1[YearMonth])

VAR Scope_Agent = CALCULATE(COUNT(Query1[Feedback_Title]),x,Query1[Team Leader] = VALUES(Query1[Team Leader]))

VAR Scope_TL = CALCULATE(COUNT(Query1[Feedback_Title]),ALLSELECTED(Query1))

RETURN

SWITCH(

TRUE()

// Agent rows  
ISINSCOPE(Query1[Agent]), DIVIDE(COUNT(Query1[Feedback_Title]), Scope_Agent), 

//Team Leader rows
ISINSCOPE(Query1[Team Leader]), DIVIDE(COUNT(Query1[Feedback_Title]), Scope_TL), 

//Grand Total row
, DIVIDE(COUNT(Query1[Feedback_Title]), Scope_TL))

It works when I filter on 1 YearMonth column, but if I select more than 1 YearMonth values, the percentage is calculated over all columns.

How can I modify so that the formula calculates on the column filter context for all hierarchy levels? I want each level of the hierarchy for each YearMonth column to add up to 100%. Currently the child percentage adds up to 100% per column but the parent percentage is calculating on the time period in the slicer, not on the matrix column.

Re: DAX percentage over hierarchy - my column totals are not respecting the column filter context

lbendlin — Sat, 01 Apr 2023 00:54:06 GMT

something like this?

Re: DAX percentage over hierarchy - my column totals are not respecting the column filter context

alexandrast — Mon, 03 Apr 2023 14:14:18 GMT

It works, thank you so much!
Such a simple statement, too.

Would it be possible to explain why this works? I don't understand how REMOVEFILTERS does not remove the slicer or report-level filters, and how it does not remove the column context.

Re: DAX percentage over hierarchy - my column totals are not respecting the column filter context

lbendlin — Mon, 03 Apr 2023 14:46:17 GMT

I used REMOVEFILTERS only against the specified column. All other filters remain intact. That is a technique sometimes called "filtering up".

topic DAX percentage over hierarchy - my column totals are not respecting the column filter context in DAX Commands and Tips

DAX percentage over hierarchy - my column totals are not respecting the column filter context

Re: DAX percentage over hierarchy - my column totals are not respecting the column filter context

Re: DAX percentage over hierarchy - my column totals are not respecting the column filter context

Re: DAX percentage over hierarchy - my column totals are not respecting the column filter context