Sum amount based on account range

[HAM]

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.

Thanks in advance

[bhanu_gautam]

@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

[HAM]

Thank you for your response but this doesn't work

[gmsamborn · Accepted solution]

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

[HAM]

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