cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Separate and sum

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

1 ACCEPTED SOLUTION
Community Support

See if this meets your needs.

3 REPLIES 3
Community Support

Hi @Analitika ,

``````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.

Post Prodigy

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

Community Support

See if this meets your needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.