Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |