Calculate difference to average within category - fing outliers

[Mr_Glister]

Hi,

I have sales data with invoice numbers and products. For identifying outliers (finding incorrect invoices) I'd like to calculate the difference between each invoice's sales price and the average sales price of all invoices per product.

NB! My [Product price on invoice] is a measure (Invoice amount / invoice quantity). That means without adding the invoice number hirachy below the products, I already get the correct average price per product.

My first idea was to calculate my measure as [Product price on invoice] - CALCULATE ([Product price on invoice] , ALLSELECTED ('Invoice number') but this will return all invoice numbers of the period within each product - even though an invoice did not have the product on it.

Using the example below it would mean that even though Invoice 4 did not have Apples on it, it would show in the Apples category and the result would be 0 - 5,25 = -5,25. And that throws me off when I'm looking for the outliers.

It might not be too complicated but I can't figure it out. Anyone who can point me in the right direction?

Sales data

Desired outcome

[amitchandak]

Try like This

Avg Diff Brand = 
CALCULATE ( AVERAGE( ( Sales[Sales] ) )
 - CALCULATE (
 AVERAGE ( Sales[Sales] ),
 ALLEXCEPT ( 'Item','Item'[Brand])
 )*-1)

You can change the position to avoid -1, multiplication. Or can split the formula into 2 and then take diff 

[Mr_Glister]

I'm not sure what your *-1 was for. If I enter it the formula throws an error. Either way, I don't think your solution works, although to be honest I'm not sure why.

[amitchandak]

It Should on on Field having value 4 OZ and 5 0Z. So when I do by Brand ,I see values at Category or Item, level

[Mr_Glister]

Sorry, not sure what you mean. In my picture above you see two different products (4 Oz and 5 Oz) and the invoice numbers that have those products on them in the week I'm looking at.

[amitchandak]

Check this image

Avg Diff Brand = 
CALCULATE ( AVERAGE( ( Sales[Sales] ) )
    - CALCULATE (
        AVERAGE ( Sales[Sales] ),
        ALLEXCEPT ( 'Item','Item'[Brand])
    ))

[Mr_Glister]

Does it maybe only work because both 'Brand' and 'Category' are in your items table?