Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sum total rows that have the same expression

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's avatar
    FreemanZ
    3 years ago

    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:

     

7 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago
    try to create a measure with the code below:
     
    Test1Amount =
    CALCULATE(
        SUM(TableName[Amount]),
        TableName[LineNo] = "test1"
    )

     

    makes sense?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

       

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        try to create a measure with the code below:

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