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?