Forum Discussion
Hierarchy Slicer Affecting Measure
Hi Anonymous ,
The function ALLEXCEPT might be more suitable in this scenario than REMOVEFILTERS. Keeps filters on specified columns while removing all other context filters. This can allow you to maintain the slicer's selection effect on the manager while removing the unwanted filter effect on the User Full Name.
VAR AverageClosedSolutionArea =
CALCULATE (
AVERAGEX ( 'Users', [m_CasesClosedCount] ),
USERELATIONSHIP ( 'Dates'[CalendarDate], 'Cases'[Case Close Date (CT)] ),
ALLEXCEPT ( 'Users', 'Users'[User Calculated Manager Name] )
)
VAR ClosedByUser =
CALCULATE (
[m_CasesClosedCount],
USERELATIONSHIP ( 'Dates'[CalendarDate], 'Cases'[Case Close Date (CT)] )
)
VAR Result =
DIVIDE ( ClosedByUser, AverageClosedSolutionArea )
RETURN
ResultBest Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous , I've shared a sample report here. It's a very simple sample that demonstrates the issue I'm seeing.
The report has three tables:
- Cases
- Dates
- Users
The report has a matrix visual UserFullName slicing the rows and Date Month/Year the columns. There are four values in the matrix:
- m_CasesClosedCount - Measure which determines the number of cases closed
- m_AverageCasesClosed - Measure which determines the average number of cases closed. This is the measure I'm having an issue with
- m_CasesClosedPercentageOfSolutionArea - This measure uses the result of m_AverageCasesClosed to determine the ratio of the cases closed by the user to the average cases closed
- VisCalc - A visual calculation I added to the matrix to show the desired results
When you open the report, the slicer should have the managers Bob and Helen selected and all of their reports:
If you view the matrix, you can do the math and see that 8.89 is the correct average for the users displayed in the matrix. You will also notice that the measure and visual calculation match.
No, deselect one of the manager's reports, say Frank. You will notice that the values change:
The m_AverageCasesClosed measure shows two different values. If you notice, all employees of Bob show a value of 8.60 and all employees of Helen show 9.25.
In fact, you can see that the averages are for employees who report to the same manager. If you add up Carl, Dan, Gail, and John's cases closed and divide by 4, you'll see it is 9.25. Similarly for Bob's reports. This is not the case if you select all of the manager's reports.
It seems that deselecting one of the reports causes an additional filter to be applied to each row that the manager must equal the employee from that row's manager. This is the issue I'm seeing and would love to be able to resolve using a measure. I was able to get the visual calculation to work, but I have many visuals where I need to show this or a similar result and would rather not have to recreate the visual calculation for each one. Thanks!