Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |