Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

SUM distinct rows returning error

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!

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

2 Replies

  • prathy's avatar
    prathy
    Advocate III
    9 years ago

    Hi,

     

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

     

    Prathy

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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