topic Re: Sum amount based on account range in DAX Commands and Tips

Sum amount based on account range

HAM — Fri, 01 Mar 2024 14:49:53 GMT

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

Re: Sum amount based on account range

bhanu_gautam — Fri, 01 Mar 2024 16:46:10 GMT

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

Re: Sum amount based on account range

HAM — Fri, 01 Mar 2024 17:31:44 GMT

Thank you for your response but this doesn't work

Re: Sum amount based on account range

gmsamborn — Fri, 01 Mar 2024 19:54:09 GMT

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

Re: Sum amount based on account range

HAM — Sat, 02 Mar 2024 12:54:05 GMT

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