Calculate sum of column based on primary key & distinct records from another table

[parameswaran]

Table 1: (ID Primary Key, Value)                                                         

ID (Primary Key)	Value
1	100,000
2	50,000
3	100,000
4	50,000

Table 2: (ID)

ID

1

1

1

2

2

To calculate sum of the Column: Table 1 [Value] based on the distinct values from Table 2.

Logic:
1. Create a measure on Table 1 which can return the sum of Table 1 [Value]

• Internally within the measure, create a table by joining these two tables which looks like:

ID (DISTINCT from Table 2)	Value (Table 1)
1	100,000
2	50,000

• Finally the measure will return the sum of the Column: Table 1 [Value] (like Measure on Table 1 = 150,000)

[amitchandak]

This can be done using groupby

https://docs.microsoft.com/en-us/dax/groupby-function-dax

[parameswaran]

Hi @amitchandak : thanks for your input. GROUPBY() is not appropriate here as it returns a new table not as a measure and CURRENTGROUP() can operate only on aggregate functions like SUMX.

To give some idea, here is the look of the measure I'm working but this is wrong..
Final Sum =
CALCULATE(

ADDCOLUMNS(
DISTINCT (Table 2[ID]),
"Result",
var T1_ = SELECTCOLUMNS (Table 1, "Total Value", [Value])
var T2_ = DISTINCT('Table 2'[ID])
RETURN
SUM(INTERSECT (T1_, T2_))
),
[Result]
)

NOTE: This is simplified form of the problem of the complex scenario, I'm working on. If the expected output is not in the form of a measure, it's not really helpful for me. Thanks for understanding.

[v-xuding-msft · Accepted solution]

Hi @parameswaran ,

Can you create a relationship between two tables?  If you can, I created a sample that maybe help you.

Measure = CALCULATE(SUM('Table 1'[Value]),FILTER('Table 1','Table 1'[ID (Primary Key)] in VALUES('Table 2'[ID])))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parameswaran]

Thanks @v-xuding-msft : your input helps me.

[parameswaran]

Measure = CALCULATE(SUM(Table 1[Value]), Table 1[ID] IN VALUES(Table 2[ID]))

@v-xuding-msft : this also worked for me.