cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mr_Glister Regular Visitor
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.JPGSales data

Desired outcome.JPGDesired outcome

  

6 REPLIES 6
amitchandak Super Contributor
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 

 

Highlighted
Mr_Glister Regular Visitor
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.

 

first try.JPG

amitchandak Super Contributor
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

Mr_Glister Regular Visitor
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.

amitchandak Super Contributor
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])
    ))

Screenshot 2019-09-13 19.53.39.png

Mr_Glister Regular Visitor
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?

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)