Forum Discussion

shaunwilks's avatar
shaunwilks
Helper V
7 years ago
Solved

Averages - Calculated Column vs Measure - And Table relationship

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.

 

Need Help

2 Replies

    • shaunwilks's avatar
      shaunwilks
      Helper V
      7 years ago

       

      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")