Average of multiple columns with null values in Power BI

[NATO-OZ]

Hi all,

First post!

In Power BI I'm trying to 'Add a column' that is the average of 3 columns, one of the columns may have a 'null' value in it, please see the data below:

So for each row I need to find the average of '47254', '121844' and '139820', unfortunately data is not always present and will have a 'null' as shown and this needs to be excluded.

  

Thank you in advance

[mahoneypat · Accepted solution]

I agree that unpivotting is almost always the way to go.  However, if you need to keep your 3 columns pivotted, this approach will get you the average and exclude nulls

Custom Column M code - List.Average(List.RemoveNulls({[Column2],[Column3],[Column4]}))

Putting your 3 columns as a list inside curly brackets makes it a list, and then you can take advantage of the set of list functions in M.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[NATO-OZ]

Thank you this is perfect, as part of the next step I unpivot the columns but wanted a site average before I did that and appended all my sites together

[Greg_Deckler]

@NATO-OZ - Welcome to the community! You might think about unpivoting those columns in Power Query and then you could just use the default average aggregation or AVERAGE DAX function. If you cannot unpivot the columns, you can use my MC Aggregation DAX Quick Measure here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...