Sum values of a column with two conditions

[webportal]

Tables A and B below are related by ID:

I want to add the "Value" column per ID - with the following conditions:

1-Valid = TRUE

2-Value >0 on aggregate.

That is,  I'm expecting the results:

123 = -158, so it will not be added

456  is FALSE, so it will not be added

789 = 177. It will be added.

So, the end result is 177.

[webportal]

@selimovd 

Here's the link to the PBIX.

[selimovd · Accepted solution]

Hey @webportal ,

thank you very much, providing the file helped a lot.

I found my error, I filtered "Table B" wrong, so that we did the sum on duplicated rows.

I could solve that by summarizing by ID.

Please check the corrected measure, that should hopefully solve your problem:

Value Table B = 
VAR _table =
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                FILTER( 'Table B', RELATED( 'Table A'[Valid] ) = TRUE ),
                'Table B'[ID]
            ),
            "SumValue", CALCULATE( SUM( 'Table B'[Value] ) )
        ),
        [SumValue] >= 0
    )
VAR result =
    SUMX( _table, [SumValue] )
RETURN
    result

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[webportal]

@selimovd 

Thanks for your help. I just don't like the RELATED, but it worked!

[selimovd]

Hey @webportal ,

what is the issue with RELATED?

Best regards

Denis

[webportal]

Nothing, it is just expensive, memory-wise, that's all 🙂

[webportal]

@selimovd 

Thanks, but I don't think it is working..

I'm expecting to obtain 177 from ID = 789

[selimovd]

Hey @webportal ,

step by step, we will find the mistake 😉

Can you send me a screenshot of the Table B, if you want filtered to product 789 and a screenshot of the relationship between Table A and Table B?

Thank you and best regards

Denis

[selimovd]

Hello @webportal ,

try the following measure:

Value Table B =
VAR _table =
    FILTER(
        ADDCOLUMNS(
            FILTER( VALUES( 'Table B' ), RELATED( 'Table A'[Valid] ) = TRUE ),
            "SumValue", CALCULATE( SUM( 'Table B'[Value] ) )
        ),
        [SumValue] >= 0
    )
VAR result =
    SUMX( _table, [SumValue] )
RETURN
    result

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi