cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate difference to average within category - fing outliers

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

6 REPLIES 6
Highlighted
Super Contributor

## Re: Calculate difference to average within category - fing outliers

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

Regular Visitor

## Re: Calculate difference to average within category - fing outliers

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.

Super Contributor

## Re: Calculate difference to average within category - fing outliers

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

Regular Visitor

## Re: Calculate difference to average within category - fing outliers

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.

Super Contributor

## Re: Calculate difference to average within category - fing outliers

Check this image

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

Regular Visitor

## Re: Calculate difference to average within category - finding outliers

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

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,718)