Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Mixing percentage and absolute values in same column

  1. #1
    Join Date
    Aug 2011
    Posts
    6

    Question Mixing percentage and absolute values in same column

    Hello all,

    Is is possible, with MDX, to create a table with both absolute values and percentage in the same columns ?

    The table should display # of transactions and amount spent (measures) per each of 3 categories (dimensions). Thus far, I have it figured out. But I also want to add a row that shows the % of people & amounts in these 3 categories across all (many more) categories.

    Example:
    Total transactions : 100
    Total amount spent : 1000$

    My Current table:

    ---------+---------+----------
    category | # trans | $ spent
    ---------+---------+----------
    food | 20 | 300$
    toys | 40 | 200$
    tools | 10 | 100$


    Now I want to add another row that shows the percentage of ( sum of all categories ) across the grand totals:
    ---------+---------+----------
    % | 70 % | 60 %

    ---------+---------+----------

    Is it possible to achieve this in a single query ?

    This is the query, without the % :

    WITH
    MEMBER [Measures].[# trans] AS ........
    MEMBER [Measures].[$ spent] AS ........
    MEMBER [Measures].[TOT trans] AS ......
    MEMBER [Measures].[TOT spent] AS .....
    SELECT
    { [Measures].[# trans], [Measures].[$ spent] } ON COLUMNS
    { [Categories].[food], [Categories].[toys], [Categories].[tools] } ON ROWS

    The row I need to add is not a category. It's would be (I think) a calculated member that sums the # trans (or $ spent) across these 3 categories, divided by [Measures].[TOT trans], formatted as %

    Any help would be greatly appreciated !

  2. #2
    Join Date
    Aug 2011
    Posts
    6

    Default

    I've resolved this problem, here's my solution to applying mixed formatting across rows & columns:

    WITH

    MEMBER [Measures].[# trans] AS ( ... )

    MEMBER [Measures].[$ spent ] AS ( ... )

    MEMBER [Categories].[food] AS ( ... ),
    FORMAT_STRING = IIF( [Measures].CurrentMember.Name = "$ spent", "$0;($0);", "" )

    MEMBER [Categories].[toys] AS ( ... ),
    FORMAT_STRING = IIF( [Measures].CurrentMember.Name = "$ spent", "$0;($0);", "" )

    MEMBER [Categories].[tools] AS ( ... ),
    FORMAT_STRING = IIF( [Measures].CurrentMember.Name = "$ spent", "$0;($0);", "" )

    MEMBER [Categories].[Total] AS ( ... )
    FORMAT_STRING = IIF( [Measures].CurrentMember.Name = "$ spent", "$0;($0);", "" )

    MEMBER [Categories].[% Total] AS ( ... )
    FORMAT_STRING = "Percent"

    SELECT
    {
    [Measures].[Accounts ],
    [Measures].[Receivables]
    } ON COLUMNS,
    {
    [Account.Status].[food],
    [Account.Status].[toys],
    [Account.Status].[tools],
    [Account.Status].[Total],
    [Account.Status].[% Total]
    } ON ROWS

    FROM
    [MyCube]


    And the results display as follow:

    ---------+---------+----------
    category | # trans | $ spent
    ---------+---------+----------
    food.....|....20...|...300$...
    toys.....|....40...|...200$...
    tools....|....10...|...100$...
    Total....|...100...|..1000$...
    % Total..|....70%..|....60%...


    Now I'm happy. Mixed formatting across rows, and across columns as well

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.