How to create a summary table

[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	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

[lbendlin]

please explain what you mean by "minus".

[POSPOS]

@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 Table

Sale 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

[lbendlin]

I used EXCEPT but that removes the exact matches - you will need to modify that to remove the difference regardless of exact matches.

[POSPOS]

@lbendlin  - Thanks for your response. We were looking for having Sales as three different measures (Sales,Sale1 and Sale2)as we cannot use a matrix as there are many more additional columns that we have and also to use these measures for further calculations.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[POSPOS]

@lbendlin  - Expected output is as provided in the post above where We will need three measures separately.

[Anonymous · Accepted solution]

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
    __result

Sales 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
    __result

Sales 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
    __result

Best Regards,
Gao
Community Support Team

If 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

[POSPOS]

@Anonymous  - When I use the above measures in another calcuated column, then I get circular dependency error.
eg: column = if( sales 2 <>0, "Y","N")

Can you please suggest on to fix this.

[Anonymous]

Hi @POSPOS ,

These two blogs explain the cause of the error and how to avoid it.

Understanding circular dependencies in DAX - SQLBI
Avoiding circular dependency errors in DAX - SQLBI

You can also consider going to build the previous table in Powerquery, see the steps in Query1.

Best Regards,
Gao
Community Support Team

If 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

[POSPOS]

@Anonymous  - This worked and saved a lot of our development effort. Much appreciated for the detailed steps. 🙂

[POSPOS]

@Anonymous  - Thank you for your response. This is very helpful. 
Could you please help me tweak the logic for Sales 2 and Sales 3 to only when the below condition is satisfied?

Sale 2 = Sale 2 from Source 1 difference Sales value from Master Table only for the Dept which is common in "Source 1" and "Master Table" and Div should not exist in "Master" Table

Sale 3 = Sale 3 from Source 2 difference Sales value from Master Table only for the Div which is common in "Source 1" and "Master Table" and Dept should not exist in Master Table