conditional grouping of dimension members

I struggling with conditional grouping of some members within query.

In my simplified example I have two dimensions and one measure. The first dimension contains some categories and the second dimension some units. In all but one category, all categories have a single unit (category A and B in the example). However, one category has multiple units. If I put both dimensions on rows I get multiple results for category C (as in the following example). What I really need is to get one line per category, with the Amount appropriately aggregated but also with the displayed units (in case of category C, they should be replaced either by single "none" or concatenated together if this is not possible ("km, kg" in the example)).

The following example should illustrate what I am trying to achieve.

With the following query:

    SELECT 
          NON EMPTY { [Measures].[Amount] } ON COLUMNS, 
          NON EMPTY { ([Grouping A].[Category].[Category].ALLMEMBERS * 
                       [Grouping B].[Unit].[Unit].ALLMEMBERS ) } ON ROWS 
    FROM [ExampleCube]

I get data like this:

Category     Unit     Amount
A            km         10
B            km         5
C            km         5
C            kg         2
C            km         5

But what I really need to achieve is to group the dimension Category and treat the dimension Unit and measure Amount appropriately as follows:

Category     Unit     Amount
A            km         10
B            km         5
C            none       12

Maybe this is really simple but I have been trying to solve this for a while with no results.

Any ideas would be appreciated.

Thanks.

Solution

It's not simple. OLAP hasn't been designed for this kind of things. Nonetheless, you can solve it in the following way:

With 
[Measures].[Unit] as 
IIF(
    NonEmpty(
        [Grouping B].[Unit].[Unit].Members,
        [Measures].[Amount]
    ).Count > 1,
    "None",
    NonEmpty(
        [Grouping B].[Unit].[Unit].Members,
        [Measures].[Amount]
    ).Item(0).Name
)


Select
Non Empty { [Measures].[Unit],[Measures].[Amount] } on 0,
Non Empty { [Grouping A].[Category].[Category].Members } on 1
From [ExampleCube]