Search code examples
powerbipowerbi-desktop

Zero value replace by average value by category

I have a table:

Product  Customer  Value
------------------------
x        A         10
y        A         20
x        B          0
x        C         12
y        C         24
x        D         15
y        D         30

And like to the zero value in a power BI visualization table as average of Product category.

Expected output:

Product  Customer  Value
-------------------------
x        A         10
y        A         20
x        B         12.33
x        C         12
y        C         24
x        D         15
y        D         30
Solution

  • Calculated Column:

    NewValue = 
  IF(
    [Value] <> 0,
    [Value],
    CALCULATE( AVERAGE(Table[Value]), ALLEXCEPT(Table, Table[Product]), Table[Value] <> 0 )
  )