Forum Discussion

krishna0's avatar
krishna0
Helper II
3 years ago

Problem with sum from calculated value

Hello, all!

 

I have pretty complex report based on DevOps and I am trying to get sum of weighted value for features based on user stories ended in particular period for a particular Team. Whole measure is here:

PercentMustUSClosed = 
    VAR _1 =
        MAX ( WorkItems[WorkItemId] )
    VAR _StartDate =
        CALCULATE (
            MAX ( IterationToDate[DateOnly] ),
            FILTER (
                ALL ( IterationToDate ),
                IterationToDate[CorrectStartIter] = SELECTEDVALUE ( IterDates[IterationLevel2] )
            )
        )
    VAR _ClosedDate =
        CALCULATE (
            MIN ( IterationToDate[DateOnly] ),
            FILTER (
                ALL ( IterationToDate ),
                IterationToDate[CorrectClosedIter]
                    = SELECTEDVALUE ( IterDates[IterationLevel2] )
            )
        )

    VAR _USClosed = 
    COALESCE (
            calculate(count(WorkItems[WorkItemId]),
                NOT ( ISBLANK ( FIND ( "must", LOWER ( WorkItems[ParentTag] ), 1, BLANK () ) ) ),
                FILTER (
                    ALL ( WorkItems ),
                    WorkItems[ParentWorkItemId] = _1
                        && WorkItems[State] = "Closed"
                        && WorkItems[State] <> "Removed"
                        && WorkItems[WorkItemType] = "User Story"
                        && WorkItems[StartDate] <= _StartDate
                        && IF (
                            ISBLANK ( WorkItems[ClosedDate] ),
                            1,
                            _ClosedDate <= WorkItems[ClosedDate]
                        )
                        && NOT ( ISBLANK ( WorkItems[ParentStartDateDay] ) )
                            && NOT ( ISBLANK ( WorkItems[ParentTag] ) )
                ),
                FILTER ( Areas, Areas[AreaLevel2] = "1 - DL DW" )
            ),
            0
        )
    VAR _USAll = 
    COALESCE (
            calculate(count(WorkItems[WorkItemId]),
                NOT ( ISBLANK ( FIND ( "must", LOWER ( WorkItems[ParentTag] ), 1, BLANK () ) ) ),
                FILTER (
                    ALL ( WorkItems ),
                    WorkItems[ParentWorkItemId] = _1
                        && WorkItems[State] <> "Removed"
                        && WorkItems[WorkItemType] = "User Story"
                        && WorkItems[StartDate] <= _StartDate
                        && IF (
                            ISBLANK ( WorkItems[ClosedDate] ),
                            1,
                            _ClosedDate <= WorkItems[ClosedDate]
                        )
                        && NOT ( ISBLANK ( WorkItems[ParentStartDateDay] ) )
                            && NOT ( ISBLANK ( WorkItems[ParentTag] ) )
                ),
                FILTER ( Areas, Areas[AreaLevel2] = "1 - DL DW" )
            ),
            0
        )
    VAR _Weight = DIVIDE ( _USClosed, _USAll, BLANK()) * 5
    RETURN
_Weight

 

It is long but the most important is just the last variable. It gives me desired values per row:

But now I struggle to get simple sum of these values and work with them further. I tried to do it using SUMMARIZE(), but I might be lacking some knowledge how to wrap it up correctly. This measure is filtered by context (iteration path chosen) so what I want is just to take the sum that comes out from this measure.

 

Thanks in advance for any kind of advise.

 

2 Replies