Forum Discussion
Solved
Sum amount based on account range
Lets say I have two tables. Entry Table: Acc Amount 1 50 2 100 3 70 4 35 5 60 3 20 and Account table: No From To 1 1 3 2 3 5 3 2 4 4 1 3...
Hi HAM
Would a measure like this help?
Total in Range = SUMX( ADDCOLUMNS( 'Account', "__amt", VAR _From = [From] VAR _To = [To] VAR _Res = CALCULATE( SUM( 'Entries'[Amount] ), FILTER( ALL( 'Entries' ), 'Entries'[Acc] >= _From && 'Entries'[Acc] <= _To ) ) RETURN _Res ), [__amt] )or a calculated column
Total in Range CC = CALCULATE( SUMX( ADDCOLUMNS( 'Account', "__amt", VAR _From = [From] VAR _To = [To] VAR _Res = CALCULATE( SUM( 'Entries'[Amount] ), FILTER( ALL( 'Entries' ), 'Entries'[Acc] >= _From && 'Entries'[Acc] <= _To ) ) RETURN _Res ), [__amt] ) )Let me know if you have any questions.
Sum of amount based on account range.pbix
gmsamborn
Super User
Super UserHi HAM
Would a measure like this help?
Total in Range =
SUMX(
ADDCOLUMNS(
'Account',
"__amt",
VAR _From = [From]
VAR _To = [To]
VAR _Res =
CALCULATE(
SUM( 'Entries'[Amount] ),
FILTER(
ALL( 'Entries' ),
'Entries'[Acc] >= _From
&& 'Entries'[Acc] <= _To
)
)
RETURN
_Res
),
[__amt]
)
or a calculated column
Total in Range CC =
CALCULATE(
SUMX(
ADDCOLUMNS(
'Account',
"__amt",
VAR _From = [From]
VAR _To = [To]
VAR _Res =
CALCULATE(
SUM( 'Entries'[Amount] ),
FILTER(
ALL( 'Entries' ),
'Entries'[Acc] >= _From
&& 'Entries'[Acc] <= _To
)
)
RETURN
_Res
),
[__amt]
)
)
Let me know if you have any questions.
Sum of amount based on account range.pbix
