I pull Jira data for a development team to measure team productivity. I have several tables for that, as well as a designated Calendar(Date) table for date relationships across tables. On one of the pages in my report, I have a histogram displaying the average number of tickets completed per developer per month using the following measure:
Integrated Per Dev =
var maxdate = CALCULATE(
MAX('JIRA-Issues'[Integrated Date]),
USERELATIONSHIP('Calendar'[Date], 'JIRA-Issues'[Integrated Date]),
'JIRA-Issues'[Integrated Date]
)
var devCount = CALCULATE(
COUNTA('JIRA-Developers'[Developer]),
'JIRA-Developers'[Start Date] < maxdate,
'JIRA-Developers'[End Date] >= maxdate
)
return
CALCULATE(
SUM('JIRA-Issues'[Is Integrated]) / devCount,
USERELATIONSHIP('Calendar'[Date], 'JIRA-Issues'[Integrated Date])
)
This works as expected using a relative date slicer for Calendar[Date] to filter the visual. On the other hand, if I add a dropdown slicer for a field like 'JIRA-Issues'[Team] which groups the tickets by a subset of developers, then the visual displays values based on the total number of tickets in that month rather than the filtered total.
* E.g. The value for March unfiltered equals 3.9 and there are two "Teams"; filtering by one team equals 2.0 and the other eqauls 1.9
Since these add up to the total (as do all other months) it appears that "SUM('JIRA-Issues'[Is Integrated])" is returning the total number of tickets rather than the number of tickets integrated by the target Team. It appears that my filter context is not what I'm expecting, so I just need to know what mistake I'm making.
@V-lianl-msft Sorry I read your reply and saw that I couldn't upload files directly, so this got buried in my to-dos. I've uploaded an .xlsx and a sample .pbix using the data here. The visuals I added should quickly show the issue I'm seeing.
WeTransfer Sample Data
As you said, the result of measure is affected by the current context. It is difficult to analyze the issue only by description. Can you share a sample pbix after deleting sensitive data?
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
User | Count |
---|---|
129 | |
61 | |
55 | |
54 | |
43 |
User | Count |
---|---|
128 | |
60 | |
57 | |
56 | |
50 |