Forum Discussion

abc_777's avatar
abc_777
Solution Specialist
3 years ago

hi

hi

 

I have a sales table where i have value sold and return. When  sold invoice number is normal a number but when return then there is CR at beginning and CR or return invoice number is not same with sold invoice number. some time customer return full or all items some time pertial items

 

Invoice number Net Amount
221101 150
221102 130
221103 200
221104 120
CR999999 -120
221105 80
221106 40
CR888888 -20
221107 15

 

now when i try to find AVG Busket value i already remodev invoices that starts with CR

so my clculation would be (150 + 130 + 200 + 80 + 20 +15) / 6 = 99.16 AVG busket vlue

i should not add120 (221104 and CR999999)  as it full return and i should add 20 (221106 and CR888888) as it pertial return

 

so here i actually total all positive values and then divide it with all posivile values not by invoice numbers

If i am correct then please help me how i can COUNT ONLY POSITIVE numbers of net amount as well as how i can not count 120 as i remove starts with CR CR999999 

Please do not hesitate to give your thoughts if any to find out the otherway 

 

thanks

 

 

Need Help

6 Replies

  • abc_777's avatar
    abc_777
    Solution Specialist
    3 years ago

    I think I need to first remove the invoice starting with CR

     

    then I have to check and compare if CR Net Amount if = Invoice net Amount then i have to remove that invoice also then sum (net amount) and divide count of invoive number

     

    please help me to create this measure 

     

    thanks

    • abc_777's avatar
      abc_777
      Solution Specialist
      3 years ago

      hi,

       

      Its my mistakes i couldn't give a good example file to understand. here ihave attached excel file. 

       

      where i can remove full and pertial returns, and match with customer_ID and invoice_DT with invoice number and CR number to remove then find the avg. 

       rows could be anywhere within 3 millions of data

       

      sorry for the inconvient.

       

      thanks

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    3 years ago

    Hi abc_777 ，

    First ,add index ,then use the below dax to create a new column:

    test = IF('Table'[Net Amount]<0,'Table'[Net Amount]+CALCULATE(MAX('Table'[Net Amount]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1)),if(CALCULATE(MAX('Table'[Net Amount]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1))<0,0,'Table'[Net Amount]))

     

     

    Then create the below measure:

    avg = SUM('Table'[test])/CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[test]>0))

    Output :

     

     

    Best Regards

    Lucien

    • abc_777's avatar
      abc_777
      Solution Specialist
      3 years ago

      v-luwang-msft 

       

      thanks for your efforts. really great. would please help me find where there error is in my calculative column

      invoice value that is fully refund with CR should be both be ) but mine not doing that. PLease help

       

      test = IF('bm_retail_t ssummary'[NET_AMT] < 0,
                      'bm_retail_t ssummary'[NET_AMT] +
                              CALCULATE(
                                      MAX(
                                          'bm_retail_t ssummary'[NET_AMT]),
                                                  FILTER(ALL('bm_retail_t ssummary'),'bm_retail_t ssummary'[Index] =
                                                      EARLIER('bm_retail_t ssummary'[Index]) - 1)),
                              if(CALCULATE(
                                  MAX('bm_retail_t ssummary'[NET_AMT]),
                                      FILTER(ALL('bm_retail_t ssummary'),
                                          'bm_retail_t ssummary'[Index] = EARLIER('bm_retail_t ssummary'[Index]) + 1)) < 0, 0, 'bm_retail_t ssummary'[NET_AMT]
                                          )
                                          )
      • abc_777's avatar
        abc_777
        Solution Specialist
        3 years ago

        I think we made a mistaked

         

        221104 120
        CR999999 -120

         

        not always be together. invoice number and corrorpondance invoice CR could be in different place, could be after fifteen thousand or tweenty thousand rows. could be anywhere in same table.

         

        in that case - 1 is not right.

         

        we could say when CR Nat Amount matches with invoice Net Amount and  in same invoice date (I didnt mention it in my example table but you can create it in yours) then make CR Nat Amount and Net Amount of them 0

         data is something like this its over 3 milions of data with invoice and CR Net amount and Invoice Date