Calculating Value Score for Epics with Jira Align Enterprise Insights

[#1]

If you have Jira Align Enterprise Insights and want to create a report that with the Value Score that is displayed on the Value tab of Epics, you may have been left scratching your head on how to do that.

The current_dw.[Epic Value] table within Enterprise Insights contains score value for each question answered on the Value tab for an Epic. The formula for the Value Score is relatively simple, but there is a slight catch which I will get to in a moment.

Here is the formula for Value Score:

Value Score = (Total points for question * Answer score * 0.1) / Sum of points possible for all questions the scorecard

Here is an example of a SQL query that returns the value score for Epic in a portfolio.

SELECT
     E.[Epic ID]
    ,E.[Epic Name]
    ,P.[Portfolio Name]
   ,CAST ((ROUND (AV.[Answer Value] / QP.[Total Question Points], 1)) as DECIMAL(5,1)) AS [Value Score]
FROM current_dw.Epic AS E WITH (NOLOCK)
INNER JOIN  --Calculate sum of question points multiplied by the answer score multiplied it by 0.1 for each epic
    (  
        SELECT
            EV.[FK epic ID]
            ,SUM(ROUND(((EV.[Question Points] * EV.[Answer Score]) * 0.1 ),2)) AS [Answer Value]
        FROM current_dw.[Epic Value] AS EV WITH (NOLOCK)
        GROUP BY
         EV.[FK Epic ID]
    ) AS AV ON AV.[FK Epic ID] = E.[Epic ID]
INNER JOIN -- Calculate the total points possible for the scorecards used for each epic
 ( 
        SELECT
            EV.[FK epic ID]
            ,ROUND(SUM(EV.[Question Points]) * 0.1 ,2) as [Total Question Points]
        FROM current_dw.[Epic Value] AS EV WITH (NOLOCK)
        GROUP BY
            EV.[FK Epic ID]
    ) AS QP ON QP.[FK Epic ID] = E.[Epic ID]   
INNER JOIN current_dw.Portfolio AS P ON P.[Portfolio ID] = E.[FK Portfolio ID]
WHERE
    P.[Portfolio Name] = 'Domain Marketplace Pillar' /*Replace with Portfolio Name*/
GROUP BY
    E.[Epic ID]
   ,E.[Epic Name]
   ,P.[Portfolio Name]
   ,AV.[Answer Value]
   ,QP.[Total Question Points]
ORDER BY
    [Value Score] DESC

Now to the slight catch...In Jira Align, Value Score is a decimal value with one place (tenths). Here is an example of a decimal Value Score of an Epic in the UI. 

The values in the [Question Points] and [Answer Score] columns are integers (whole numbers) and if you were to calculate the Value Score with integer values you will get a integer and no decimal.

In order to return a Value Score that matches the decimal value that is displayed in the Jira Align, the calculated value score must be converted using CAST and ROUND SQL functions. In SQL the CAST function is used to convert the calculated value score in the sample query to a decimal value with a single place (tenths). Below is an example from the sample query to convert the value in SQL.

CAST((ROUND (AV.[Answer Value] / QP.[Total Question Points], 1)) as DECIMAL(5,1)) AS [Value Score]

Below are the query results for the sample query.

Here are the value scores for the Epics in the Jira Align UI (Accessed from the Analyze button on the Value tab).

As you can see, calculating the Value Score isn't a big mystery but it does requires a little transformation of the value.