topic Re: Averageif based con current line in DAX Commands and Tips

Averageif based con current line

stefanot — Fri, 11 Nov 2022 17:30:05 GMT

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

Re: Averageif based con current line

daXtreme — Fri, 11 Nov 2022 18:45:50 GMT

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

Re: Averageif based con current line

stefanot — Fri, 11 Nov 2022 19:54:41 GMT

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!

Re: Averageif based con current line

daXtreme — Sat, 12 Nov 2022 11:02:13 GMT

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

Re: Averageif based con current line

v-jianboli-msft — Mon, 14 Nov 2022 07:59:10 GMT

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.

Re: Averageif based con current line

stefanot — Mon, 14 Nov 2022 08:14:40 GMT

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

Re: Averageif based con current line

v-jianboli-msft — Mon, 14 Nov 2022 08:27:15 GMT

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.