How to create a summary table with dynamic fields

[POSPOS]

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	Sales	Emp ID	Div
Dept A	500	Emp1	Div 1
Dept A	620	Emp2	Div 2
Dept A	800	Emp3	Div 3
Dept B	700	Emp4	Div 1
Dept B	560	Emp5	Div 11
Dept C	800	Emp6	Div 5

Source 1:

Dept	Div	Sales	Emp
Dept A	Div 1	700	Emp1
Dept A	Div 2	700	Emp2
Dept A	Div 3	900	Emp3
Dept A	Div 5	205	Emp4
Dept A	Div 11	650	Emp5
Dept B	Div 1	900	Emp6
Dept B	Div 11	600	Emp7
Dept B	Div 2	200	Emp8
Dept C	Div 5	900	Emp9

Source 2:

Dept	Div	Sales	Emp
Dept A	Div 1	500	Emp1
Dept A	Div 2	700	Emp2
Dept A	Div 3	900	Emp3
Dept A	Div 2	620	Emp4
Dept A	Div 11	800	Emp5
Dept B	Div 1	700	Emp6
Dept B	Div 11	900	Emp7
Dept C	Div 5	800	Emp8
Dept E	Div 3	500	Emp9

Requirement

1) 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

Note: There are around 20 columns in the actual report. Based on the Dept and Div , other fields eg: emp should be filtered and populated Sale 1, Sale 2, Sale 3  with the same criteria.

Can someone please suggest on how to achieve this?

Thank you

[Ashish_Mathur]

Hi,

Create a Many to One relationship from the Emp columns of the 2 source tables to the Emp column of the Master table.  Create a 4th table with unique values of all Dept's.  Create a Many to One relationship from the Dept column of the Master table to this 4th table.  To your visual, drag Emp from the Master table and Dept from the 4th table. 

[POSPOS]

@Ashish_Mathur  - Emp columns cannot be joined as there is an existing join between Depts/Div. Sample pbix is avaialble in the post.