none
Create calculated measures as row level

    Question

  • Hi, 

    I am new to SSAS. I have the following scenario and not sure how to address it

    I have a Fact Table as below and fed to my SSAS Cube, i need to create a  few complex  Calculated metric based on the available metric from my Fact Table (eg of simple calculated Metric is Price = Revenue/Sales Volume.)

    When it comes to Cube, users want to see metric as rows (as we have 80 over metrics) and not as calculated columns, how can i achieve that. My final output should be similar to below fact based on the slicers the users pick up. Since the Price metric is non aggreagatable an cannot be rolled up i have issues. 

    Appreciate if anyone could throw light on this or provide any links to any samples

    Metric, Company, Commodity all has Hierarchy to it

    Company Commodity Product Counterparty Metric MeasureValue
    A C1 P1   SalesVolume 1000
    A C1 P1 CP1 Revenue 5000
    A C1 P1 CP2 Revenue 10000



    Thanks

    Hema


    

    Tuesday, March 21, 2017 3:17 AM

All replies

  • Hi Hema,

    Thanks for your question.

    If i understand you correctly, "Price" metric should be aggragatable dimension. To get the desired results,you can just do a crossjoin with all these dimension.

    See my sample MDX query in Adventure Works below:
    select [Measures].[Order Count] on 0,
    [Product].[Category].&[1]*
    [Product].[Subcategory].[Subcategory]*
    [Product].[Size].[Size]*
    [Product].[Class].[Class]*
    [Product].[Weight].[Weight]*
    [Product].[Size Range].[Size Range] on 1
    from [Adventure Works]


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, March 21, 2017 7:09 AM
  • Hi Wilson,

    Thanks for your quick response.

    Not sure i have convened the requirement correctly. My user can load the data into DWH on any level based on specific metric, ie they can load data at product level or counterparty level or combination of this, so when it comes to cubes, it has to rollup the data according to measure. This kind of functionality - grouping Measure and creating calculated measure based on existing measure is available in Cognos,power play cubes, i am looking for similar functionality.

    i.e. data will be loaded into cubes by individual measures as dimensions and i need to created calculated measures based on the fixed measures, but slicers(other dimensions than measure like product, company, counterparty) will be chosen by the users while consuming the cubes and the calculated measure should appear as one more record bases on the slicers

    My Current Cube will give me data like 

     Sales - Export - Product1 - Couterparty1 - 5000

     Sales - Domestic - Product2 - Couterparty1 - 2000

    Revenue - Export - Product1- <Null for counterparty> - 10000

    I want to create a Price metric = Revenue/Sales and when the qry the cube the result should be 

    Price - Export - Product1 - Counterparty1 - Null ( as no revenue is there) (when counterparty dim is selected)

    Price - Export - Product1-  2 (10000/5000) (when counterparty is not Selected)

    Tuesday, March 21, 2017 8:50 AM
  • Hi Hema,

    First of all, in MDX, the columns and rows axis do not play any special row like they do in relational databases. Whatever you can put on the rows, you can as well put on the columns and vice versa.

    The basic structure of an MDX statement (slightly simplified) is

    SELECT <set1> ON COLUMNS, <set2> ON ROWS FROM <cube>

    And by just switching these sets, you can switch the result structure:

    SELECT <set2> ON COLUMNS, <set1> ON ROWS FROM <cube>

    There is no requirement that measures must be on columns (except that some client tools like SQL Server Reporting Services require that, but that is a restriction of that tool, not of the cube or MDX).

    Having said that, now let's look at the price calculation. I would implement that as a calculated measure in the calculation script of the cube, somehow similar to

    CREATE MEMBER CURRENTCUBE.[Measures].[Price] AS [Measures].[Revenue] / [Measures].[Sales Volume];

    or, better 

    CREATE MEMBER CURRENTCUBE.[Measures].[Price] AS IIf([Measures].[Sales Volume] = 0, NULL, [Measures].[Revenue] / [Measures].[Sales Volume]);

    The first expression is technically completely valid, but it would display +INF or -INF (plus/minus infinity) should the Sales Volume be 0 or null, a result which is to some extent technically correct, but not what users expect to see normally, and hence the IIf expression prevents that.

    Frank


    • Proposed as answer by FrankPl Tuesday, March 21, 2017 7:31 PM
    Tuesday, March 21, 2017 7:30 PM
  • Thanks Wilson,

    I did mange to create the calculated Measures for Revenue, Sales , Price . We have few complexities over here. Eventually we will be having 80 over measures, some are Aggregated measures ( Eg:  Inventory: Inventory - Port Inventory- Finished Goods and so i have a hierarchy in measures). Users feels that its hard for them to locate each measure when they pivot it using excel or power bi also we have 3 scenarios for measure (Actual, Budget , outlook) and their kpi which they want to see side by side so they prefer to view in row format

    ie my Pivot from cube should be like

    Rows -  Required Dimensions , Measure Hierachy (Sales (Export, Domestic, Retail), Revenue (Export, Domestic, Retail), Price (Export, Domestic, Retail), Inventory(Port, Goods)... Cost  etc)

    Columns - Period hierarchy and Scenarios 

    Values - One single Field that is Measure value

    Under normal scenario i can get this, my only issue is when there is calculated measure. I tried even rollup of data and filter in datasource level, but complexities few are Aggregated as Average and few are Sum , few are calculation based on few other metrics




    Wednesday, March 22, 2017 10:20 AM
  • Hi Hema,

    I would suggest to put Export, Domestic, Retail into a separate attribute. This would then reduce the number of measures to one third, but would require the users to add that attribute to the rows (depending on the tool you use).

    Things that are calculated after aggregation are normally best implemented as calculated measures in the calculation script of the cube. Things that need to be calculated before aggregations are normally best put into the data source view.

    Frank

    Wednesday, March 22, 2017 10:56 AM
  • Hi Frank,

    I roughly understand that the SSAS doesn't allow to group the measure and dimension on rows which is what i was looking for using excel, but we managed to achieve the functionality using Tableau, probably we cannot expose the cubes directly to user, we need to look for reporting tool as well. 

    Thanks

    Friday, March 24, 2017 2:08 AM
  • Hi Hema,

    Measures can be put on rows in MDX, but not some measures on rows and some on columns in the same query. And calculated measures are just measures with regard to this rule.

    However, calculated members need not be measures. In fact, you can build calculated members on every attribute or hierarchy.

    There are some restrictions that some client tools have, like Reporting Services requiring that measures and only measures are on the columns.

    Frank

    Friday, March 24, 2017 2:29 AM