Hello,
We are trying to set up a scorecard structure for our teams. As seen in the attached image, each Board ID represents a specific team, and the scorecard consists of Themes, Objectives, and Metrics with their respective Weight, Minimum Target (TMIN), Target (TTGT), and Maximum Target (TMAX) values for that team.
I have tried several ways to import this data (e.g., importing as measures, using properties, mapping at different levels), but I haven’t been able to solve the following issue:
Since calculations will be made at the Sprint level, I need to retrieve the threshold values (Weight, Min, Max) for the corresponding metric (e.g., Velocity TVC+) based on the Board ID.
My intended approach is to reference the Board level from Sprint and fetch the Weight, Min, Max values for the corresponding Metric in that Board. However, I haven’t been able to properly implement this in eazyBI.
Could you guide me on how to model this correctly in eazyBI?
Velocity TVC+ for TTGT
CASE
WHEN [Sprint].CurrentHierarchyMember.get("Metric Name") = "Velocity TVC+"
THEN [Sprint].CurrentHierarchyMember.Parent.get("TTGT")
ELSE NULL
END
sample score card
IIF(
IsEmpty([Measures].[Velocity]),
NULL,
-- If Current Velocity is greater than the Max,
IIF(
[Measures].[Velocity] > [Measures].[Max - Velocity],
5,
-- If Current Velocity is between Target and Max:
IIF(
[Measures].[Velocity] >= [Measures].[Target - Velocity],
3 +
(
([Measures].[Velocity] - [Measures].[Target - Velocity]) * 2
/
([Measures].[Max - Velocity] - [Measures].[Target - Velocity])
),
-- If Current Velocity is between Min and Target:
IIF(
[Measures].[Velocity] > [Measures].[Min - Velocity],
1 +
(
([Measures].[Velocity] - [Measures].[Min - Velocity]) * 2
/
([Measures].[Target - Velocity] - [Measures].[Min - Velocity])
),
-- If Current Velocity is less than or equal to Min:
1
)
)
)
)