topic Separate and sum in Developer

Separate and sum

Analitika — Mon, 05 Jul 2021 10:51:13 GMT

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

Re: Separate and sum

V-lianl-msft — Wed, 07 Jul 2021 06:15:58 GMT

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.

Re: Separate and sum

Analitika — Wed, 07 Jul 2021 08:50:24 GMT

@V-lianl-msft ty for reply

on big data it work wrong

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

Re: Separate and sum

V-lianl-msft — Fri, 09 Jul 2021 07:52:59 GMT

See if this meets your needs.