Finding difference between two Tables generated from one table using DAX

Question

I want to find the difference between the sum of all the common elements between 2 tables.

We have one large table (Table A)

You can observe that neither the id, nor the date can be used individually as a unique identifier.

Based on certain filters(one of it is date), we have to create 2 tables:

We then have to find the difference between the sum of all the common elements.

Table 1 and 2 may not be real tables and could be simply virtual tables (defined using VAR) declared in order to create the measure.

I have tried the following code:

Difference = VAR Table1= ADDCOLUMNS('TableA', "id", CALCULATE(VALUES('TableA'[Id]), ALL('Date'),ALL('TableA'), USERELATIONSHIP('Date'[As of Date], Previous_Date[Previous_Date]),USERELATIONSHIP('Date'[As of Date], 'TableA'[Date])), "Value", 'TableA'[Value] )

VAR Table2= ADDCOLUMNS('TableA', "id", CALCULATE(VALUES('TableA'[Id]),ALL('TableA'), USERELATIONSHIP('Date'[As of Date], 'TableA'[Date])),"Value", 'TableA'[Value] )

VAR abc = CALCULATE(SUMX(Table1,IF(VALUES('TableA'[Id]) IN Table1 && VALUES('TableA'[Id]) in Table2,Table1[Value])) )

VAR pqr = CALCULATE(SUMX(Table2,IF(VALUES('TableA'[Id]) IN Table1 && VALUES('TableA'[Id]) in Table2,Table2[Value])) )

RETURN abc-pqr

Student of the Digital World · Accepted Answer · 2019-07-03 10:58:55Z

0

Pre-requisite :- Change the Data Type of your id columns to Text in both the tables.

Then, Create this Calculated Column in Table1 :-

Common_NotCommon_Table1 = 
         Var out1 = LOOKUPVALUE(Table2[id],Table2[id],Table1[id])
         Var out2 = IF(out1 <> "", "Common","Not-Common")
return out2

Then Create this Calculated Column in Table2 Likewise :-

Common_NotCommon_Table2 = 
         Var out1 = LOOKUPVALUE(Table1[id],Table1[id],Table2[id])
         Var out2 = IF(out1 <> "", "Common","Not-Common")
return out2

Then Create the two Measures to find the Sum - this can be created in any of your tables.

Sum_Common_Table1 = CALCULATE(SUM(Table1[corresponding value]), FILTER(Table1, Table1[Common_NotCommon_Table1] = "Common")) 

Sum_Common_Table2 = CALCULATE(SUM(Table2[corresponding value]), FILTER(Table2, Table2[Common_NotCommon_Table2] = "Common"))

Then Create the Difference Measure in any one of the Table:-

Common_Diff = [Sum_Common_Table2] - [Sum_Common_Table1]

The Output as Expected looks like,

Kindly accept the answer if it helps and let me know, how it works for you.

answered Jul 3, 2019 at 10:58

Student of the Digital World

3,05122 gold badges74 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ameya Bhave Over a year ago

Thanks for the reply! Is there any way I can do all the above processes in a single measure field?

Collectives™ on Stack Overflow

Finding difference between two Tables generated from one table using DAX

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related