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, I have Table 1 in Power BI shown below
Table 1 | |
Group | Number of Candidates |
A | 4 |
B | 3 |
C | 6 |
BC | 8 |
AB | 12 |
D | 5 |
Total | 38 |
Group and Number of Candidates are Fields. I want to get Table 2 shown below
Table 2 | |
Group | Number of Candidates |
A | 16 |
B | 23 |
C | 14 |
D | 5 |
Total | 58 |
So the Group field has been put into bins, for example Group A (16 candidates) in Table 2 consist of all occurrences of A in Table 1 which is A (4 candidates) and AB (12 candidates).
Is there any way this can be done, this would be very useful.
Solved! Go to Solution.
Hi @Silverfeet,
Two solutions for your reference:
Solution1
You should manually create a Table2 with one single column that lists all unique group values.
Then, add a caculated column in Table2.
Number of Candidates = CALCULATE ( SUM ( Table1[Number of Candidates] ), FILTER ( Table1, NOT ( ISERROR ( FIND ( EARLIER ( Table2[Group] ), Table1[Group] ) ) ) = TRUE () ) )
Solution2
In Query Editor mode, duplicate Table1 first. Split the [Group] column in duplicated table 'Table1(2)'.
Save above changes. In Data view mode, new a calculated table with below formula:
Table1(3) = SUMMARIZE ( FILTER ( UNION ( SELECTCOLUMNS ( 'Table1 (2)', "Group", 'Table1 (2)'[Group.1], "Number", 'Table1 (2)'[Number of Candidates] ), SELECTCOLUMNS ( 'Table1 (2)', "Group", 'Table1 (2)'[Group.2], "Number of Candidates", 'Table1 (2)'[Number of Candidates] ) ), [Group] <> BLANK () ), [Group], "Number of Candidates", SUM ( 'Table1 (2)'[Number of Candidates] ) )
Best regards,
Yuliana Gu
Hi @Silverfeet,
Two solutions for your reference:
Solution1
You should manually create a Table2 with one single column that lists all unique group values.
Then, add a caculated column in Table2.
Number of Candidates = CALCULATE ( SUM ( Table1[Number of Candidates] ), FILTER ( Table1, NOT ( ISERROR ( FIND ( EARLIER ( Table2[Group] ), Table1[Group] ) ) ) = TRUE () ) )
Solution2
In Query Editor mode, duplicate Table1 first. Split the [Group] column in duplicated table 'Table1(2)'.
Save above changes. In Data view mode, new a calculated table with below formula:
Table1(3) = SUMMARIZE ( FILTER ( UNION ( SELECTCOLUMNS ( 'Table1 (2)', "Group", 'Table1 (2)'[Group.1], "Number", 'Table1 (2)'[Number of Candidates] ), SELECTCOLUMNS ( 'Table1 (2)', "Group", 'Table1 (2)'[Group.2], "Number of Candidates", 'Table1 (2)'[Number of Candidates] ) ), [Group] <> BLANK () ), [Group], "Number of Candidates", SUM ( 'Table1 (2)'[Number of Candidates] ) )
Best regards,
Yuliana Gu
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 |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |