OBIEE Reports Aggregation Question

2806807

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.