Average by group and not by number of items.

[Anonymous]

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

[v-jiascu-msft]

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

[Anonymous]

@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

[v-jiascu-msft]

Hi @Anonymous,

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

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

Best Regards,
Dale

[Anonymous]

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.

[v-jiascu-msft]

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

[Anonymous]

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.

😞

[v-jiascu-msft]

Hi @Anonymous,

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

Best Regards,
Dale

[Anonymous]

Hello @v-jiascu-msft

Here it goes

Group by average

In this case I have 7,5 as average on each group on in fact I was looking for 45 divide by 2 ElementsID (1 and 2). 
Sum of all timeInstatus in Group1 divided by the number of elements in my case 2.

Thanks

[v-jiascu-msft]

Hi @Anonymous,

Please try this one which is similar to the last one.

Measure = 
DIVIDE ( SUM ( Elements[TimeInStatus] ), distinctCOUNT ( 'Elements'[ElementID] ), 99999 )

Best Regards,
Dale

[themistoklis]

@Anonymous

Your formula should be:

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

[Anonymous]

See sample

https://ufile.io/a60o6