Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
5 years ago
Solved

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

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    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's avatar
      Analitika
      Post Prodigy
      5 years ago

      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