Forum Discussion

stefanot's avatar
stefanot
Frequent Visitor
3 years ago
Solved

Averageif based con current line

Hallo, I've found similar questions to mine in the forum, but it looks like none of them is really what I'm looking for

 

In PowerPivot/DAX I want to create a measure that represents an averageif of sales for each kind of products, like in the example below: 

 

ProductSALESaverageif
drink32
drink12
food44

 

 

In other discussions I see solutions like 

AverageNumberofSALES=Calculate(average(Table[SALES]),Filter(Table, Table[Product]="drink"))

 

but in my case instead of inputing "drink" i'd like to tell in DAX "current value of any single row"

 

thanks

 

 

6 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi stefanot ,

     

    Please try:

    AverageNumberofSALES = CALCULATE(AVERAGE('Table'[SALES]),ALLEXCEPT('Table','Table'[Product]))

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • stefanot's avatar
      stefanot
      Frequent Visitor
      3 years ago

      This works perfectly, thanks!

      let me take the chance for an additional question though:

       

      imagine you have an additional variable of status: 

      PRODUCTSTATUSSALES

      DRINKSOLD1
      DRINKPROPOSED3
      FOODPROPOSED4

       

      how can you get the average if that changes depending on whether you filter by status (eg the average of only sold drinks would become 1)?

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

    You didn't say what you really want, a measure or a calculated column...

  • stefanot's avatar
    stefanot
    Frequent Visitor
    3 years ago

    a calculated column.

    the objective later on is to create a measure that can calculate the difference between the sales of any selected line in the pivot and the fixed number (so not changing based on how you play with the pivot) of average sales of that specific product.

     

    hope this clarifies, thanks for your message!

    • daXtreme's avatar
      daXtreme
      Solution Sage
      3 years ago
      [SalesAverageIf] = // calc column
var CurrentProd = T[Product]
var Result =
    AVERAGEX(
        FILTER(
            T,
            T[Product] = CurrentProd
        ),
        T[Sales]
    )
return
    Result
  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi stefanot ,

     

    You can achieve this by changing the aggregation of SALES directly:

    Output:

     

    Besides, this question is beyond the topic discussed at the beginning of the thread. So to be able to better help other users, if you still have any other questions, please consider marking the replies that are useful to you and creating a thread about the new topic, which will allow the thread to be addressed more efficiently.

    Thanks in advance!

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.