How to One table entry no find in check CLosed by Entry no then value Sum in one variable

[PratikVyas]

How to One table entry no find in check CLosed by Entry no then value Sum in one variable

For your reference,

table details 

One table nmae is Cust_LedgEntry Entry no Coloumn check and find entry no . in same table in other coloum CLosedBY ENtry no. then if match entry after value sum in one variable entrywise. it is possible or not?

Cust Ledg Entry
Document Type	Customer posting Group	Document_No	Entry_No_	Collec_Amtinr	ClosedbyENtrymeas
Invoice	Export	ud/24-25/0001	123	500
Payment		pay/24-25/0001	487	500	123
Invoice	Domestic	ud/24-25/0002	125	1000
Payment		pay/24-25/0002	498	500	125
Payment		pay/24-25/0002	490	300	125
Result
Document Type	Customer posting Group	Document_No	Entry_No_	Collec_Amtinr	APPLIED AMT(CR)
Invoice	Export	ud/24-25/0001	123	500	500
Invoice	Domestic	ud/24-25/0002	125	1000	800
Group Result
Customer posting Group	Collec_Amtinr	APPLIED AMT(CR)
Export	500	500
Domestic	1000	800

[ahadkarimi]

Hi @PratikVyas, you will need to create a calculated column to find the corresponding matching Entry_No_ based on the ClosedbyENtrymeasgive, trye these and if you encounter any issues, let me know.

MatchedAmount = 
IF (
    NOT(ISBLANK('Cust_LedgEntry'[ClosedbyENtrymeas])),
    CALCULATE (
        SUM('Cust_LedgEntry'[Collec_Amtinr]),
        FILTER (
            'Cust_LedgEntry',
            'Cust_LedgEntry'[Entry_No_] = EARLIER('Cust_LedgEntry'[ClosedbyENtrymeas])
        )
    ),
    BLANK()
)

Next, create a calculated measure:

AppliedAmount = 
SUMX (
    FILTER (
        'Cust_LedgEntry',
        'Cust_LedgEntry'[Entry_No_] = 'Cust_LedgEntry'[ClosedbyENtrymeas]
    ),
    'Cust_LedgEntry'[Collec_Amtinr]
)

GroupResult = 
SUMX (
    'Cust_LedgEntry',
    'Cust_LedgEntry'[Collec_Amtinr] + 'Cust_LedgEntry'[MatchedAmount]
)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Specialist!

[PratikVyas]

Error getting

i am selecting Earlier function

[Anonymous]

Hi ahadkarimi ,thanks for the quick reply, I'll add more.

Hi @PratikVyas ,

Please make sure you create a calculated column and not a measure. 'Earlier' funciton requires row context to calculate. Or you can try this.

Column = 
VAR _Entry_No_ = [Entry_No_]
RETURN SUMX(FILTER('Cust_LedgEntry',[ClosedbyENtrymeas] = _Entry_No_),[Collec_Amtinr])

If I understand wrongly, please correct me.

Best Regards,
Wenbin Zhou

[PratikVyas]

@Anonymous  it is working fine but i want to result is 

Customer posting group , Collect_AMtinr and Column value this scenario is not working

Group Result
Customer posting Group	Collec_Amtinr	APPLIED AMT(CR)
Export	500	500
Domestic	1000	800

[Anonymous · Accepted solution]

Hi @PratikVyas ,

Do you mean this is a new table?'APPLIED AMT(CR)' is the result you need, right?

Use the following DAX expression to create a measure

APPLIED AMT =
VAR _result =
    ADDCOLUMNS (
        'Cust Ledg Entry',
        "APPLIED AMOUNT",
            SUMX (
                FILTER (
                    'Cust Ledg Entry',
                    [ClosedbyENtrymeas] = EARLIER ( 'Cust Ledg Entry'[Entry_No_] )
                ),
                [Collec_Amtinr]
            )
    )
VAR _group =
    SELECTEDVALUE ( 'Group Result'[Customer posting Group] )
RETURN
    SUMX ( FILTER ( _result, [Customer posting Group] = _group ), [APPLIED AMOUNT] )

Best Regards,
Wenbin Zhou

[PratikVyas]

Thanks it is working.

But it is possible no need to new table. Calulation on one table  Cust_LedgEntry

[Anonymous]

Hi PratikVyas,

After creating the calculated column using my method, create a 'Table' visual. Put 'Customer posting Group' in it. Create two measures.

Measure = IF(SELECTEDVALUE('Table'[Customer posting Group]) = BLANK(),BLANK(),SUM('Table'[Collec_Amtinr]))

Measure 2 = IF(SELECTEDVALUE('Table'[Customer posting Group]) = BLANK(),BLANK(),SUM('Table'[Column]))

Best Regards,
Wenbin Zhou