cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
chirayuw Member
Member

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

themistoklis New Contributor
New Contributor

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

@Anonymous

Your formula should be:

 
SUM('Item Table'[Values]) / DISTINCTCOUNT('Group Table'[Status])
v-jiascu-msft Super Contributor
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

 

 

v-jiascu-msft Super Contributor
Super Contributor

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

Hi @Anonymous,

 

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

 

 

v-jiascu-msft Super Contributor
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.

 

Smiley Sad

v-jiascu-msft Super Contributor
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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 289 members 3,093 guests
Please welcome our newest community members: