none
Finding max for category

    Question

  • Hi guys, I'm struggling to find a simple solution but I can't...problem:

    I got a cube with measure price and a dimension with six company ( for example Ferrari, Mercedes, Ford, etc). Now I 'd like to find the max price regarding all companies ( Mercedes 100, Ferrari 200, Ford 50)..in this case 200. That's because I want see the purchase simulation, Mercedes qty*maxprice (so prize Ferrari), Ford qty*maxprice and go on.

    I tried with: max([Prodotto].[Category].members, [Measures].[price]) but it returns me a huge value and not 200...

    Any suggestion?

    Monday, September 16, 2013 2:56 PM

Answers

  • Hi Diegoctn,

    We can also create User Defined member property for the "Prodotto" dimension. Please refer to "Product ->List Price" attribute relationship in the Adventure Works sample database. We can use the following MDX query to get the expected result:

    WITH  MEMBER [Measures].[MaxValue] AS
          MAX([Product].[Product Categories].CurrentMember.Children, [Product].[Product].Properties("List Price")) 
    SELECT 
          {[Measures].[MaxValue]} ON COLUMNS,
          {[Product].[Product Categories].[Subcategory].Members} ON ROWS 
    
    FROM  [Adventure Works]

    For more information about how to create User Defined member properties, please refer to the following article:
    http://sornanara.blogspot.in/2012/10/ssas-how-to-create-user-defined-member.html

    You can also implement ShankarGs's solution to create a new measure with MAX usage. If a measure's Aggregate Function property value is Max, the measure value for a cube cell is calculated by taking the highest value in the measure's source column from only the rows for the combination of members that defines the cell and the descendants of those members.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, September 17, 2013 6:48 AM

All replies

  • The huge number is result of your measure. Its not set to a aggregation function. You would want the maximum to be semiadditive, So add an new measure based on price to your cube and set its AggregationFunction property to Max or change the property to to be an aggregation function. 

    http://technet.microsoft.com/en-us/library/ms175623.aspx#AggFunction


    • Edited by ShankarGS Monday, September 16, 2013 5:58 PM
    • Proposed as answer by ShankarGS Tuesday, September 17, 2013 4:37 PM
    Monday, September 16, 2013 4:14 PM
  • Hi Diegoctn,

    We can also create User Defined member property for the "Prodotto" dimension. Please refer to "Product ->List Price" attribute relationship in the Adventure Works sample database. We can use the following MDX query to get the expected result:

    WITH  MEMBER [Measures].[MaxValue] AS
          MAX([Product].[Product Categories].CurrentMember.Children, [Product].[Product].Properties("List Price")) 
    SELECT 
          {[Measures].[MaxValue]} ON COLUMNS,
          {[Product].[Product Categories].[Subcategory].Members} ON ROWS 
    
    FROM  [Adventure Works]

    For more information about how to create User Defined member properties, please refer to the following article:
    http://sornanara.blogspot.in/2012/10/ssas-how-to-create-user-defined-member.html

    You can also implement ShankarGs's solution to create a new measure with MAX usage. If a measure's Aggregate Function property value is Max, the measure value for a cube cell is calculated by taking the highest value in the measure's source column from only the rows for the combination of members that defines the cell and the descendants of those members.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, September 17, 2013 6:48 AM
  • Hi, thank you to everyone. I'm still go on struggling to find a solution. I tried with the Aggregate Function Max but it returns me the max price for every dimension. I mean, Category-->Nation it returns the max price for every Category and Nation. 

    I tried with 

    CREATE MEMBER CURRENTCUBE.[Measures].maxprice
    AS MAX([Product].[categoria].CurrentMember.Children, [Measures].[price]) 

    but it returns me null. 

    I tried using TSQL but it's a mess. I'm looking just to have the max price for each category and stop. That's mean that if I got six category I'll work with six different maxprice. When I call for example Category-->Nation and measures price I'll have 6 category for 6 nation so 36 different price but just 6 max price repeated six time

    Spain Ferrari 200 200

    Usa    Ford      50 200

    France Ford     80 200

    Japan Kawasaki 70 100 ( the maxprice in the motorbike category)

    Italy Suzuki  90  100

    France Suzuki 100 100

    It's possible?

    • Edited by DIEGOCTN Tuesday, September 17, 2013 9:08 AM
    Tuesday, September 17, 2013 9:05 AM
  • I got it. I used an aggregate function but I had to struggle with a strange behaviour. 

    I created in TSQL a field maxprice, giving for each category the max price ( I know it's pretty bady but I hadn't any other solution). After I put the new aggregation like MAX but it returned me strange values or null. So I tried with MIN and it worked perfectly. I'd like to understand why...however in the aggregate function I've seen that you cannot use the average ( only data average), another strange, for me, feature.

    Tuesday, September 17, 2013 4:19 PM