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    AB...
Need Help
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")