Avg value per project

Questions & Answers
,
1

Hi!
I’m trying to count avg numbers per project (not per line)

image
image1838×940 128 KB

I looked throught manuals and examples, but i didnt found how to do it
In last column values should be
Project A - 0,57 (because its only one line in this project)
Project B - 0,37 (avg of project’s B lines)
Please help

2

maybe i can take all issues with same project as in issue and calculate all of their commited issues / complited issues?

will it work on values that shown only?

3

@lauma.cirule
Hi!
I tryed to filter my members, but after 2 days of searching and trying all options still nothing(
I hope you can tell me at least where i can search solution

4

Hi @Stas ,

Thank you for the screenshot! From there I can see you wish to divide the total coefficient with the number of Sprints. For this you can use the Avg(…) function over a set of Sprints. Please try the following formula:

Avg(
  Filter(
    [Sprint].[Sprint].Members,
    [Measures].[coefficient] > 0),
  [Measures].[coefficient]
)

The result would be similar to this:

Issues_-_eazyBI
Issues_-_eazyBI1662×582 69.5 KB

If you have time, you can check out the MDX debugging presentation I did during eazyBI community days:
Journeying with an MDX (30 min)

Lauma / support@eazybi.com

5

I think it worked!
And one more thing, it works only for visible rows (when filtering) or whole cube?
And what i need to do co count on higher levels? (custom_field/project/sprints)?

Thank you!!! <3

6

i Got different numbers of formula and pivot. Strange.

image
image1592×802 89.4 KB

And how can i get Avg coefficient through custom_field level?

coifficient is counted by formula
CASE WHEN
[Measures].[Complited issues] <= [Measures].[Commited issues]
THEN
Round([Measures].[Complited issues] /
[Measures].[Commited issues], 2)
ELSE
1
END

7

@Stas,

The formula is calculating average for all Sprints that have a coefficient greater than zero in the selected context. You can check over what set the formula is working with the following formula:

SetToStr(
Filter(
    [Sprint].[Sprint].Members,
    [Measures].[coefficient] > 0)
)

Possibly you wish to do the average of Sprint’s coefficient over set of Sprints that have committed issues > 0?

Avg(
  Filter(
    [Sprint].[Sprint].Members,
    [Measures].[Commited Issues] > 0),
  [Measures].[coefficient]
)

Lauma / support@eazybi.com

8

Got it!
But still have same 2 questions

1 And how can i get Avg coefficient through custom_field level only (ignoring other levels)?

Avg(
  Filter(
    [customfield_dimention].Members,
    [Measures].[Commited Issues] > 0),
  [Measures].[coefficient]
)

Not working, still chop numbers by sprint

2 How make formula count only visible rows?

9

2 How make formula count only visible rows?
that worked

sum(
filter(
Generate(VisibleRowsSet(),
[Sprint].[Sprint].currentmember),
[Measures].[Commited issues] > 0),
[Measures].[Commited issues]
)

1st question still important

1 Like
10

DefaultContext(
([customfield_dimention].CurrentMember, [Measures].[previously created measure])
)

Seems works

1 Like