topic Sum total rows that have the same expression in DAX Commands and Tips

Sum total rows that have the same expression

Anonymous — Tue, 22 Nov 2022 07:45:01 GMT

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.

Re: Sum total rows that have the same expression

FreemanZ — Tue, 22 Nov 2022 08:09:20 GMT

try to create a measure with the code below:

Test1Amount =

CALCULATE(

SUM(TableName[Amount]),

TableName[LineNo] = "test1"

)

makes sense?

Re: Sum total rows that have the same expression

Anonymous — Tue, 22 Nov 2022 08:15:33 GMT

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

Re: Sum total rows that have the same expression

FreemanZ — Tue, 22 Nov 2022 08:22:29 GMT

try to create a measure with the code below:

Test1Amount =

CALCULATE(

SUM(TableName[Amount]),

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

)

Re: Sum total rows that have the same expression

Anonymous — Tue, 22 Nov 2022 08:29:02 GMT

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

Re: Sum total rows that have the same expression

FreemanZ — Tue, 22 Nov 2022 09:09:40 GMT

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:

Re: Sum total rows that have the same expression

Anonymous — Tue, 22 Nov 2022 09:09:16 GMT

It works! Thank you!

Re: Sum total rows that have the same expression

FreemanZ — Tue, 22 Nov 2022 09:12:58 GMT

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