wrong result when applying divide with blank value in details

[Gilad]

Hello Community , 

I am trying to create an average price by dividing sales amount by quantity.

I have filter records in the amount variable so calculation will apply only on records that the amount is greater than 1 

The same calculation is apply on qty that should be greater than 0 

It seems that aggregate divide result is incorrect as some of calculation result in detail are equal to blank.

I know I need to filter out blank but I did not succeed to get correct result.

please advice.

avg_price =
var vprice = CALCULATE(
SUM(INVOICES[Amount]),
CALCULATETABLE(
VALUES('INVOICES'),
'INVOICES'[Amount] > 1
))
var vqty = CALCULATE(
SUM(INVOICES[qty]),
CALCULATETABLE(
VALUES('INVOICES'),
'INVOICES'[qty] > 0
))
return DIVIDE(vprice , vqty, 0)

[GiladO]

Thank you @FreemanZ  , it works

[FreemanZ · Accepted solution]

hi @Gilad 

the filters need to be applied for each row of your table simultaneously.

Supposing your data is like this:

Amt	Qty
1	-1
10	0
100	2
400	3

try to plot a measure like:

Avg Price = 
VAR _table = 
FILTER(
    TableName, 
    TableName[Amt]>1&&TableName[Qty]>0
)
VAR _amt = SUMX(_table, TableName[Amt])
VAR _qty = SUMX(_table, TableName[Qty])
RETURN 
DIVIDE(_amt, _qty)

it worked like: