MDX排序失效求助：基于AdventureWorks Cube取Top5销售子类排序异常

Got it, let's figure out why your sorting isn't working as expected. The core issue here is that your MDX query isn't explicitly tying the sort order to the Internet Sales Amount measure—so it's falling back to the Product Subcategory dimension's default sorting (like member name or internal ID) instead of using your sales metric. Even adding BDESC won't help if it's not linked to the right value.

Here's the correct MDX query that will return the top 5 product subcategories sorted properly by Internet Sales Amount:

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  TOPCOUNT(
    ORDER(
      [Product].[Product Subcategory].[Product Subcategory].MEMBERS,
      [Measures].[Internet Sales Amount], BDESC
    ), 5
  ) ON ROWS
FROM [Adventure Works]

Common reasons your original query failed: #

• No explicit sort metric: If you used TOPCOUNT without specifying the measure to sort by, or ordered the dimension members without linking them to Internet Sales Amount, MDX uses the dimension's default sort order (not your sales numbers).
• Wrong hierarchy level: Make sure you're targeting the exact subcategory level—[Product].[Product Subcategory].[Product Subcategory].MEMBERS ensures you're getting individual subcategory members, not parent categories.
• Default measure mismatch: If your cube's default measure isn't Internet Sales Amount, omitting the metric in TOPCOUNT/ORDER will sort by that default instead of the one you want.

Alternative query (excludes subcategories with no sales): #

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  NON EMPTY
  ORDER(
    [Product].[Product Subcategory].[Product Subcategory].MEMBERS,
    [Measures].[Internet Sales Amount], BDESC
  ) ON ROWS
FROM [Adventure Works]
WHERE TOPCOUNT(
    [Product].[Product Subcategory].[Product Subcategory].MEMBERS,
    5, [Measures].[Internet Sales Amount]
  )

Run either of these, and you'll see the subcategories sorted from highest to lowest sales—Helmets should now appear before Tires and Tubes, matching their actual sales values.

内容的提问来源于stack exchange，提问作者Nicholas Humphrey