Incorrect results in TOPCOUNT query for some attributes

Question

I am trying to find out which year had the highest reseller sales across each of the State-Province. I am using Adventure Works DW 2008 R2 database.

Here is the query that I have got -

SELECT { [Reseller Sales Amount] } ON COLUMNS,
{
  Generate (
    [Geography].[Geography].[Country].Members,
    TopCount (
      Order (
        Descendants ( 
           [Geography].[Geography].CurrentMember
         , [Geography].[Geography].[State-Province] 
        ) 
       *[Date].[Calendar].[Calendar Year].Members,
        [Reseller Sales Amount],
        DESC
      ),
      1
    )
  )
} ON ROWS
FROM [Adventure Works]

When it comes to France it's displaying (null) as result set. Also why is it not listing all the State-Provinces but only select few of them? Is it also possible to get the Country alongside the results. When I tried another cross-join I get error saying Geography hierarchy already exists.

Answer 1

To get rid of the null you can use BDESC rather than DESC. The extra B means that all natural hierarchy order will be Broken

SELECT { [Reseller Sales Amount] } ON COLUMNS,
{
  Generate (
    [Geography].[Geography].[Country].Members,
    TopCount (
      Order (
        Descendants ( 
           [Geography].[Geography].CurrentMember
         , [Geography].[Geography].[State-Province] 
        ) 
       *[Date].[Calendar].[Calendar Year].Members,
        [Reseller Sales Amount],
        BDESC //<<< 
      ),
      1
    )
  )
} ON ROWS
FROM [Adventure Works];

To add in country and avoid the error that you are seeing use the unused attribute hierarchy [Geography].[Country] rather than the user hierarchy [Geography].[Geography] which you already have on the 1 axis:

SELECT 
  {[Reseller Sales Amount]} ON COLUMNS
 ,{
    Generate
    (
      [Geography].[Geography].[Country].MEMBERS
     ,TopCount
      (
        Order
        (
            [Geography].[Country].[Country]*
            Descendants
            (
              [Geography].[Geography].CurrentMember
             ,[Geography].[Geography].[State-Province]
            )*
            [Date].[Calendar].[Calendar Year].MEMBERS
         ,[Reseller Sales Amount]
         ,bDESC
        )
       ,1
      )
    )
  } ON ROWS
FROM [Adventure Works];

Results are the following: