Sum amount based on account range Question

DAX DAX Calculations
1

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 corresponding 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.

I am attaching the made-up data file. Thanks in advance
DataEntry.xlsx (9.3 KB)

2

ANSWER:
Simple & crude solution, since no further details are provided.

Use this code as a calculated column:

Total = SUMX( FILTER(Entry, Entry[Acc]>=Account[From] && Entry[Acc]<=Account[To]),Entry[Amount])

Why is there a relationship between [Acc] & [No], though, since they seem to be different things?

3

Hi @nikot.23 This solution doesn’t work for what i need. I have found the solution on another forum:

allAmount =
var minAcc = MIN(Account[From])
var maxAcc = MAX(Account[To])
VAR seri = GENERATESERIES(minAcc, maxAcc, 1)
RETURN
SUMX(
FILTER(
ALLSELECTED(Entry),
Entry[Acc] IN seri
),
Entry[Amount]
)

4

Ah nice, so you needed a measure and what you posted it seems ok.

In that solution’s model, do you have an active relationship between the tables?

5

Yes there is an active relationship. Entry[Acc] and Account[No] are both Account Numbers and there’s one-many relationship based on these two fields