cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

## How to SUM the rows in column X which are selected by a DISTINCT on column Y?

I have been stuck with this issue for a while now, I want to sum the Y column, but only on DISTINCT values of Column X. If I would filter in the report on tables(Table1 & Table2), I want the measure to calculate on the table that is selected.

I have tried the following statement, but it returns an error:

Z = DIVIDE(SUMX('myTable'[Y]);DISTINCT(myTable[Table])))

Error:
`A single value for column 'gewicht' in table '_dataerror' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.`

Table structure myTable:

```Table           X                    Y
Table1          supplier             2
Table1          customer             1
Table1          supplier             2
Table2          project              3
Table2          project              3
Table1          customer             1```

The output that I am looking for here is a measure that does the following and returns one value:

If I would select Table 1:

3*2 + 2*1 / (2+1)

If I would select Table 2:

2*3 / 3

How can I get the values of column Y with using a distinct on column X ?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: How to SUM the rows in column X which are selected by a DISTINCT on column Y?

@Anonymous ,

You may try the measure below.

```Measure =
SUMX ( VALUES ( myTable[X] ), CALCULATE ( MAX ( myTable[Y] ) ) )
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: How to SUM the rows in column X which are selected by a DISTINCT on column Y?

@Anonymous ,

You may try the measure below.

```Measure =
SUMX ( VALUES ( myTable[X] ), CALCULATE ( MAX ( myTable[Y] ) ) )
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!