DAX Grouping and sum

Question

I have the following DAX query for the Adventure Works DB:

evaluate
(
   summarize
   (
      'Internet Sales',
      'Product Category'[Product Category Name],
      'Product Subcategory'[Product Subcategory Name],
      'Product'[Product Name],
      'Date'[Calendar Year],
      "Total Sales Amount", sum('Internet Sales'[Sales Amount])
   )
)
order by 'Product Category'[Product Category Name],
         'Product Subcategory'[Product Subcategory Name],
         'Product'[Product Name]

This returns the data in this format:

Accessories Bike Racks  Hitch Rack - 4-Bike 2008    22920
Accessories Bike Racks  Hitch Rack - 4-Bike 2007    16440
Accessories Bike Stands All-Purpose Bike Stand  2008    20670

I want to return it as this:

CATEGORY        SUB CATEGORY    PRODUCT                 2007     2008      2009     2010
Accessories Bike Racks  Hitch Rack - 4-Bike 22920    16440     22920    16440
Accessories Bike Stands All-Purpose Bike Stand  20850    20670     22920    16440

There should be a column for every year in the result set.

Is this possible? if so how?

Thanks

Answer 1

You probable need the ADDCOLUMNS() function, not quit sure though

http://technet.microsoft.com/en-us/library/gg492204.aspx