Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
nrichards_au
Helper I
Helper I

Replace 0 with average of column

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?

 

ENTITYPC_GROUPAC_GROUPPERIODPYCY
E1PC1G1P0501
E1PC1G1P0611
E1PC1G1P071.48489471
E1PC1G1P0811.21053
E1PC1G1P091.32330831

 

1 REPLY 1
mahoneypat
Microsoft Employee
Microsoft Employee

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
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.