Consolidate value wrong in Transition Status Dimension

Questions & Answers
1

Hi, I’m trying to generate a flow efficiency chart and I have issues with consolidated values as we can see below:

image
image1169×608 50.5 KB

The “flow Efficiency - Managed Status” column is the result of division of “In progress Status column” by “Managed Status” column. The calculation is a calculated member formula placed inside Transition Status dimension:

[Transition Status].[In Progress Status]/
[Transition Status].[Managed Status]

If we check in the ticket level, the formula is ok, but in the “consolidated” line (per project and All issues) show a 232% (really a wrong value in my point of view). I think that I’m doing something wrong here, but I cannot find why… Can someone tell show me how to have the correct value?

See below how I calculated the all columns:
Waiting Status
Aggregate({
[Transition Status].[Waiting Dev Analysis],
[Transition Status].[Waiting Dev Code],
[Transition Status].[Waiting Dev Package],
[Transition Status].[Waiting Dev Review],
[Transition Status].[Waiting Dev QA Test],
[Transition Status].[Waiting Dev QA Review]
})
In Progress Status
Aggregate({
[Transition Status].[Dev Analyzing],
[Transition Status].[Dev Coding],
[Transition Status].[Dev Reviewing],
[Transition Status].[Dev QA Testing],
[Transition Status].[Dev QA Reviewing],
[Transition Status].[In Progress],
[Transition Status].[In Test]
})
Flow Efficiency - All Status
[Transition Status].[In Progress Status]/
[Transition Status].[All Status]
Flow Efficiency - Managed Status
[Transition Status].[In Progress Status]/
[Transition Status].[Managed Status]
All Status
[Transition Status].[All Transition Statuses]
Managed Status
[Transition Status].[Waiting Status]+
[Transition Status].[In Progress Status]

Thank you!

Hugo!

2

Hi Hugo

The total calculations are not correct, as, for arithmetical calculations, you may want to create calculations in Measures . In other dimensions (Transition Status etc.) calculations should be used only to aggregate members as described here:
https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members/calculated-members-in-other-dimensions
In your case, Waiting Status and In Progress Status are correct, while flow efficiency calculations should be created in Measures.

You may want to reorganise the report a bit and create several calculations in Measures, based on Transition status calculated members that aggregate statuses by groups, as well as standard measure “Workdays in transition status”.

  1. Create measures in Measures to calculate workdays in transition status for each of transition status groups:

Example for Waiting in status ( let’s name it “Workdays in Waiting status”) and create a similar measure for In Progress Status .

([Measures].[Workdays in transition status],
 [Transition Status].[Waiting Status])

For Manager status, measure would be a sum of measures Workdays in Waiting status and Workdays in In Progress status

[Measures].[Workdays in Waiting status] + [Measures].[Workdays in In Progress status]

For All status, simply use default measure “Workdays in transition status” in the report.

To get the percentage calculations, please, create two more measure (in Measures) with a division of measures that calculates workdays in specific statuses (or all statuses)

Example for Flow Efficiency - All Status

[Measures].[Workdays in In Progress status]
/
[Measures].[Workdays in transition status]

And set decimal percentage as formatting.

  1. Put the report together: use all newly created measures in columns; you won’t need to add in the report Transition status dimension at all, as it is already incorporated in the calculations.

Best,
Ilze / support@eazybi.com

3

Works fine now! thank you for your detailed explanation! :smiley: