I have a simplified example cube used for learning purposes, and to try to figure out a more complex problem.

The cube represents a small web server log, 

* number of hits as a measure
* hostname as a dimension
* http status code as a dimension

I can get a breakdown on number of hits per host and http status code with the MDX

```
SELECT NON EMPTY { [Measures].[CNT HITS] } ON COLUMNS, 
       NON EMPTY { ([DIM NOS STATUSCODE].[Statuscode].[Statuscode].ALLMEMBERS * 
                    [DIM NOS HOST].[HOST].[HOST].ALLMEMBERS ) }  ON ROWS 
FROM [DW]

```

[![enter image description here](https://i.stack.imgur.com/jKcNh.png)](https://i.stack.imgur.com/jKcNh.png)

Now what I would like is to make groups over various HTTP status codes to e.g. show the percentage of successful hits (all 2xx status codes), the percentage unsuccessful hits (all non 2xx status codes). 

I can do this with SQL, but I'm at a loss on how to do it with MDX. e.g. with SQL I'd do:

```
select HOST,  
       sum(CNT_HITS) as HITS ,
       SUM(CASE WHEN s.statuscode div 100 = 2 THEN CNT_HITS ELSE 0 END)/sum(CNT_HITS) * 100 as success_percent,
       SUM(CASE WHEN s.statuscode div 100 = 2 THEN 0 ELSE CNT_HITS END)/sum(CNT_HITS) * 100 as failed_percent,
       sum(CASE WHEN s.statuscode = 401 THEN CNT_HITS ELSE 0 END)/sum(CNT_HITS) * 100 as auth_fail_percent
       from FACT_NOS_HTTPLOG fact  
group by HOST;

```

And for the data shown in the above screenshot, I'd get

```
+-----------------+------+-----------------+----------------+-------------------+
| HOST            | HITS | success_percent | failed_percent | auth_fail_percent |
+-----------------+------+-----------------+----------------+-------------------+
| www.example.com | 1610 |         93.1677 |         6.8323 |            6.2112 |
| www.test.com    |   50 |          0.0000 |       100.0000 |            0.0000 |
+-----------------+------+-----------------+----------------+-------------------+

```

But how can I accomplish this with MDX ?