How to sum by dimension

Questions & Answers
,
1

Hello,

I created a simple report to track overdue tasks by Assignee (Assignee dimension) and direction (Direction dimension).

problem
problem1108×1386 232 KB

Questions:

  1. Firstly, I can’t understand why the total of my Overdue by assignee measure (n.1 on screen) and the total of the Overdue by direction measure (n.2 on screen) diverge. (14 against 15)
  2. Secondary, I can’t figure out why the values ​​in the far right column are different even though I’m summing across the direction (n.3 on screen). There should be a value of 15 in each cell.

Overdue by direction (formula):

-- annotations.total=sum
Count(
      Filter(
        [Issue].CurrentHierarchy.Members,
        [Measures].[Was there a overdue?] = "YES"
        AND
        [Direction].CurrentHierarchyMember.Name = [Issue].CurrentMember.Get('Direction')
      )
    )

Overdue (formula):

-- annotations.total=sum
Count(
      Filter(
        [Issue].CurrentHierarchy.Members,
        [Measures].[Was there a overdue?] = "YES"
        AND
        [Direction].CurrentHierarchyMember.Name = [Issue].CurrentMember.Get('Direction')
        AND 
        Cast([Assignee].CurrentHierarchy.CurrentMember.Get('DISPLAY_KEY') AS STRING) = Cast([Issue].CurrentMember.Get('Assignee name') AS STRING)
      )
    )

Was there a overdue? (formula):

IIf(
  ([Measures].[Workdays in transition status], [Transition Status].[In progress]) > 5,
  "YES",
  "NO"
)

Thanks

2

Hi @Sergey_Opypey,

The results differ between “Overdue” and “Overdue by direction” because of issues that have changed the assignee.
The “Was there a overdue?” is based on historical events (transition status change) and considers the user who was assigned to the issue when it moved out of “In Progress” status. If the assignee at the status transition and the current assignee does not match, then this code line would leave out issues:

Cast([Assignee].CurrentHierarchy.CurrentMember.Get('DISPLAY_KEY') AS STRING) = Cast([Issue].CurrentMember.Get('Assignee name') AS STRING)

Yuo might want to remove this line from the filter criteria to get all overdue issues assigned to the user at the time.

The logic for the Count() function is current; however, you might want to describe the set of issues using another function DescendantsSet().
The updated expression might look like this:

-- annotations.total=sum
Count(
      Filter(
        --iterate through Issue dimension members at Issue level
        DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
        --filter criteria
        [Measures].[Was there a overdue?] = "YES"
         AND
         [Direction].CurrentHierarchyMember.Name = [Issue].CurrentMember.Get('Direction')
      )
    )

To get the total for each Direction, you might want to use the function VisibleRowsSet. It is the bit abstract function at first glance as it operates with dimension and member placement in the report rather than names. The principle is to gather the names of Assignees as the second dimension on rows and sum up the results.

Sum(
  --set all visible rows from the second dimension
  Generate(
    VisibleRowsSet(),
    CurrentTuple(VisibleRowsSet()).Item(1)
  ),
  --sum up values by each direction (first dimension on rows)
  [Measures].[Overdue by direction]
)

Best,
Zane / support@eazyBI.com

1 Like