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