Sum of distinct values.

[Anonymous]

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...

[AlB]

Hi @Anonymous

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

[Anonymous]

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.

[AlB · Accepted solution]

@Anonymous

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] )
)