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: 

AccAmount
150
2100
370
435
560
320

 

and Account table:

NoFromTo
113
235
324
413
54

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

"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

     

4 Replies

  • 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