Can someone please help me how to calculate average value when we have null values in the data.

[Anonymous]

Please find below the data source screenshot.

I have to calculate average of all columns. Please do help me. Thanks.

when i do using AveageA function as below, i am getting an error.

Average value = AVERAGEA('Booked Time'[W/c 03-09-2018]+'Booked Time'[W/c 10-09-2018]+'Booked Time'[W/c 20-08-2018]+'Booked Time'[W/c 27-08-2018])

[Greg_Deckler · Accepted solution]

Have a look at my Multi-Column Aggregations (MC Aggregations) Quick Measure. This is DAX.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...

[v-yuta-msft · Accepted solution]

Hi karthiksunku,

You can create a calculate column to calculate the sum of the four columns then use AVERAGE() function to achieve the result of that calculate column.

Regards,

Jimmy Tao 

[ofirk · Accepted solution]

Hi, I'm not sure if I understand correctly: you're trying to calculate the average of the values in the four columns, not the average of each column separately, right?

This isn't very elegant, but I expect it will work:

Average value = (IF(NOT ISBLANK([W/c 03-09-2018]), [W/c 03-09-2018]) + IF(NOT ISBLANK([W/c 10-09-2018]), [W/c 10-09-2018]) + IF(NOT ISBLANK([W/c 20-08-2018]), [W/c 20-08-2018]) + IF(NOT ISBLANK([W/c 27-08-2018]), [W/c 27-08-2018])) / (IF(NOT ISBLANK([W/c 03-09-2018]), 1) + IF(NOT ISBLANK([W/c 10-09-2018]), 1) + IF(NOT ISBLANK([W/c 20-08-2018]), 1) + IF(NOT ISBLANK([W/c 27-08-2018]), 1))

[v-yuta-msft · Accepted solution]

Hi karthiksunku,

You can create a calculate column to calculate the sum of the four columns then use AVERAGE() function to achieve the result of that calculate column.

Regards,

Jimmy Tao 

[Greg_Deckler]

Are you trying to use AVERAGEA in the Query Editor? Because that is not going to work. AVERAGEA is DAX and Query Editor is M.

[Anonymous]

Hi @Greg_Deckler,

Actually i want to try it creating calculated column or measure but fed up of it getting error and asking in the community.

Please help me if you have any idea on doing it using DAX or M Query.

Thanks in advance !!

[Greg_Deckler · Accepted solution]

Have a look at my Multi-Column Aggregations (MC Aggregations) Quick Measure. This is DAX.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...