cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## 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 5 4 5

There's a relationship based on 'Acc' column and 'No' column. I want to calculate the sum of Amounts for each Account No based on the coresponding Account range (From, To). For example, for Account No 1, i want to be able to add 50+100+70+20 (and get 240 as the result) and for Account No 2 70+20+35+60. How can this be done.

1 ACCEPTED SOLUTION
Super User

Hi @HAM

Would a measure like this help?

``````Total in Range =
SUMX(
'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(
'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

4 REPLIES 4
Super User

@HAM , You can first add one calculated column in Entry table using below method

Account Number =
VAR CurrentAcc = EntryTable[Acc]
RETURN
CALCULATE(
MAX('Account Table'[No]),
'Account Table'[From] <= CurrentAcc && (ISBLANK('Account Table'[To]) || CurrentAcc <= 'Account Table'[To])
)

Then you can create one measure for sum using

Total Amount =
CALCULATE(
SUM(EntryTable[Amount]),
ALLEXCEPT(EntryTable, EntryTable[Account Number])
)

Please accept as solution and give kudos if it helps

Proud to be a Super User!

Frequent Visitor

Thank you for your response but this doesn't work

Super User

Hi @HAM

Would a measure like this help?

``````Total in Range =
SUMX(
'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(
'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

Frequent Visitor

@gmsamborn Thank you so much. The measure and the calculated column both work!