hi

[abc_777]

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

[v-luwang-msft]

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]

@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]

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

[abc_777]

@v-luwang-msft 

could you tell me how i can i make ) when CR is full refunede. if thats the thing is hard to do

thanks

[abc_777]

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]

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