Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alexandrast
Frequent Visitor

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

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.

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

something like this?

lbendlin_0-1680310440665.png

 

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

something like this?

lbendlin_0-1680310440665.png

 

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors