Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
@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
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
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.
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
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.
😞
Hi @Anonymous,
Can you share a new sample? I guess your old sample is misleading or you created a wrong visual.
Best Regards,
Dale
Hello @v-jiascu-msft
Here it goes
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
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
@Anonymous
Your formula should be:
SUM('Item Table'[Values]) / DISTINCTCOUNT('Group Table'[Status])
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |