cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Sum of distinct values.

Hi, I have a table which I am trying to process to find out the sum of Input Values for distinct Linked Permit numbers.

I can't delete duplicates, because this is a part of other columns, where I need duplicates.

I am trying to do this and it does not work, how hard I tried.

Sum of all input values = sum(TEST[Input Value])
Distinct sum = sumx(DISTINCT(TEST[Linked Permit]),[Sum of all input values])

Logic tells me that DISTINCT should create a context were only distinct Linked Permits exist, and then SUMX should add all Input Values, but it does not work. Can you please help? I searched everywhere and no help...

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Sum of distinct values.

@VLRE

Do you mean the version with DISTINCT should sum only once the duplicate rows? When you talk about duplicates, is it the whole row (i.e. all columns) that are duplicated?

If all rows are duplicated, wouldn't the result you want just what you get now divided by two?

Try these measures, a variation of what you had:

Sum of all input values = sum(TEST[Input Value])
Number Of Duplicates = COUNTROWS(TEST)
Distinct sum =
SUMX (
DIVIDE ( [Sum of all input values], [Number Of Duplicates] )
)
3 REPLIES 3
Super User

## Re: Sum of distinct values.

Hi @VLRE

What is the problem exactly? What is the result as opposed to your expected/required result?

Frequent Visitor

## Re: Sum of distinct values.

The issue is that the result of those measures is literally the same, so no filtering is going on in regards of distinct values (Linked Permit). I think it's visible on the picture above.

Super User

## Re: Sum of distinct values.

@VLRE

Do you mean the version with DISTINCT should sum only once the duplicate rows? When you talk about duplicates, is it the whole row (i.e. all columns) that are duplicated?

If all rows are duplicated, wouldn't the result you want just what you get now divided by two?

Try these measures, a variation of what you had:

Sum of all input values = sum(TEST[Input Value])
Number Of Duplicates = COUNTROWS(TEST)
Distinct sum =
SUMX (