Forum Discussion

AdamRobC's avatar
AdamRobC
Icon for Helper II rankHelper II
5 years ago
Solved

Calculating average of a numeric value across unique column values

Hi, I have what I believe is a fairly simple use case but struggling to get the right DAX.

I would like to create a column that calculates the average Current Price by retailer and product ID e.g. for the below table extract the value for rows where Retailer = Zalando (CZ) would be 12.19 ((13.46 + 10.60 + 12.52) / 3)

The full table includes multiple product IDs as well so would need to work across retailers and product IDs.

I've tried various solutions involving AVERAGEX, SUMMARIZE etc. but nothing has worked for me

Thanks!

 

av
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    AdamRobC I believe there are two approaches to this that should work. Approach 1:

    Column = CALCULATE(AVERAGE('Table'[Current Price])

    Uses row to filter context switching.

     

    Approach 2:

    Column = AVERAGEX(FILTER('Table',[Retailer] = EARLIER('Table'[Retailer]) && [Retailer Product ID] = EARLIER('Table'[Retailer Product ID)),'Table'[Current Price])

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    AdamRobC I believe there are two approaches to this that should work. Approach 1:

    Column = CALCULATE(AVERAGE('Table'[Current Price])

    Uses row to filter context switching.

     

    Approach 2:

    Column = AVERAGEX(FILTER('Table',[Retailer] = EARLIER('Table'[Retailer]) && [Retailer Product ID] = EARLIER('Table'[Retailer Product ID)),'Table'[Current Price])