Equivalent of averageIFS in powerBI (powerPiwot)

[Dejw]

Hello community, I would like to know if its possible to calculate value using DAX in generated table in powerPiwot (in my case). The calculations I would like to make are equivalent of using AVERAGEIFS in excel. I need to get one average number for goods with same condition. I have sample of goods sold (in my currency) during several months from several stocks. I need to calculate column with AVERAGE NUMBER of sells during my time period with STOCK and GoodsID as an condition of calculation. It means that result in new column should be same number(average) for each GoodsID on specific stock during several months that are included in the source table.

If I am doing it in excel I would do it this way (unfortunatelly not possible using DAX)

This is exactly what I need > CRITERIA of averageifs function is item in each row and calculations are made for each row (each GoodsID on specific Stock) across whole time period.

Thanks a lot for your advice! 

D.

[v-zhangti · Accepted solution]

Hi, @Dejw 

I restored your data, please try the following method.

Column:

Average =
CALCULATE (
    AVERAGE ( 'Table'[Sold] ),
    FILTER (
        'Table',
        [STOCK] = EARLIER ( 'Table'[STOCK] )
            && [GoodsID] = EARLIER ( 'Table'[GoodsID] )
    )
)

Is this the output you expect in Desktop?

Best Regards,

Community Support Team _Charlotte

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

[Dejw]

Hi @v-zhangti ! That is exactly what I needed, thakns a lot, just for my info for expample difference between [STOCK] and ( 'Table'[STOCK]) is that first one refers to to item on specific row whereas second one refers to the whole column in specific table? Thanks a lot man !

[lbendlin]

yes, it's the difference between a row context and a filter context.  Note that you _could_  add the table name to a row context reference, and it would still work.  But you cannot have a filter context without the table name.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Dejw]

Hi ! thanks a lot for your response, Attached see data, column AVERAGEIFS is what I need to calculate in PowerPivot for every GoodsID. Formula should calculate Average number of sales of specific GoodsID, on specific stock during the set time period.

Month	STOCK	GoodsID	Sold	AVERAGEIFS
VI.22	HS030	PV1FATHE0321	6 544,80	6 544,80		>>>EXPECTED OUTCOME
VI.22	HS030	PV1FATHE0343	6 544,80
VI.22	HS030	PV1TAIDC0009	37 276,37
I.21	HS020	DOPFASCH0022	288,00
I.21	HS020	CH3HEK180018	2 578,26
II.21	HS020	PV1FATHE0321	11 793,60	5 933,56		>>>EXPECTED OUTCOME
II.21	HS020	VINFAVEP0029	39 463,22
II.21	HS020	CH3HEK180018	1 289,13
V.21	HS020	PV1FATHE0321	73,52	5 933,56		>>>EXPECTED OUTCOME
II.21	HS020	DOPGHUKP0002	5,05
II.21	HS020	DOPGHUKP0030	20,54
II.21	HS020	DOPGHUKP0044	5,14
II.21	HS020	NARCONAV0004	117,50

AverageIFS formula used in excel: "=AVERAGEIFS($D$2:$D$14;$C$2:$C$14;C2;$B$2:$B$14;B2)" ; where "MONTH" is in cell A1

Hopefully this helps you, let me know, thank you a lot D.