Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |