Averageif based con current line

[stefanot]

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: 

Product	SALES	averageif
drink	3	2
drink	1	2
food	4	4

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

[v-jianboli-msft]

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.

[v-jianboli-msft · Accepted solution]

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]

This works perfectly, thanks!

let me take the chance for an additional question though:

imagine you have an additional variable of status: 

PRODUCTSTATUSSALES

DRINK	SOLD	1
DRINK	PROPOSED	3
FOOD	PROPOSED	4

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

[stefanot]

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]

[SalesAverageIf] = // calc column
var CurrentProd = T[Product]
var Result =
    AVERAGEX(
        FILTER(
            T,
            T[Product] = CurrentProd
        ),
        T[Sales]
    )
return
    Result

[daXtreme]

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