Averages - Calculated Column vs Measure - And Table relationship

[shaunwilks]

Lets take the following tables

Table 1 - Invoices

Date,  Supplier, Invoice Nbr, Item Code, Qty, Price, Line Value

01/02    S1     1234    ABC    5    20.00    100.00

01/02    S2     1235    ABC    5    21.00    105.00

01/02    S1     1236    XYZ    5    20.00    100.00

01/02    S1     1237    XYZ    5    20.00    100.00

01/02    S1     1238    ABC    5    19.00    95.00

01/02    S1     1239    ABC    5    18.00    90.00

Table 2 - Suppliers

Table 3 - Items

The ultimate goal is to count how many times the supplier offered a price above the total average or the Item.

So in the above instance...

- Item ABC has an average Buy Price of 88/4 =22

So I want to count how many times the buy price for each supplier was above (or below) that average.

I thought to do it I would need a flag on each row in the table that determined whether that buy price was above or below the average.

Could someone please assist as I am going around in circles on this.

I have measures that calculate the Total Average for the Item, and another for specific suppliers.

What I am battling with is flagging each row in a way that tells me whether it as greater or less than the average buy price.

Do I assume I need a Measure or Calculated column on the Stock table for Averge Buy price.

And then a calculated column in the Invoices table that compares the price field to the Stock measure ?

Any thoughts greatly appreciated.

[LivioLanzo · Accepted solution]

Hi @shaunwilks

check the file: https://1drv.ms/u/s!AiiWkkwHZChHj0_9NlI0Luaj1J2D

[shaunwilks]

Thanks for the time in doing that.

I worked on it yesterday evening and ended up getting a similar result but clearly not in such a clean way as you have achieved it.

Ill use your code as a way to educate myself.  

Above Average Count = 
SUMX(
    VALUES( Items[ItemCode] ),
    VAR AVGPrice = CALCULATE( AVERAGE( Invoices[Price] ), ALL( Suppliers ) )
    RETURN
    CALCULATE( 
        COUNTROWS( Invoices ),
        Invoices[Price] > AVGPrice
    )
)

I created a calculated column on the Item table for the fixed Price avg.

Avg Buy Price = CALCULATE(AVERAGEX(RELATEDTABLE('Invoices'),'Invoices'[Price])

I then had a calculated column in the invoices table to do the comparison.

Just a snippet below but extended it to match exact requirements.

Price Status = IF('Invoices'[Price]>Related('Items'[Avg Buy Price]),"Over", "Under")