Measure Not Working Without a specific Row Dimension

I created a new measure but I’m not sure what I have wrong in it because I can only get it to work with a specific row added. The measure I have calculates a certain teams utilization (shown below) and the report is to display this utilization over time dimension (monthly). I would like Time to be my only row dimension in the report but the utilization measure does not produce values without the addition of the row dimension for teams names. Hope this makes sense and someone can help me correct this measure to work with only the time dimension.

Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issues created]>0
),
CASE
WHEN [Application].CurrentMember.Name MATCHES “A&C”
THEN
CASE
WHEN [Measures].[Issue status] = “In Progress” OR [Measures].[Issue status] = “At Risk”
THEN
[Measures].[Resource #1 % Allocated created] +
[Measures].[Resource #2 % Allocated created] +
[Measures].[Resource #3 % Allocated created] +
[Measures].[Resource #4 % Allocated created] +
[Measures].[Resource #5 % Allocated created] +
[Measures].[Resource #6 % Allocated created] +
[Measures].[Resource #7 % Allocated created] +
[Measures].[Resource #8 % Allocated created] +
[Measures].[Resource #9 % Allocated created] +
[Measures].[Resource #10 % Allocated created]
END/4
END
)/100

I know I have this top portion wrong…

Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issues created]>0
),
CASE
WHEN [Application].CurrentMember.Name MATCHES “A&C”
THEN
CASE
WHEN [Measures].[Issue status] = “In Progress” OR [Measures].[Issue status] = “At Risk”

I need to identify the issues that were/are “In Progress” or “At Risk” status during each month and with those then add the measures I have for % allocated. Hope someone can help me. Thank you

@Alyssa_A

Your formula iterates through issues and then is dependant on the “Application” dimension members (to check the name vs A&C) that is why it won’t work without the “Application” dimension used in the report context (rows, page filter or columns).

Instead, you would need to iterate through Application dimension members.
Try this formula:

Sum(
Filter(
Descendants([Application].CurrentHierarchyMember,[Application].[Application]),
[Application].CurrentMember.name Matches "A&C"
AND
Aggregate({
[Status].[In Progress],
[Status].[At Risk]
},
[Measures].[Issues created]
)>0
),
[Measures].[Resource #1 % Allocated created] +
[Measures].[Resource #2 % Allocated created] +
[Measures].[Resource #3 % Allocated created] +
[Measures].[Resource #4 % Allocated created] +
[Measures].[Resource #5 % Allocated created] +
[Measures].[Resource #6 % Allocated created] +
[Measures].[Resource #7 % Allocated created] +
[Measures].[Resource #8 % Allocated created] +
[Measures].[Resource #9 % Allocated created] +
[Measures].[Resource #10 % Allocated created]
)/4
/100

Martins / eazyBI support

Thank you so much Martin, this is a great help. I now need to adjust the status portion of the formula because I need to reference issues of that application that have transitioned to In Progress/At Risk or are In Progress/At Risk during each month. For example, I need it recognize an issue that transitioned to In Progress in December and remained In Progress for January and February therefore all three of those months should be including that one issue in the calculation.

I’ve been attempting using these formulas…
[Measures].[Transitions to status],
[Transition Status].[In Progress]

[Measures].[Transitions to status],
[Transition Status].[At Risk]

[Status].[In Progress],
[Status].[At Risk]

but not succeeding at writing the correct argument for all at once.

Here you could try the following approach.

  1. create a new calculated member in “Status” dimension to aggregate two statuses

    Aggregate({
    [Status].[At Risk],
    [Status].[In Progress]
    })

  2. do the same for the “Transition Status” dimension.

  3. use this formula for your calculated measure where you point to calculated members from step1 and step2

    (
    [Measures].[Transitions to status issues count],
    [Status].[calculated member step2],
    [Transition Status].[calculated member step3]
    )

Martins / eazyBI

I’m getting an error that ‘All arguments to function {} must have same hierarchy’

Sum(
Filter(
Descendants([Application].CurrentHierarchyMember,[Application].[Application]),
[Application].CurrentMember.Name MATCHES “MA21”
AND
Aggregate({
[Measures].[Transitions to status issues count],
[Status].[Active],
[Transition Status].[Active]
},
[Measures].[Issues created]
)>0
),
[Measures].[Resource #1 % Allocated created] +
[Measures].[Resource #2 % Allocated created] +
[Measures].[Resource #3 % Allocated created] +
[Measures].[Resource #4 % Allocated created] +
[Measures].[Resource #5 % Allocated created] +
[Measures].[Resource #6 % Allocated created] +
[Measures].[Resource #7 % Allocated created] +
[Measures].[Resource #8 % Allocated created] +
[Measures].[Resource #9 % Allocated created] +
[Measures].[Resource #10 % Allocated created]
)/4
/100

Try this code:

Sum(
Filter(
Descendants([Application].CurrentHierarchyMember,[Application].[Application]),
[Application].CurrentMember.Name MATCHES “MA21”
AND
(
[Measures].[Transitions to status issues count],
[Status].[Active],
[Transition Status].[Active]
)>0
),
[Measures].[Resource #1 % Allocated created] +
[Measures].[Resource #2 % Allocated created] +
[Measures].[Resource #3 % Allocated created] +
[Measures].[Resource #4 % Allocated created] +
[Measures].[Resource #5 % Allocated created] +
[Measures].[Resource #6 % Allocated created] +
[Measures].[Resource #7 % Allocated created] +
[Measures].[Resource #8 % Allocated created] +
[Measures].[Resource #9 % Allocated created] +
[Measures].[Resource #10 % Allocated created]
)/4
/100

Martins / eazyBI

Thank you, that cleared the issue. Unfortunately, it’s still not including the tasks that I need it to be calculating. In some cases it’s only identifying the tasks that transitioned to the specific status for each month but not the tasks that are currently remaining in that status. In other cases, for another measured application, it’s missing majority of all the tasks needed for the calculation. And you’ll also notice the total values are not equaling to the individual values when you drill through.

These three tasks transitioned to the “In Progress” status in October and remain in the “In Progress” status for November, December, January, February, March but are missing for these additional months.

image

I don’t know where the total value is coming from here.

image

Hi,

If issue reached In Progress status in Oct 2020 and remains in that status today, in which month(s) would you want to see it counted?
You could change your calculation and use “Issues history” measure in the formula instead of “Transitions to status issues count”. That would change the logic for your calculated measure and it would look at issues that were in the Transition status at the end of each time period (regardless when they reached the status)

Martins / eazyBI

I would expect it to be counted for October, November, December, January, February and March. Unfortunately I’m not getting any different data using “Issues history”.

I’m trying to break it down piece by piece to see each of the tasks that should be counted for the percentage calculation portion of the MA21 measure I need but I can’t even get the task/statuses to be counted correctly….

MA21 Transition to In Progress: Drilling into October it is counting two tasks that didn’t transition to the In Progress status until November for one and December for the other. Same issue is found for the other months. I really wouldn’t expect December, February and March to be blank. I think it is displaying the counts based on the tasks that were created in each month.

MA21 Transition to At Risk & MA21 Current At Risk: Each month should be blank as none of the MA21 tasks were never or are currently in an At Risk status.

MA21 Current In Progress: Only March should have a count as we have around 7 tasks In Progress at this time

Try using the properties in “SUM” function if all resource fields are imported as properties

Sum(
Filter(
Descendants([Application].CurrentHierarchyMember,[Application].[Application]),
[Application].CurrentMember.Name MATCHES "MA21"
AND
(
[Measures].[Issues history],
[Status].[Active],
[Transition Status].[Active]
)>0
),
[Measures].[Issue Resource #1 % Allocated] +
[Measures].[Issue Resource #2 % Allocated] +
[Measures].[Issue Resource #3 % Allocated] +
[Measures].[Issue Resource #4 % Allocated] +
[Measures].[Issue Resource #5 % Allocated] +
[Measures].[Issue Resource #6 % Allocated] +
[Measures].[Issue Resource #7 % Allocated] +
[Measures].[Issue Resource #8 % Allocated] +
[Measures].[Issue Resource #9 % Allocated] +
[Measures].[Issue Resource #10 % Allocated]
)/4
/100

Martins / eazyBI