Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Sum total rows that have the same expression

adriandumitru_0-1669102862320.png

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.

1 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:
FreemanZ_0-1669108075760.png

 

I used some simplified data like below:

FreemanZ_1-1669108173719.png

 

View solution in original post

7 REPLIES 7
FreemanZ
Super User
Super User

try to create a measure with the code below:
 
Test1Amount =
CALCULATE(
    SUM(TableName[Amount]),
    TableName[LineNo] = "test1"
)

 

makes sense?

Anonymous
Not applicable

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

 

try to create a measure with the code below:

 
Test1Amount =
CALCULATE(
    SUM(TableName[Amount]),
    TableName[DocumentID] in {"SCM234", "SCM235", "SCM236"}
)
Anonymous
Not applicable

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

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:
FreemanZ_0-1669108075760.png

 

I used some simplified data like below:

FreemanZ_1-1669108173719.png

 

Anonymous
Not applicable

It works! Thank you!

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.