topic Re: wrong result when applying divide with blank value in details in DAX Commands and Tips

wrong result when applying divide with blank value in details

Gilad — Thu, 30 Mar 2023 17:29:27 GMT

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)

Re: wrong result when applying divide with blank value in details

FreemanZ — Fri, 31 Mar 2023 02:00:44 GMT

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:

Re: wrong result when applying divide with blank value in details

GiladO — Sun, 02 Apr 2023 06:16:28 GMT

Thank you FreemanZ , it works