Summerize Everything except These Value

Question

Goal:
Summerize the value of "Reseller Freight Cost" and "Discount Amount" into seperated cell with total value in the table.

Problem:
Everything should be included for total selection of a value except "Road" and "Touring" from the "Product Line" column. How should I do it?

SELECT NON EMPTY { [Measures].[Discount Amount], [Measures].[Reseller Freight Cost] } ON COLUMNS, NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS * [Product].[Product Line].[Product Line].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Answer 1

I think you should use set exceptions - your query amended below: ( double check as I don't have AW): SELECT NON EMPTY { [Measures].[Discount Amount], [Measures].[Reseller Freight Cost] } ON COLUMNS, NON EMPTY

{ ([Product].[Category].[Category].ALLMEMBERS)} * Except([Product].[Product Line].[Product Line].ALLMEMBERS , {[Product].[Product Line].[Product Line].[Road], [Product].[Product Line].[Product Line].[Touring]})

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Answer 2

Also , you can simply add the dataset filtering or tablix filtering with (NOT IN ) .

Answer 3

Also - Your question is not quiet clear to me , the solution it may also be like below:

            with 
            member measures.total as 

            iif([Product].[Product Line].currentmember.name = "road" or 
            [Product].[Product Line].currentmember.name = "Touring"
             ,0, [Measures].[Discount Amount]+[Measures].[Reseller Freight Cost])

            SELECT NON EMPTY { [Measures].[Discount Amount], [Measures].[Reseller Freight Cost],measures.total  } 
            ON COLUMNS, NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS * [Product].[Product Line].[Product Line].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS