SUM distinct rows returning error

[Anonymous]

I'm trying to create a total transaction amount from a table (SalesLines) of the individual sales lines. 

I created a new table (Table) with the distinct transaction numbers and I tried to create a new column using the DAX formula:

=CALCULATE(SUM(SalesLines[NetAmount]),Table[TransactionNumber])

But I recieved the error: "Cannot convert value '(individual transaction number)' of type Text to type True/False."

I've seen similar threads but no solution has worked for me. Thanks for any help!

[Anonymous · Accepted solution]

Hi @Anonymous,

Create a measure instead of column using the following DAX and check if you get expected result. If not, share sample data as other post.

Measure  = CALCULATE (
    SUM (SalesLines[NetAmount] ),
    FILTER (
        SalesLines,
        CONTAINS (
           Table,
           Table[TrasactionNumber] , SalesLines[TransactionNumber]
        )
    )
)

Thanks,
Lydia Zhang

[prathy]

Hi,

Is it possible to share sample data, it could be data type issue?

Prathy