Hello,
Since 2 days I try to find the solution of my problem and Iβm going mad
For each opened issues, I would like to calculate
RESULT = [Measures].[value1] - [Measures].[value2] - [Measures].[value3] and if the result is <0 then the result of the issue =0.
Ex:
|Value1|Value2|Value2|Result the issue|
| --10-- |-----15-- |-----0β|0|
|β10-- |----- 5-----|-----0β|5 |
|β10-- |------0-----|-----5β| 5 |
|β10β|------0-----|----12β| 0 |
------------------------------------- 10 |<-- Value I would like to calculate (sum of results : 5+5)
In this case, you would need the separate approach for total calculations.
Try this code when creating a calculated measure (with integer format).
NonZero(
SUM(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Value1]>0
AND
[Measures].[Issues created]>0
),
CASE
WHEN ([Measures].[Value1] - [Measures].[Value2] - [Measures].[Value3]) < 0
THEN 0
ELSE [Measures].[Value1] - [Measures].[Value2] - [Measures].[Value3]
END
)
)
Here I am expecting that βValue1β, βValue2β, βValue3β all are numeric Jira custom fields imported as properties and measures in eazyBI cube