cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors