Convert excel (IF(ISERROR(AVERAGEIFS) to DAX

[Anonymous]

Can anyone have any suggestion for below excel formula to convert into a Dax? =IF(ISERROR(AVERAGEIFS('P'!$U$3:$U$2186,'P'!$H$3:$H$2186,'C'!$I2),"No" (***Tabsheet=P,C)

[Ashish_Mathur]

Hi,

You might as well show some data and the expected result.

[Anonymous]

Hi Ashish,

Please see below,

below excel fomula with column and tabsheet name

=IF(ISERROR(AVERAGEIFS(UP,MAT,MAT,WORLD,PLANNING,LINE,LINE)),"No"

(***Tabsheet=P,C)

>>>Dataset in Excel

Tasheet P
UP	MAT	WORLD	LINE
123	ABC	EMEA	AX
456	XYZ	ASA	LM
789	MNP	AME	AR
Tabsheet C
MAT	Planning	Line
ABC	ACD	AX
XYZ	KBC	LM
MNP	HKR	AR

[Anonymous]

Hi Ashish,

Please see below formula,

=IF(ISERROR(AVERAGEIFS('Prior Year'!$U$3:$U$2186,'Prior Year'!$H$3:$H$2186,'Current Year'!$I2,'Prior Year'!$X$3:$X$2186,'Current Year'!$C2,'Prior Year'!$F$3:$F$2186,'Current Year'!$F2)),"No AUP Avail",AVERAGEIFS('Prior Year'!$U$3:$U$2186,'Prior Year'!$H$3:$H$2186,'Current Year'!$I2,'Prior Year'!$X$3:$X$2186,'Current Year'!$C2,'Prior Year'!$F$3:$F$2186,'Current Year'!$F2))

Expected Result=59152

Prior Year Tabsheet
U3	H3	X3	F3
91677	A66528	usa	pap

Current Year Tabsheet
I2	C2	F2
B52521	APAC	bmx

[v-juanli-msft · Accepted solution]

Hi @Anonymous

In Editor Queries

merge columns for table P (MAT, WORLD,LINE)

and for table C(MAT, PLANNING,LINE)

Then "apply&close", create a relationship between two tables

Finally create measures in Table P

sum = CALCULATE(SUM(P[UP]),FILTER(ALL(P),[Merged]=MAX(C[Merged])))

count = CALCULATE(COUNT(P[UP]),FILTER(ALL(P),[Merged]=MAX(C[Merged])))

average1 = [sum]/[count]

Or

Measure =
VAR sum1 =
    CALCULATE ( SUM ( P[UP] ), FILTER ( ALL ( P ), [Merged] = MAX ( C[Merged] ) ) )
VAR count1 =
    CALCULATE (
        COUNT ( P[UP] ),
        FILTER ( ALL ( P ), [Merged] = MAX ( C[Merged] ) )
    )
RETURN
    IF ( MAX ( P[Merged] ) = MAX ( C[Merged] ), sum1 / count1, "no trial" )

Best Regards

Maggie

[Anonymous]

Thank you so much Maggie & Ashish...Now it makes very clear to me.Thanks

[Ashish_Mathur]

Hi,

Using the query editor just merge the two queries

[Anonymous]

Hi Ashish,

let me know if you need more information.

Thanks