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 

 

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

Highlighted
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 140 members 1,547 guests
Please welcome our newest community members: