cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

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

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

11 REPLIES 11
Member

New Contributor

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

@Anonymous

`SUM('Item Table'[Values]) / DISTINCTCOUNT('Group Table'[Status])`
Super Contributor

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

Hi @Anonymous,

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

@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

Super Contributor

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

Hi @Anonymous,

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

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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.

Super Contributor

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

Hi @Anonymous,

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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.

Super Contributor

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

Hi @Anonymous,

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

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 289 members 3,093 guests
Recent signins: