Replace 0 with average of column

nrichards_au — Wed, 29 Apr 2020 01:06:37 GMT

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?

Re: Replace 0 with average of column

mahoneypat — Wed, 29 Apr 2020 01:54:39 GMT

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