Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Measure to calculate the sum of distinct values

  1. #1
    Join Date
    Feb 2016
    Posts
    3

    Default Measure to calculate the sum of distinct values

    Hi,


    I'm wondering if it is possible to create a calculated member to obtain the sum of distinct values for a fact. I will try to explain it with the following example:


    I have a fact where the primary key is related with two dimensions (one to many cardinality). The fact contains a measure and its value is the same for all members of each distinct combination of FACT_ID and DIM_1_ID. For the total, I don't want to consider multiple times the same values. So, with the following values the total should be 450 and not 850 (default Mondrian behavior).

    Code:
    | FACT_ID | DIM_1_ID | DIM_2_ID | MEASURE |
    |---------|----------|----------|---------|
    | 1       | A        | D        | 100     |
    | 1       | A        | E        | 100     |
    | 1       | B        | F        |  50     |
    | 2       | A        | D        | 300     |
    | 2       | A        | E        | 300     |
    |---------|----------|----------|---------|
                              TOTAL | 450     |
    Is it possible? How it can be done with Mondrian?


    Thanks in advance

  2. #2
    Join Date
    Jun 2016
    Posts
    2

    Default

    Hello how are you? I wanted to know if you already know how to do

  3. #3
    Join Date
    Jun 2016
    Posts
    2

    Default

    I researched and found that adding a EXPRESSION MEASURE can make a SQL query where you can select and agupar but not as is the sintaxis

  4. #4
    Join Date
    Feb 2016
    Posts
    3

    Default

    Quote Originally Posted by Alberth View Post
    I researched and found that adding a EXPRESSION MEASURE can make a SQL query where you can select and agupar but not as is the sintaxis
    Hi,

    as far as I know currently it is not possible to do it. I manage to create a calculated measure that actually calculates the right values when data is not filtered. However, the moment there is some filter in place that approach does not work, as the calculated measure uses all the data no matter the filter, and the totals are always the same.

    In order to solve that problem it seems that it would be necessary that Mondrian adds support for EXISTING MDX keyword. There is an open jira for this (http://jira.pentaho.com/browse/MONDRIAN-1180). Feel free to vote for it.

    Regarding the expression measure syntax, have a look to this thread, maybe it could help you: http://forums.pentaho.com/showthread...ression-syntax

Tags for this Thread

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.