Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hello everyone,
I have the following measures . All of them are corect except the last one the "Debit Balance 3". I want to sum this measure but the result at the total is not correct , although the individual results at the rows are ok.
Please help!!!!!!!!!!!
OBalance 3 =
var b =
SELECTEDVALUE('Date-Table'[Date])
var c =
CALCULATETABLE(
STARTOFYEAR(
'Date-Table'[Date]
),
'Date-Table'[Date]=b
)
return
CALCULATE(
sum(
'ERP Amount'[Amount (LCY)]
),
FILTER(
'ERP Amount',
'ERP Amount'[Posting Date]<
c
)
)
------------------------------------------------------------------------------
PCrebit 3 =
var b =
SELECTEDVALUE('Date-Table'[Date])
var c =
CALCULATETABLE(
STARTOFYEAR(
'Date-Table'[Date]
),
'Date-Table'[Date]=b
)
return
CALCULATE(
sum(
'ERP Amount'[Credit Amount (LCY)]
),
FILTER(
'ERP Amount',
'ERP Amount'[Posting Date]>=c
&&
'ERP Amount'[Posting Date]<=SELECTEDVALUE('Date-Table'[Date])-1
)
)
------------------------------------------------------------------------------
Dedit 3 =
var b =
SELECTEDVALUE('Date-Table'[Date])
var c =
SELECTEDVALUE('Date-Table2'[Date])
return
CALCULATE(
sum(
'ERP Amount'[Dedit Amount (LCY)]
),
FILTER(
'ERP Amount',
'ERP Amount'[Posting Date]>=b
&&
'ERP Amount'[Posting Date]<=c
)
)
------------------------------------------------------------------------------
Cumulative Dedit 3 =
[Dedit 3]+[PDebit 3]
------------------------------------------------------------------------------
var a =
[OBalance 3]+[Cumulative Debit 3]-[Cumulative Credit 3]
var b=
IF(
a>0,
a,
0)
var c=
SUMX(
FILTER(
'ERP Amount',
a>0
),
a
)
var d=
IF(
HASONEFILTER(
'ERP Amount'[Customer No_]
),
b,
c
)
return
d
------------------------------------------------------------------------------
Hello @Geo_Dor,
Try modifying the "Debit Balance 3" measure to use the ALL function to remove any existing filter context from the 'ERP Amount'[Customer No_] column, which may be causing the incorrect calculation at the total level:
Debit Balance 3 =
VAR a = [OBalance 3] + [Cumulative Debit 3] - [Cumulative Credit 3]
VAR b = IF(a > 0, a, 0)
VAR c = SUMX(FILTER('ERP Amount', a > 0), a)
VAR d = IF(HASONEFILTER('ERP Amount'[Customer No_]), b, c)
VAR e = CALCULATE(d, ALL('ERP Amount'[Customer No_]))
RETURN e
The addition of the CALCULATE function and ALL function with the 'ERP Amount'[Customer No_] column will remove any existing filter context on this column and ensure that the measure is calculated correctly at the total level.
Let me know if you might need further assistance.
Hello @Sahir_Maharaj ,
than you for your answer but unfortunately the total is completely wrong.
the measure calculates correcly the result at the rows of the table and it must sum this results at this column but it isnt happend
Thanks in andvace
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |