Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
HAM
Frequent Visitor

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

1 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

 

View solution in original post

4 REPLIES 4
bhanu_gautam
Super User
Super User

@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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thank you for your response but this doesn't work

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
Frequent Visitor

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors