Measure to sum with left function and only negatives values by row

[ruinaldo]

i have a table named "Tabela"

I want to do a measure that sum only negative rows, that starts with 24 and conta movimento is yes.

I tried this measure but don´t work.

How can i do that?

[AlB · Accepted solution]

Hi @ruinaldo 

Measure =
SUMX (
    CALCULATETABLE (
        DISTINCT ( Tabela[Conta] ),
        Tabela[Conta movimento] = "Yes",
        Tabela[Saldo] > 0,
        LEFT ( Tabela[Conta], 2 ) = "24"
    ),
    CALCULATE ( SUM ( Tabela[Saldo] ) )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous · Accepted solution]

Hi @ruinaldo ,

Based on your description, you can create a measure as follows.

_sum =

SUMX(

FILTER(

'Sheet1',

LEFT('Sheet1'[Conta],2)="24"&&[conta]=SELECTEDVALUE('Sheet1'[conta])

),

[Saldofinal])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[ruinaldo]

Hi, thank you very much for the formula, you are great.

but i have another question...i want to check the negative saldo by accumulate the conta by month.

Here is an simple example:

i have this table. I delete the column conta movimento to simplify

In month 0 your formula is correct

But In month 0+1 is wrong. I want to check the accumate saldo negative of conta by month

The corret is conta 24371 have accumulate saldo of 11535.32

The another conta have positive accumate saldo. Check the conta 24241...have positive saldo in month 0+1, so don´t have to consider

Your formula give me in month 0+1 this result

[AlB]

@ruinaldo 

I would say go ahead and convert it to text. Check if the result is consistent with the numbers you had. OTherwise you can keep it as number and extract the two first digits as number

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[ruinaldo]

Give me a error....

I think have this problem because "Table[Conta] is in text.

How can i resolve that? I´m working in direct query and when i choose text give me that:

[AlB · Accepted solution]

Hi @ruinaldo 

Measure =
SUMX (
    CALCULATETABLE (
        DISTINCT ( Tabela[Conta] ),
        Tabela[Conta movimento] = "Yes",
        Tabela[Saldo] > 0,
        LEFT ( Tabela[Conta], 2 ) = "24"
    ),
    CALCULATE ( SUM ( Tabela[Saldo] ) )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers