Help to write a DAX measure to average three columns ignoring blanks

Hi All

I was wondering whether someone can help me with the following problem

I have a powerpivot and have created three measure using RANKX. These measures are called Rank1, Rank2 and Rank3. In these measure if cells are blank the rank will return a blank row i.e. it doesn't count it in the ranking.

What I need to do is crate another measure to add (Rank1 + Rank2 + Rank3) /3 but this measure doesn't seem to work of rows in Rank1 2 or 3 contain a blank, It returns a wrong value

What I want is a DAX measure to calculate Sum of (Rank1,2 and 3)/3 in table Employees and if any of the have a Blank, then ignores them i.e. if rank1 = Blank, Rank2=2 and Rank3=3 then it will just add (2+3)/2 and ignore rank1. but if rank1=2, rank2=3 and rank3=4 then the formula should be(2+3+4)/3, in other words I want to find the average of Rank1,2 and 3 and ignoring the blank cells.

I hope this makes sense, I've tried everything and I can seem to get a right answer.

any help is appreciate it

You mean (Rank1 + Rank2 + Rank3) /3 as AVERAGE? If yes try with DAX: AVERAGEA()

Thanks for this. I will try this. Would this ignore blanks? I have not used AverageA before is cry's syntax averagea(table name, rank1,rank2,rank3)?

Thanks again

Hi Sandy666
I tried this and I get eh following error
Too many arguments were passed to the AVERAGEA function. The maximum argument count for the function is 1.

I put in averagea('table1'[Rank1],'table2'[rank2],'table3'[rank3])

my pivot table is like below (simple example)
Name Rank1 rank2 rank3
aaa 2 4
bbb 4 7 8
ccc 9 3

my three tables table1,2 & 3 are all have a relation with name table.
I get the names from name and rank1 from table1, rank2 from table2 and so on

Can I use more than one argument in averagea?

thanks again for your help

I don't like DAX language but you can try read here: DAX: Statistical Functions

I think I have manged to figure this out. thanks for all your help

I wish you luck