Average is correct for one team but when selecting more than one team it's showing SUM not AVG

Questions & Answers
1

Hey all,

I’m using a SQL Server datasource to track deploys and using AVG to have the average deployment frequency of the teams. It’s showing correct results for one team (pod) but when I select more than one pod or a project with more than one pod in the filter it’s showing a SUM of the averages instead of the AVG of averages:

1- For one pod (OK)

2 - For more than one pod (considerring that Pricing 1 and Pricing 2 pods have same results; the average for one or both selected should be the same result but it’s showing the sum):

image
image803×324 17.3 KB

As the example shows the average deployment freq is calculated correctly but the deployment freq by week is showing a sum of both pods.

This is the code for my Avg Deployment Freq measure (almost sure the problem isn’t there):

–annotations.group = Deployment Frequency
CASE WHEN [Time].CurrentHierarchyMember.Level.Name = ‘Month’
THEN
Avg(
ChildrenSet([Time].CurrentHierarchyMember),
[Measures].[Deployment Frequency]
)

WHEN [Time].CurrentHierarchyMember.Level.Name = ‘Week’
THEN
Avg(
ChildrenSet([Time].CurrentHierarchyMember),
[Measures].[Deployment Frequency]
)
ELSE
Avg(
ChildrenSet([Time].CurrentHierarchyMember),
[Measures].[Deployment Frequency]
)
END

Many thanks in advance, I’m getting the skills but its a journey not a sprint :slight_smile:
Best,

2

This is the result for just one pod (both have same results):

image
image742×328 14.7 KB

3

Hi @Danilo,

The average in the formula is calculating only from Time (days) - total value from all days divided with the count of days that have a value. Thus, the multiple value selection shows a larger number each day (multiplied by two) and a larger average.

I can suggest you divide the average by the count of members if there are multiple selections on pages. See example (note I also simplified the code you had previously for different Time hierarchies):

Avg(
  Descendants(
    [Time].CurrentHierarchyMember,
    [Time].CurrentHierarchy.Levels("Day")
  ), [Measures].[Deployment Frequency]
) 
/ 
CASE WHEN 
  [Label].CurrentMember.Name = "AggregatePageMembers" 
THEN Count(ChildrenSet([Pod].CurrentMember))
ELSE 1
END

Lauma / support@eazybi.com