Replace 0 with average of column

[nrichards_au]

Hi All,

Looking for some guidance here.

Need to replace PY values of 0 with the average of PY where ENTITY, PC_GROUP & AC_GROUP are the same. 

Any one have any ideas on how it can be done in DAX or should I be creating an aggregate table and linking it prior to the load?

ENTITY	PC_GROUP	AC_GROUP	PERIOD	PY	CY
E1	PC1	G1	P05	0	1
E1	PC1	G1	P06	1	1
E1	PC1	G1	P07	1.4848947	1
E1	PC1	G1	P08	1	1.21053
E1	PC1	G1	P09	1.3233083	1

[mahoneypat]

I think this DAX will do what you are looking for.  I named the table "PYData" and added a Calculated Column with this expression:

Adj PY = var currentPY =PYData[PY]

var currentENT = PYData[ENTITY]

var currentPC = PYData[PC_GROUP]

var currentAC = PYData[AC_GROUP]

var avgsameENT_PC_AC = CALCULATE(AVERAGE(PYData[PY]), All(PYData), PYData[ENTITY]=currentENT, PYData[PC_GROUP]=currentPC, PYData[AC_GROUP]=currentAC)

return if(currentPY=0, avgsameENT_PC_AC, currentPY)

If this works, please mark it as solution.  Please let me know if any questions or not what you needed.

Regards,

Pat