Total/Avg column and group by assignee

Questions & Answers
1

I have a report that I want to get a total and average of how many days transpired between Tier 2 Start Time and Tier 2 End Time over a month or more. I’ve created a calculated measure called TotalAvg. This field should show the total and possibly the average for the “Time in Tier 2” field. If needed I can create a separate row/column. I simply need to understand how to add up the entries in the column “Time in Tier II” and place it on the “TotalAvg” row for each User (specific to that user which is the assignee of the ticket). So User 1 should currently show 2.32 for a Total. User 2 should show 5.42 for a TotalAvg row. Ofcourse these items will increase when more data is populated into the Tier 2 Start Time and Tier 2 End Time fields. The Time in Tier 2 column is a calculated column that simply subtracts Tier 1 Start Time from Tier 2 End Time in days. Thank You

Tier II
Tier II1403×475 64.4 KB

2

Hi @chuck3rd22

First, please check that you are importing the “Tier 2 Start Time” and “Tier 2 End Time” custom fields as both a measure and a property. This will allow you to use measures like “Issues with Tier 2 Start Time” and “Issues with Tier 2 End Time” in the calculations.

This is not really viable to create as a row, however, you can define a new calculated measure in the Measures dimension with a formula like this:

CASE WHEN
  -- check if there are issues with both Start and End times
  NOT IsEmpty([Measures].[Issues with Tear 2 Start Time])
  AND
  NOT IsEmpty([Measures].[Issues with Tear 2 End Time])
THEN
  CASE WHEN
    [Issue].CurrentHierarchyMember.Level.Name = "Issue"
  THEN
    DateDiffDays(
      [Measures].[Issue Tear 2 Start Time],
      [Measures].[Issue Tear 2 End Time]
    )
  ELSE
    Sum(
      -- filter all issues and retain only those with Start and End times
      Filter(
        Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
        NOT IsEmpty([Measures].[Issue Tear 2 Start Time])
        AND
        NOT IsEmpty([Measures].[Issue Tear 2 End Time])
      ),
      CASE WHEN
        -- if Time dimension is used then issue Tear 2 End Time will align with Time dimension selection
        [Measures].[Issues with Tear 2 End Time] > 0
      THEN
        DateDiffDays(
          [Measures].[Issue Tear 2 Start Time],
          [Measures].[Issue Tear 2 End Time]
        )
      END
    )
  END
END

This formula will count the Total time. You can get the Average time by creating a new measure, using this same formula, but changing the Sum() function to Avg() function in the formula.

Let me know if this works as expected!
​Best regards,
​Nauris