Forum Discussion

Satish_Kumar's avatar
Satish_Kumar
Helper V
9 years ago
Solved

Issue in the filter

    I am tryinmg to summerize the amount in my data on the basis of entry type=1 but when i am applying the filter i am facing this issue as i shown, If their is any mistake in my code please make...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Satish_Kumar，

    >> if Doc.No Xxxxx having multiple Payment, the result should show the value =15000 gainst the Doc No Xxxxx.

    10000 1st
    2000   2nd
    3000  3rd
     
    I create the sample data and create a new table using the formula.

     

    Table 2 = SUMMARIZE(Table2,Table2[Type],"Amount",SUM(Table2[Doc]))


    As shown in the snapshot, it will return the desired result about sum of “Doc” group by “Type”.

     



    Based on my understanding, do you want to calculate the sum of amount for “Entry Type”=1 group by Document No_, Posting Date, Entry Type, right? If it is, I try to reproduce it using your given sample data.

    Create a new table using the formula below, and get expected result shown the screenshot.

    Table = SUMMARIZE(Table1,Table1[Document No_],Table1[Posting Date],Table1[Entry Type],"Invc Amount",CALCULATE(SUM(Table1[Amount]),Table1[Entry Type]=1))

     
    If you have any other issue, please share more details.

    Best Regards,
    Angelia

Satish_Kumar's avatar
Satish_Kumar
Helper V
9 years ago

Baskar

I have did that Baskar but at the end the summerize donot giving the correct value.

For Example-

if Doc.No Xxxxx having multiple Payment

10000 1st

2000   2nd

3000  3rd

 

the result should show the value =15000 gainst the Doc No Xxxxx

 

But its is not giving the correct it only shows the value i.e. 10000

 

 

Baskar's avatar
Baskar
Resident Rockstar
9 years ago

Do one thing my friend share some sample data and what is ur expected result . 

 

i will help u

  • Satish_Kumar's avatar
    Satish_Kumar
    Helper V
    9 years ago

    Baskar

    I am doing that this take few mintues.

     

    Thanks

    Satish

     

  • Satish_Kumar's avatar
    Satish_Kumar
    Helper V
    9 years ago

    Baskar

     

    Entry No_Cust_ Ledger Entry No_Entry TypePosting DateDocument TypeDocument No_AmountAmount (LCY)
    105245530514/27/2012 0:001BRV/DEC/09/01393-131367-131367
    105335531514/26/2012 0:001BRV/DEC/09/01395-490000-490000
    105345531714/26/2012 0:001BRV/DEC/09/01396-490000-490000
    105355531914/26/2012 0:001BRV/DEC/09/01397-6734-6734
    105575575914/30/2012 0:001BRV/DEC/09/01434-27575-27575
    105615576715/3/2012 0:001BRV/DEC/09/01435-186131-186131
    105665577515/5/2012 0:001BRV/DEC/09/01436-165450-165450
    105675577715/5/2012 0:001BRV/DEC/09/01437-151387-151387
    105875589915/8/2012 0:001BRV/DEC/09/01443-44550-44550
    105885590115/8/2012 0:001BRV/DEC/09/01444-5100-5100
    106315651515/21/2012 0:001BRV/DEC/09/01480-70000-70000
    106415653515/21/2012 0:001BRV/DEC/09/01481-1216744-1216744
    106475658615/14/2012 0:001BRV/DEC/09/01490-33437-33437
    106485658815/14/2012 0:001BRV/DEC/09/01491-30000-30000
    106645665315/23/2012 0:001BRV/DEC/09/01492-195082-195082
    106755682315/25/2012 0:001BRV/DEC/09/01499-5100-5100
    106775682815/25/2012 0:001BRV/DEC/09/01500-118570-118570
    106805683215/25/2012 0:001BRV/DEC/09/01501-39600-39600
    106875684615/28/2012 0:001BRV/DEC/09/01504-5300-5300
    107125696315/29/2012 0:001BRV/DEC/09/01511-498282-498282
    107355699715/29/2012 0:001BRV/DEC/09/01513-28630-28630
    107465702415/31/2012 0:001BRV/DEC/09/01515-82921-82921
    107655718416/2/2012 0:001BRV/DEC/09/01522-104530-104530
    107705719516/4/2012 0:001BRV/DEC/09/01524-83427-83427
    107715719716/4/2012 0:001BRV/DEC/09/01525-6741-6741
    107745720116/4/2012 0:001BRV/DEC/09/01526-195555-195555
    107755720316/4/2012 0:001BRV/DEC/09/01527-81793-81793
    108455778016/13/2012 0:001BRV/DEC/09/01552-131533-131533
    108495778416/15/2012 0:001BRV/DEC/09/01553-150000-150000
    108635795016/19/2012 0:001BRV/DEC/09/01569-222984-222984
    108705796516/22/2012 0:001BRV/DEC/09/01571-1293493-1293493
    108735797216/22/2012 0:001BRV/DEC/09/01574-290466-290466
  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi Satish_Kumar，

    >> if Doc.No Xxxxx having multiple Payment, the result should show the value =15000 gainst the Doc No Xxxxx.

    10000 1st
    2000   2nd
    3000  3rd
     
    I create the sample data and create a new table using the formula.

     

    Table 2 = SUMMARIZE(Table2,Table2[Type],"Amount",SUM(Table2[Doc]))


    As shown in the snapshot, it will return the desired result about sum of “Doc” group by “Type”.

     



    Based on my understanding, do you want to calculate the sum of amount for “Entry Type”=1 group by Document No_, Posting Date, Entry Type, right? If it is, I try to reproduce it using your given sample data.

    Create a new table using the formula below, and get expected result shown the screenshot.

    Table = SUMMARIZE(Table1,Table1[Document No_],Table1[Posting Date],Table1[Entry Type],"Invc Amount",CALCULATE(SUM(Table1[Amount]),Table1[Entry Type]=1))

     
    If you have any other issue, please share more details.

    Best Regards,
    Angelia