Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New column returns sum only when ID is specific

Hello Power BI experts 🙂

 

I ran into a problem, seems easy, but I can't figure it out. 

 

I am creating a new column, a simple SUM (from another column), but I only want this sum to appear only on the row with a specific ID.

 

Heres is an example.

Let's say that I want to SUM a certain column from Table 2, and I only want it to appear in Table 1 when ID =2 

 

My table 1:

ID   Column I need

1          

2         SUM('Table2'[Column])

3         

4         

 

Does anyone know how to do this? 

 

I tried doing a calculate and then filter it to this specific ID but it returns with errors.

 

Thank you for your help 🙂

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    You can do like this.

    1. Create a column in Table2.

     

    Column = 
CALCULATE(
    SUM(Table2[Value]),
    FILTER(
        Table2,
        Table2[ID] = EARLIER( Table2[ID]) && Table2[ID] = 2
    )
)

     

    2. Create a column in Table 1.

     

    Column = 
LOOKUPVALUE(
    Table2[Column],
    Table2[ID], Table1[ID]
)

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous

    You can indeed use the calculate function here.

    The Column would look like this:

    Column =
VAR CurrentID = 'Table1'[ID]
RETURN
    CALCULATE (
        SUM ( 'table2'[column] ),
        FILTER ( table2, 'table2'[ID] = CurrentID )
    )

     

     

    Hope it helps,

     

    Kind regards
    Joren Venema

    Data & Analytics Consultant
    If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , try like

    if there is a join
    new column = sum(RELATED('Table2'[Column]))

    wthout using related
    new column = sumx(FILTER(table2,table2[Id]=table1[ID] && table2[Id]=2),'Table2'[Column])

     

    You can change the filter condition as per need

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You can do like this.

    1. Create a column in Table2.

     

    Column = 
CALCULATE(
    SUM(Table2[Value]),
    FILTER(
        Table2,
        Table2[ID] = EARLIER( Table2[ID]) && Table2[ID] = 2
    )
)

     

    2. Create a column in Table 1.

     

    Column = 
LOOKUPVALUE(
    Table2[Column],
    Table2[ID], Table1[ID]
)

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.