How to get the average through different table

[Anonymous]

Hi,

I'm sorry for bothering but I really want to ask the question. What to do if I want to get the average score for the below attribute while ignoring the blank value. As I a begginer what I do to get the bar chart is I sum all the value for the attribute like this and make the average for the value like this in the image below. But when I doing it like this it does not ignore the blank value when I doing the average because all the CP is from the different table. What I should do gurus to make it ignore the blank value by filter it by region and month.

Example is like this. It still divide by 9 even other value is 0.

Thank you so much in advance for the help given.

[Anonymous · Accepted solution]

I already found the solution for this.

Average for Customer Promise =

VAR Count_ =

INT ( Table 1<> BLANK () )

+ INT ( Table 2 <> BLANK () )

+ INT ( Table 3 <> BLANK () )

RETURN

DIVIDE ( Table 1+ Table 2 + Table 3, Count_ )

[amitchandak]

@Anonymous ,

Averagex(Values(Table[Customer]),[Sum of CP])

[Anonymous]

Hi @amitchandak , I've try the method you give but it doesn't work as the table for customer is the combination of my sheet in the field. so when i try it it turn on error. because i dont have one table for all the customer value. 

[amitchandak]

@Anonymous , in Values you need to give group by or the not summarized column of visual and that should close by )  like customer

[Anonymous]

I try to group the measures into one table like the image below but when I insert the the formula for the averagex it still error.

it stated that the group does not have the column why it happen like that?

[amitchandak]

@Anonymous , is the Sum of CP is measure?  Also, you can not use values on a table in this case , you need to give a column

[Anonymous]

yes the sum of cp is the measure where i sum all the 9 percentage in the table so that I can divide by 9 but the disadvantage that happen is it ignore all the blank value as it divide all the value by 9 eventhough there is table that have less than 9 values as there is blank value in it.