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 blank value if one of the ranks is blank.
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.
my pivot table is like below (simple example)
Name Rank1 rank2 rank3
aaa 2 4
bbb 4 7 8
ccc 9 3
Names come from table called employee and rank 1,2 &3 are from tables called score1, score2 and score3. All tables have relationship to Employee table by Id so when the employee is listed it looks at the appropriate rank from score 1,2, and 3 tables to look at their ranks
any help is appreciate it
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 blank value if one of the ranks is blank.
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.
my pivot table is like below (simple example)
Name Rank1 rank2 rank3
aaa 2 4
bbb 4 7 8
ccc 9 3
Names come from table called employee and rank 1,2 &3 are from tables called score1, score2 and score3. All tables have relationship to Employee table by Id so when the employee is listed it looks at the appropriate rank from score 1,2, and 3 tables to look at their ranks
any help is appreciate it
