Aggregate vs. Total of grouped dimensions

[Stijn]

For a specific report I want to measure the amount of days an issue spends in average in a specific stage, measured by release.
Related to this, I want to create a higher level report and report the average amount of days spent across a set of states

For the validation I’ve been using the TOTALS feature and I’ve been looking at the individual members of the Aggregate function → though all individual members are the same for both, I don’t get the same outcome
38 36 26 7 31 11 5 7 = 161 (TOTAL)
38 36 26 7 31 11 5 7 = 133 (AGGREGATE)
(Exact sum = TOTAL, difference between both = 28)
→ How can this difference be explained?

More details on how I’ve created the report:
COLUMNS:
a) Measures: Counting average days: OK, created a custom measure as documented elsewhere in this community and turns out to work fine
b) Transition status

Observed behaviour:
Situation 1: individual states

• I select each of the transition states I’m interested in through bookmarking (Transition status)
• At the end I want to know the totals - so I’m using the TOTAL function, standard in EazyBI
• This gives me the following table:

Calling out 1 example:
38 36 26 7 31 11 5 7 = 161 (TOTAL)

Situation 2: grouping the same states through calculated measure in Transition status through the Aggregate function

Calculated measure:

Aggregate({
[Transition Status].[Solution Architecture],
[Transition Status].[Business Ready (in Grooming)],
[Transition Status].[Component Estimation],
[Transition Status].[Fix Planning],
[Transition Status].[In Development],
[Transition Status].[In Testing],
[Transition Status].[Product Accepted (in Trial)],
[Transition Status].[Country Accepted (in Roll-out)]
})

Calling out 1 example:
38 36 26 7 31 11 5 7 = 133 (AGGREGATE)

Question: When looking at the Total of the individual states and when looking at the value of the Aggregated dimension, I got different values
→ I expected that Aggregate would simply give me the sum of the individual members?

[martins.vanags]

Hello @Stijn

First of all, the “TOTAL” feature in eazyBI returns just the mathematical sum of the deepest level members (if multiple levels from the hierarchy are displayed).
For example, if you have an aggregate that is expanded, the total would shows the mathematical sum of columns. See this example:

Which is not always the same when using an Aggregate of dimension members.
When using an aggregate, the results would depend on the calculation steps (defined in the measure - in your case, “Avg workdays”).
Aggregate is not always a sum function. It can be a sum if calculated measures define so, but it can also be an aggregate of other functions.
Assuming you calculate the averages in your “Avg workdays” measure, the aggregate would be the total average of transition status members aggregated.

Let’s look at the example:
Here i’m using the measure “Average workdays in transition status” which is calculated with the following formula:

CASE WHEN [Measures].[Transitions from status] > 0 THEN
  [Measures].[Workdays in transition status] 
/
  [Measures].[Transitions from status]
END

It means the average value on the aggregate level would be calculated based on transitions from statuses (because of /[Measures].[Transitions from status]). The more statuses you aggregate, the more transitions from the statuses can be in this part of the calculation

Now let’s look at my example much closer.

Issue D1-48 created on Jun 1 2017 in “To Do” status and stayed in 0.13 wdays (~3 hours) in To DO status before status was changed to next status In Review (which is not part of my Aggregate)

Then it went back to “To Do” on Sep 4 2017 (it stayed for 67.19 wdays in “In review” status but that is outside of the scope in this aggregate)

Next, it stayed for another 1.05 wdays in status “To Do” before it was changed to “In Progress” on Sep 5 2017
Finally, it stayed in “IN Progress” for 3.94 wdays before it was “Closed” (current status of the issue) on Sep 11, 2017.

That means issue had in total three transitions from the aggregated statuses (two times from “To Do”, one time from “In progress”).
In total it stayed in both “To Do” and “In progress” statuses for 5.11 (0.13+1.05+3.94) workdays.
If you calcualte the average it is 5.11 wd by 3 transitions you get 1.7 wdays on average for the aggregate.

This is why “Total” and Aggregate member results can not be the same for your calculated measure with average function.

If you want to count just one transition per status for the issue, you can try using the following formula for average workdays in transition status calculation

CASE WHEN [Measures].[Transitions from status] > 0 THEN
  [Measures].[Workdays in transition status] /
  [Measures].[Transitions from status issues count]
END

I hope it gives you a better understanding of the difference in your case.
Martins / eazyBI

[Stijn]

Hi,

Not sure if we’re fully aligned. Maybe a bit of a shorter explanation of my observed behaviour.

In Columns, I have 2 attributes: Measures and Transition States

• On Measures, I’m using in both cases “Average Age issue count” as suggested by you, I found this in another topic → this works great.
• On transition states, I’m having 2 ways of presenting my data:

1. Bookmark a set of Transition states and present the total for these
2. For the same Transition states, use the Aggregate function (so very basic calculation)

When looking at the outcome of both, I see differences in the results which I don’t understand where these are coming from:

Using the Total function of bookmarked states

The result when using the aggregate:

I’m not sure what the Aggregate function does compared to making the total sum.

[martins.vanags]

@Stijn
Total is just a simple sum of columns for the row.
Aggregated members is aggretated result of averages which gives you the total (average of all) average, not the sum of averages.
Please read the example above where I tried to explain how Aggregate works with “Average workdays” measure.

Martins

[Stijn]

Many thanks for your help - I’ve done some of the calculations myself and together with your explanation I know what to look for. For my use case the use of the Aggregated calculation is the most appropriate one and is giving me the results I need.

The use of ‘Total’ is still relevant to us to improve our data quality and flow distribution.