# Thread: Measure to calculate the sum of distinct values

1. Junior Member
Join Date
Feb 2016
Posts
3

## 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? 2. Junior Member
Join Date
Jun 2016
Posts
2

## Hello how are you? I wanted to know if you already know how to do 3. Junior Member
Join Date
Jun 2016
Posts
2

## 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. Junior Member
Join Date
Feb 2016
Posts
3

##  Originally Posted by Alberth 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. calculated member, distinct, measure, mondrian 