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.
I have a table of data where normally the value of one of the fields would be field itself. I want to create a new table from the raw data table.
The table of data:
Program | Color | Date | Type | EntryType | QTY |
Part A | Black | 6/14/2019 | LH | Inspected | 20 |
Part A | Black | 6/14/2019 | LH | Assembled | 17 |
Part A | Black | 6/14/2019 | LH | Defect A | 3 |
Part A | Black | 6/14/2019 | LH | Defect B | 1 |
Part A | Black | 6/14/2019 | LH | Defect E | 1 |
Part A | Black | 6/14/2019 | LH | Defect F | 1 |
Part A | Black | 6/14/2019 | RH | Inspected | 40 |
Part A | Black | 6/14/2019 | RH | Assembled | 31 |
Part A | Black | 6/14/2019 | RH | Defect A | 0 |
Part A | Black | 6/14/2019 | RH | Defect B | 9 |
Part A | Black | 6/14/2019 | RH | Defect C | 4 |
Part A | Black | 6/14/2019 | RH | Defect D | 2 |
Part A | Black | 6/14/2019 | RH | Defect G | 2 |
Part A | Black | 6/14/2019 | RH | Defect H | 1 |
Part B | Black | 6/14/2019 | LH | Inspected | 100 |
Part B | Black | 6/14/2019 | LH | Assembled | 10 |
Part B | Black | 6/14/2019 | LH | Defect A | 3 |
Part B | Black | 6/14/2019 | LH | Defect B | 1 |
Part B | Black | 6/14/2019 | LH | Defect C | 1 |
Part B | Black | 6/14/2019 | LH | Defect D | 1 |
Part B | Black | 6/14/2019 | RH | Inspected | 1000 |
Part B | Black | 6/14/2019 | RH | Assembled | 200 |
Part B | Black | 6/14/2019 | RH | Defect A | 0 |
Part B | Black | 6/14/2019 | RH | Defect B | 9 |
Part B | Black | 6/14/2019 | RH | Defect C | 4 |
Part B | Black | 6/14/2019 | RH | Defect D | 2 |
Part B | Black | 6/14/2019 | RH | Defect I | 2 |
Part B | Black | 6/14/2019 | RH | Defect J | 1 |
I would like to create a new table that would show (by date) the total of assembled and total of inspected for each Program/Type combination.
Something like this:
Program | Color | Date | Type | Inspected | Assembled |
Part A | Black | 6/14/2019 | LH | 20 | 17 |
Part A | Black | 6/14/2019 | RH | 40 | 31 |
Part B | Black | 6/14/2019 | LH | 100 | 10 |
Part B | Black | 6/14/2019 | RH | 1000 | 200 |
Solved! Go to Solution.
Hello @KamelKlutch , give this a try.
Summary Table = SUMMARIZE ( 'Base Table', 'Base Table'[Program], 'Base Table'[Color], 'Base Table'[Date], 'Base Table'[Type], "Inspected", CALCULATE ( SUM ( 'Base Table'[QTY] ), 'Base Table'[EntryType] = "Inspected" ), "Assembled", CALCULATE ( SUM ( 'Base Table'[QTY] ), 'Base Table'[EntryType] = "Assembled" ) )
Hello @KamelKlutch , give this a try.
Summary Table = SUMMARIZE ( 'Base Table', 'Base Table'[Program], 'Base Table'[Color], 'Base Table'[Date], 'Base Table'[Type], "Inspected", CALCULATE ( SUM ( 'Base Table'[QTY] ), 'Base Table'[EntryType] = "Inspected" ), "Assembled", CALCULATE ( SUM ( 'Base Table'[QTY] ), 'Base Table'[EntryType] = "Assembled" ) )
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 |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |