Separate and sum

[Analitika]

I have table with values

Sas_kod | deb | cred

1209 |  12400 | 0

1209 |  1400 | 900

1209 |  0 | 400

I need create measure wich give me  visual

(it give me wong result )

Cred or Deb | Sas_kod | Sum

Cred | 1209 | 1300

Deb | 1209 | -12400

I trying to write custom column in table with formula
Cred or Deb = IF ( Left([sas_kod],1) in {"0","1"} && [cred] <>0 ,  "Cred", "Deb")

And use Measure for sum

Sum = SUMX(Table, [cred]-[deb])

But it took only first value in second row where are deb and cred, so cant separate this row into two rows. And i loose 1400 deb value as it goes wront to cred but should be separate

So i need sum only cred if sas_kod start with 0 or 1, and sum deb with minus sign if sas_kod start with 0 or 1, but if sas_kod start NOT with 0 or 1 (everithing else) sum should be cred-deb (this should be valid for measure and for formula which separate Cred or deb column)

Expected visual result

Cred or Deb | Sas_kod | Sum

Cred | 1209 | 1300

Deb | 1209 | -13800

[V-lianl-msft]

Hi @Analitika ,

Please refer to this measure:

Measure = 
var 
c_o_d_ = SELECTEDVALUE('Table'[Cred or Deb])
var
sas_ = LEFT(MAX('Table'[Sas_kod]),1)
return SWITCH(TRUE(),
            c_o_d_="Deb"&&sas_ in {"0","1"},CALCULATE(SUM('Table'[deb]),ALLEXCEPT('Table','Table'[Sas_kod])),
            c_o_d_="Cred"&&sas_ in {"0","1"},CALCULATE(SUM('Table'[cred]),ALLEXCEPT('Table','Table'[Sas_kod])),
            SUM('Table'[cred])-SUM('Table'[deb]))

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

[Analitika]

@V-lianl-msft   ty for reply

on big data it work wrong

as 

c_o_d_="Deb"&&sas_ in {"0","1"},CALCULATE(SUM('Table'[deb]),ALLEXCEPT('Table','Table'[Sas_kod]))

never been hapen as formula Cred or Deb always choose cred value

also this should be done only in measure as many other measures is linked to period and it cant be dublicate

it should be like 

Cred or Deb =
IF (
Left([sas_kod],1) in {"0","1"} && [cred] <>0 && [deb] <>0,  "Cred and Deb",
       IF ( Left([sas_kod],1) in {"0","1"} && [cred] <>0, "Cred", "Deb")
)

and measure
if "cred and deb" then credsum = [cred]-0 and debsum =0 - [deb]

i didnt see deb at all, and all Sum values is same

https://easyupload.io/etibt5

[V-lianl-msft · Accepted solution]

See if this meets your needs.