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.
Hi people,
I have the following table:
I want to show in a diagram how often the teams are working with the same Ordernumber.
There are 4 Teams: AAA, BBB, CCC, DDD
Diagram 1 should show how many OrderNumbers where used by every one of the four teams independently (Just be counted when no other team used the Number. And just be counted once, even if the number appears multiple times for that Team)
Diagram 2 should show how many OrderNumbers where used by Team Combinations, e.g. by AAA and BBB together. Or by BBB, CCC and DDD together. The Number should also be counted just once, even if it appears multiple times per team.
Thanks in advance!
@Anonymous - Seems like you could just put Ordernumber (no aggregation) in a table along with a count of Team. Then you could just filter on the count of Team.
If that is not sufficient, please post data as text.
Hi @Greg_Deckler ,
thanks for your approach! The problem that appears, when I'm doing it like this is, that it doesn't subtract numbers that appear multiple times in one team (should just be counted as "1", even if it appears 5 times for a team.
It also doesn't subtract the Ordernumbers that are used by multiple teams.
This is the data as a text
Team | Ordernumber |
AAA | 1111 |
AAA | 1111 |
BBB | 1111 |
BBB | 2222 |
AAA | 2222 |
CCC | 2222 |
DDD | 2222 |
CCC | 3333 |
DDD | 4444 |
BBB | 5555 |
CCC | 5555 |
AAA | 6666 |
AAA | 7777 |
DDD | 7777 |
AAA | 8888 |
BBB | 9999 |
Its just an example I made.
Diagram 1 would show:
AAA = 2
BBB = 1
CCC = 1
DDD = 1
Diagram 2 would show:
AAA & BBB = 1
BBB & CCC = 1
AAA & DDD = 1
AAA & CCC = 0
BBB & DDD = 0
CCC & DDD = 0
AAA & BBB & CCC = 0
AAA & BBB & DDD = 0
AAA& CCC & DDD = 0
BBB & CCC & DDD = 0
AAA & BBB & CCC & DDD = 1
@Anonymous - In that case, create a measure and do a SUMMARIZE or GROUPBY by team, then just count the rows in that summarized/grouped table. That will eliminate counting teams multiple times.
Or, use DISTINCT to get the distinct teams and count the rows (COUNTROWS) returned. Same thing as SUMMARIZE or GROUPBY but perhaps cleaner.
Thank you @Greg_Deckler
I think I made the right measure:
Measure = COUNTROWS(FILTER(DISTINCT(Ordernumber),CALCULATE(COUNTROWS(Table)) = 1))
But it doesn't show me the right numbers yet. I think it is because I didn't group the Teams for Diagram 2 yet. What would be the best way to do it? It seems like there is no right tool inside Power Bi to group them. Should I create a new table? I'm not sure how the rows should look like.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |