Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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?
Hi @Anonymous ,
Please try to use the updated formula to have a try. If it doesn't meet your requirement, kindly share your pbix or sample data to me, please upload your files to One Drive and share the link here.
Title Tickets Closed = CALCULATE( COUNTROWS('Tickets'), USERELATIONSHIP('Member'[Member ID],'Tickets'[Closed By]), USERELATIONSHIP('Tickets'[Date Closed],'Date'[Date]), ALL('Member'),Values('Member'[Name]) )
Regards,
Frank
Hi @Anonymous ,
Has your issue been solved? If so, kindly mark my answer as a solution to close the case. If any other question, feel free to let me know please.
Regards,
Frank