Sum total rows that have the same expression

[Anonymous]

I have DocumentID, LineNo and Amount as columns.

I want to sum all DocumentID's Amount that have a specific expression on LineNo. The result from the picture should be the sum of blue values, because only does DocumentIDs have "test1" on LineNo.

[FreemanZ]

try to create a measure with the code below:

 

Test1Amount =

CALCULATE(

    SUM(TableName[Amount]),

    TableName[LineNo] = "test1"

)

makes sense?

[Anonymous]

No, this measure will sum only rows with "test1". This will sum only green rows, I need to sum blue rows.

[FreemanZ]

try to create a measure with the code below:

 

Test1Amount =

CALCULATE(

    SUM(TableName[Amount]),

    TableName[DocumentID] in {"SCM234", "SCM235", "SCM236"}

)

[Anonymous]

This is just a sample. I have millions of documents and I don't know which one of them has "test1" on LineNo.

[FreemanZ · Accepted solution]

Aha, then try this:

Measure =

VAR IDList =

CALCULATETABLE(

    VALUES(TableName[DocumentID]),

    TableName[LineNo]="test1"

)

RETURN

CALCULATE(

    SUM(TableName[Amount]),

    IDList

)

i tried and it worked like this:

I used some simplified data like below:

[Anonymous]

It works! Thank you!

[FreemanZ]

Only in the last chat, came I to understand your expection. The requirement clarification is soooo important.