In the following table: M1, M2, and M3 are the measures. I have include them in the same fact table. The real case is that I have more than twenty of those measures. To simplify my question, I only included 3.
What I want to do is an average of M1, M2 and M3 when they are not equal to 88 or 99.
Example: average of (M1), average of (M2), average of (M3) as performance tile on the same report.
My challenge is that I can’t get the 'case when then average function' to work from the front end. In the backend OBIEE RPD, I set the aggregation rule as none for those three measures.
anyone has any suggestions?
| ID | M1 | M2 | M3 | Gender |
| 1 | 0 | 5 | 88 | M |
| 2 | - 2.5
| 88 | - 7.5
| F |
| 3 | - 7.5
| 99 | 10 | F |
| 4 | 88 | 10 | 99 | F |
| 5 | 99 | - 2.5
| 0 | M |
Thanks.
