Reply
Highlighted
Member
Posts: 140
Registered: ‎01-03-2017

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

Member
Posts: 65
Registered: ‎10-03-2018

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

New Contributor
Posts: 420
Registered: ‎04-14-2018

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

@rmachado

Your formula should be:

 
SUM('Item Table'[Values]) / DISTINCTCOUNT('Group Table'[Status])
Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

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

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

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.
Member
Posts: 140
Registered: ‎01-03-2017

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

 

 

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

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

Hi @rmachado,

 

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

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

Average-by-group-and-not-by-number-of-items

 

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.
Attachment
Member
Posts: 140
Registered: ‎01-03-2017

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. Smiley Sad

 

Here goes my table:
image.png

 

 

 

 

 

 

 

 

 

 

 

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.

 

 

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

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

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

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.
Member
Posts: 140
Registered: ‎01-03-2017

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.

 

Smiley Sad

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

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

Hi @rmachado,

 

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.