taking average and ignore values where 0

[selpaqm]

Hi,

I have a table as below. customer service may entering incorrect customer info for same product. In table for a product if actual value is 0 that means product is matched with incorrect customer so I need to add values on value column where actual value column 0. and also for same product can be sold 2 or more customer. I need to make assumption and take average of value column with using actual value column values. so I cannot solve the issue hope you may help.

Customer	product	value1	actual value	Result_value1
a	x	2	0	0
b	x	1	0	0
c	x	2	0	0
d	x	13	8417	19
e	x	1	0	0
f	y	2	0	0
g	y	3	0	0
a	y	5	1000	15
b	y	3	0	0
s	y	1	0	0
c	y	3	200	5
e	y	3	0	0

[ryan_mayu]

@selpaqm 

not clear about your request.

maybe try create a column : if(actual value =0, value1,actual value)

could you please provide the expected output and calculation logic.

[selpaqm]

Hi @ryan_mayu  thanks for your reply.

result value1 column is expected result column. 

just for logic, for each product check actual value column if it is 0 add data on value1 column to where actual value data is not 0 )for product x only one customer have actual value different than 0 so adding 6 where all the customer with actual value is zero). if for a unique product actual value column data is not zero for more than one, get average weight average of all actual value column data (for this table where y is not 0 for customer a and c which actual value is 1000 and 200. and total of value data where actual value data is 12 need to be added to value section as 10 for customer a and 2 for customer c) need to be added to value section as example. hope it is more clear atm.

[ryan_mayu]

@selpaqm 

now i can understand the first part. If there is only one value not 0 for a product, then add up all value1. 2+1+2+13+1=19.

Then I am still not clear about the expected result for product y, how you get 15 and 5? could you pls provide the calculation equation?

[selpaqm]

@ryan_mayu sorry for late respond. what a busy week! 

for product y for 0 values 2+3+3+1+3=12

actual values are 1000 and 200 total 1200

so for total 1200 empty values are 12 so for 1000 it is 10 and for 200 it is 2. it is weigh average. if we divide 12 to 2 that is average. however we make proportion.

1000X12/1200=10 for bigger one 200X12/1200=2

so for customer with actual value 1000, 10+5(value1 column data)=15 and the other 2+3=5

[ryan_mayu · Accepted solution]

@selpaqm 

you can try this

Column = 
VAR _avg=DIVIDE(CALCULATE(sum('Table'[actual value]),ALLEXCEPT('Table','Table'[product])),CALCULATE(sum('Table'[value1]),FILTER(ALLEXCEPT('Table','Table'[product]),'Table'[actual value]=0)))
return if('Table'[actual value]=0,0,'Table'[actual value]/_avg+'Table'[value1])

pls see the attachment below