I am attempting to generate a report that shows the average value of one person's daily production vs the average daily production of all others with the same job title.
I am able to determine the value of the individual or for the group. However, when I use a slicer to choose the a person. The value that shows the average for that group title changes. I have attempted to use allexcept() and all() neither seem to have any effect on the value. I cannot specify the group and have different measures as the report will allow managers to review employees with different job titles.
There are two tables involved:
tickets - which lists the individual tickets with the following information: ticket number, task, entered by, closed by, date entered, date closed
members - which lists the people who work with tickets: memberid, name, title
To determine the members tickets closed - I used the following measure:
Con Tickets Closed =
CALCULATE(
COUNTROWS('Tickets'),
USERELATIONSHIP('Member'[Member ID],'Tickets'[Closed By]),
USERELATIONSHIP('Tickets'[Date Closed],'Date'[Date])
)To determine the average daily tickets closed by a person, I used the following measure:
Con Avg. Daily Closed =
VAR contbl=SUMMARIZE('Tickets',Tickets[Closed By],Tickets[Date Closed],"tkts",[Con Tickets Closed])
RETURN
AVERAGEX(contbl,[tkts])I originally tried to use make a new measure, which was the same above but I added
var titletkts = CALCULATE ( "tkts",ALL('Member'[Name])
then modified the averagex
When that did not work, I took a step back to get just the ticket count of all of the people with the same title when a person is selected with a slicer.
Title Tickets Closed =
CALCULATE(
COUNTROWS('Tickets'),
USERELATIONSHIP('Member'[Member ID],'Tickets'[Closed By]),
USERELATIONSHIP('Tickets'[Date Closed],'Date'[Date]),
ALL('Member'[Name])
) I also tried variations of allexcept('member'[title]) - etc.
I am still not able to get the number of tickets closed by an individual and in a seperate visual, the number of tickets closed by all of the people with the same title, when selecting the the individual's name from a slicer that uses 'member'[name].
Thoughts?
