- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 03:13 AM

Hello To all

Geeting my mind drained by this simple one.

Consider the following:

Group Table

Group1 State1

Group1 State2

Group1 State3

Group2 State4

Group2 State5

Group2 State6

Group3 State7

Group3 State8

Item Table

id Status Values

1 State1 10

2 State2 8

3 State1 16

4 State4 5

5 State5 12

6 State1 4

7 State7 2

8 State8 14

9 State9 3

10 State3 1

I need the average by Group1 that in this example should be: 39 (sum all values of State1, 2 and 3) and not 39/5.

How can I do this?

Thanks

## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 03:48 AM

## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 03:59 AM

## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-15-2018 10:04 PM

Hi @rmachado,

There are already two solutions. I have to say it may not be an average. It isn't 39 / 5 or 39 / 3 (3 group 1). So it isn't an average. It is the total by group.

Best Regards,

Dale

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2018 08:43 AM

@v-jiascu-msft, maybe I didn't explained correctly so the two proposed solutions migth be correct but do not solve my problem.

in fact I have this:

Item Table

id Status Values Tid

1 State1 10 1

2 State2 8 1

3 State1 16 2

4 State4 5 3

5 State5 12 4

6 State1 4 5

7 State7 2 6

8 State8 14 7

9 State9 3 8

10 State3 1 1

So I need the Average by Tid using group.

I need to add the values for each group and them make the average using the number of Tid's by group.

Does it sound clear?

Regards

## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2018 09:14 PM

Hi @rmachado,

Try this measure and also download the demo in the attachment, please.

Measure = DIVIDE ( SUM ( 'Item'[Values] ), COUNT ( 'Item'[Tid] ), 99999 )

Best Regards,

Dale

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018 07:34 AM

Hello @v-jiascu-msft

It seem I have to something diferent in my dataset because it does not work.

Here goes my table:

The other is the Group I mentioned before an it is linked to Status on this one.

I need to get the Average time by status by but combining all issueid that is in the status corresponding to the Group.

If I express myself correctly, I need first to add all time in status corresponding to the group status and the divide it by the number of issueid (distinct).

Did I explained myself correctly?

Thanks onde more.

## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-23-2018 12:52 AM

Hi @rmachado,

This one?

Measure = DIVIDE ( SUM ( 'Item'[Values] ),distinctCOUNT( 'Item'[Tid] ), 99999 )

Can you share the expected result based on your snapshot? It's better to have steps how you calculate.

Best Regards,

Dale

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-23-2018 06:28 AM

Hello @v-jiascu-msft

Still strugling with it.

But this is what I'm pursuing.

I have itens that go trough several states A,B,C,D...

I group that states in phases Phase 1 is in my case, states A,B and C

I have a measure that gives the time they spend in each state.

So, using 2 items;

Item 1

Takes 10 days from state A to B

Takes 4 days from B to C

Takes 6 days from C to D

Total days in Phase 1 = 20 days

Item 2

Takes 5 days from state A to B

Takes 4 days from B to C

Takes 1 days from C to D

Total days in Phase 1 = 10 days

So average on Phase 1 (that I want) would be 20 days + 10 days / 2 itens = 15 days

So conceptually I want the average of the sum of total days of each state in phase 1 divided by the number of itens that passed trough that phase.

Now I'm getting the average of states per phase

item 1 is giving me 20/3

item 2 is giving me 10/3

Total 30/6... per phase.

## Re: Average by group and not by number of items.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2018 09:28 PM

Hi @rmachado,

Can you share a new sample? I guess your old sample is misleading or you created a wrong visual.

Best Regards,

Dale

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*