Create a SUM measure that sums conditionally (based on a value in another column)

[Anonymous]

Hello,

I'm new in PowerBI so looking for some assist. Let's say I've table called DATA :

GL Account no.	Amount
6001110	-2663,67
5008310	-2434,96
5008110	-1530
6001310	-1080
6001152	-243,34
6008152	-56,66
6008152	243,34
6008110	616,33
6008310	945
6008310	1080
6001110	1530
6008110	2663,67
6001110	-62
6001110	-50,66
6001110	40
6001110	50,66
6001110	50,66
6001110	62
6001310	135
5001310	-550
6008110	-120
5001110	-6
5001110	-1466
5001310	-64
8001310	-63,72

And another table called GL_COSTS

GL Account no.

6001110

6001152

6001310

How can I sum values only for those GL Account no. which are in GL_COSTS table?

Thanks in advance!

[Zubair_Muhammad]

@Anonymous

If the two tables are related by Account No. you can use

Measure =
CALCULATE ( SUM ( Data[Amount] ), GL_COSTS )

if the tables are not related...you can try

Measure 2 =
CALCULATE (
    SUM ( Data[Amount] ),
    FILTER ( Data, Data[GL Account no.] IN VALUES ( GL_COSTS[GL Account No.] ) )
)

[Anonymous]

These two tables are related by Account No. so I used your first formula but unfortunately it does not give any result 😞

I created this measure in DATA table, is it ok?

[v-yuta-msft · Accepted solution]

Hi samto22,

Have you built a relationship between the two tables? Click "manage relationship" and set many-to-one relationship as below:

 

Then you can create a measure using both of DAX formula like below:

Measure = 
CALCULATE (
    SUM ( Data[Amount] ),
    FILTER(DATA, DATA[GL Account no.] = RELATED(GL_COSTS[GL Account no.])
))

Measure = 
CALCULATE (
    SUM ( Data[Amount] ),
    GL_COSTS
)

  

Regards,

Jimmy Tao