Data Cube Finding Max Values of Columns

Question

I created a cube called Tb_Offers_Cube with the following characteristics. The trouble is using the max function to find the product offered in largest quantity for each city. I keep on getting multiple products for each city yet.

The dimensions of the cube are: Tb_Supplier and Tb_Product.
Measure groups table is: Tb_Offers.
Measure aggregates: SUM(Quantity), SUM(Quantity*Price), 
MAX(Price) , MIN(Price).
Dimension hierarchies:

Tb_Supplier:    State > City > Name
Tb_Product:     Product_Packaging  > Name 
                Product_Category > Product_Line > Name

--For each supplier city find the product offered in largest quantity?

    SELECT [Supplier City], [Product Name], max(DISTINCT [Total Transactions Quantity])
    FROM Tb_Offers_Cube
    WHERE [Supplier Name] is NULL
    AND [Supplier State] is NOT NULL
    AND [Supplier City] is NOT NULL
    AND [Product Name] is NOT NULL
    AND [Product Packaging] is NOT NULL
    AND [Product Category] is NULL
    AND [Product Line] is NULL
    GROUP BY [Supplier City], [Product Name], [Total Transactions Quantity]
    ORDER BY [Supplier City], [Total Transactions Quantity] DESC;

Answer 1

You can get the max product name to include in your display for each city.

SELECT [Supplier City]    
    , [Total Transactions Quantity]
    , (SELECT TOP 1 [Product Name] FROM Tb_Offers_Cube WHERE [Total Transactions Quantity] = t.[Total Transactions Quantity]
        AND [Supplier City] = t1.[Supplier City]) AS [Product Name]
FROM 
    (SELECT [Supplier City]    
        , max(DISTINCT [Total Transactions Quantity]) [Total Transactions Quantity]
    FROM Tb_Offers_Cube
    WHERE [Supplier Name] is NULL
    AND [Supplier State] is NOT NULL
    AND [Supplier City] is NOT NULL
    AND [Product Name] is NOT NULL
    AND [Product Packaging] is NOT NULL
    AND [Product Category] is NULL
    AND [Product Line] is NULL
    GROUP BY [Supplier City]) t
ORDER BY t.[Supplier City], t.[Total Transactions Quantity] DESC;