Forum Discussion
How to create a summary table
Hi All,
I have three tables (Master, Source 1, Source 2).
Based on the tables, my requriement is to create a summary table:
Sample file -Download
Master Table:
| Dept | Div | Sales |
| Dept A | Div 1 | 500 |
| Dept A | Div 2 | 620 |
| Dept A | Div 3 | 800 |
| Dept B | Div 1 | 700 |
| Dept B | Div 11 | 560 |
| Dept C | Div 5 | 800 |
Source 1:
| Dept | Div | Sales |
| Dept A | Div 1 | 700 |
| Dept A | Div 2 | 700 |
| Dept A | Div 3 | 900 |
| Dept A | Div 5 | 205 |
| Dept A | Div 11 | 650 |
| Dept B | Div 1 | 900 |
| Dept B | Div 11 | 600 |
| Dept B | Div 2 | 200 |
| Dept C | Div 5 | 900 |
Source 2:
| Dept | Div | Sales |
| Dept A | Div 1 | 500 |
| Dept A | Div 2 | 700 |
| Dept A | Div 3 | 900 |
| Dept A | Div 2 | 620 |
| Dept A | Div 11 | 800 |
| Dept B | Div 1 | 700 |
| Dept B | Div 11 | 900 |
| Dept C | Div 5 | 800 |
| Dept E | Div 3 | 500 |
Expected Output:
Requirement is to create a summary table with all the data from the three tables.
Dept = Union of all values from Master, Source 1, Source 2 tables
Div = Union of all values from Master, Source 1, Source 2 tables
Sales 1= Sales value from Master Table
Sale 2 = Sale 2 from Source 1 minus Sales value from Master Table
Sale 3 = Sale 3 from Source 2 minus Sales value from Master Table
Can someone please suggest on how to achieve this?
Thank you
Hi POSPOS ,
Please create a new table :
And 3 measures:
Sales 1 = VAR __curr_dept = MAX('Table'[Dept]) VAR __curr_div = MAX('Table'[Div]) VAR __result = IF( HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Master'[Sales]),'Master'[Dept]=__curr_dept&&'Master'[Div]=__curr_div), SUM('Master'[Sales])) RETURN __resultSales 2 = VAR __curr_dept = MAX('Table'[Dept]) VAR __curr_div = MAX('Table'[Div]) VAR __sales = IF(HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Source 1'[Sales]),'Source 1'[Dept]=__curr_dept, 'Source 1'[Div]=__curr_div), SUM('Source 1'[Sales])) VAR __result = __sales - [Sales 1] RETURN __resultSales 3 = VAR __curr_dept = MAX('Table'[Dept]) VAR __curr_div = MAX('Table'[Div]) VAR __sales = IF( HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Source 2'[Sales]),'Source 2'[Dept]=__curr_dept, 'Source 2'[Div]=__curr_div), SUM('Source 2'[Sales])) VAR __result = __sales - [Sales 1] RETURN __resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
11 Replies
please explain what you mean by "minus".
lbendlin I was referrning to "Minus" as "Difference"/"Substraction" between two values.
Dept = Union of all values from Master, Source 1, Source 2 tables
Div = Union of all values from Master, Source 1, Source 2 tables
Sales 1= Sales value from Master TableSale 2 = Sale 2 from Source 1 difference Sales value from Master Table
Sale 3 = Sale 3 from Source 2 difference Sales value from Master Table
I used EXCEPT but that removes the exact matches - you will need to modify that to remove the difference regardless of exact matches.
