Forum Discussion

HAM's avatar
HAM
Frequent Visitor
2 years ago
Solved

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...
"DAX help"
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    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

     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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's avatar
HAM
Frequent Visitor
2 years ago

Thank you for your response but this doesn't work

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      HAM
      Frequent Visitor
      2 years ago

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