I am trying to create a report showing the % of resolved incidents. I have created the following calculated field:
Resolved (%) = [Measures].[Issues resolved] / [Measures].[Issues created]
This works fine, except if the Issues resolved is empty. then the resolved field is also empty when it should be 0%. I have played around with iif and isempty with no luck so far.
Also I would like to see the trend per week. So the above % of resolved incidents for this week, last week etc. How is this possible taking into consideration all tickets, not just those created in a certain week?
I solved this one myself in the meantime with the following calculated field:
CASE WHEN
[Measures].[Issues RESOLVED] > 0
THEN
[Measures].[Issues resolved]
/
[Measures].[Issues created]
ELSE
0
END
Suggestion for product improvement: I should be able to show or exclude the 0% rather than it just being hidden
The formula with the CASE WHEN checks that the Issues created are not zero (in this case, greater than zero). Your solution checks that Issues resolved are not zero, which in some cases may lead to division by zero since the divider is Issues created.