cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VLRE Frequent Visitor
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...
 
Capture1.PNG

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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 (
    DISTINCT ( TEST[Linked Permit] ),
    DIVIDE ( [Sum of all input values], [Number Of Duplicates] )
)
3 REPLIES 3
Super User
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?

VLRE Frequent Visitor
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
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 (
    DISTINCT ( TEST[Linked Permit] ),
    DIVIDE ( [Sum of all input values], [Number Of Duplicates] )
)