SUM - Finding sum of unique value

[bhaba123]

I want to find the summation of column E based on the UID. Each UID should count once 

The expected output is given in the last row as 18.

Can you please let me know the DAX?

SUM OF COLUMN E

[rubayatyasmin · Accepted solution]

Hi, @bhaba123 

Here is another solution for you. Previous one won't work because you have same values in ID 6 and 10. 

1. Create a calculated table by distinct count the UID. DAX code is:

DistinctIdTable = DISTINCT('Table'[UID]) - (replace your table with your actual table name) this will result in unique UIDs. 

2. Create calculate column in the newly created table. DAX code:

Web of SCI = LOOKUPVALUE('Table'[WEB OF SCI], 'Table'[Index], 'DistinctIdTable'[Index])

The result should look like this, ignore different column names:

my original data looks like this:

which is sort of similar to yours. 

3. Now, create a measure. 

SumDistinctAmount = SUM('DistinctIdTable'[Amount]) - (replace the Amount column with WEB OF SCI)

Note: Do not forget to draw relationships. Relationship should be one-to-many. 

try this solution and let me know if any further assistance is needed. 

[bhaba123]

Thank you for your message may i know why table[web of science] 2 times ??

I tried but did not work

[rubayatyasmin · Accepted solution]

Hi, @bhaba123 

Here is another solution for you. Previous one won't work because you have same values in ID 6 and 10. 

1. Create a calculated table by distinct count the UID. DAX code is:

DistinctIdTable = DISTINCT('Table'[UID]) - (replace your table with your actual table name) this will result in unique UIDs. 

2. Create calculate column in the newly created table. DAX code:

Web of SCI = LOOKUPVALUE('Table'[WEB OF SCI], 'Table'[Index], 'DistinctIdTable'[Index])

The result should look like this, ignore different column names:

my original data looks like this:

which is sort of similar to yours. 

3. Now, create a measure. 

SumDistinctAmount = SUM('DistinctIdTable'[Amount]) - (replace the Amount column with WEB OF SCI)

Note: Do not forget to draw relationships. Relationship should be one-to-many. 

try this solution and let me know if any further assistance is needed. 

[bhaba123 · Accepted solution]

Thank you so much. perfectly worked in my data

[bhaba123]

Thanks I will try this and let you know 

[rubayatyasmin]

It should work. 2 times because you want to calculate distinct values. Web OF SCI inside DISTINCT will count the distinct rows only. and second Web OF SCI will sum the values. You wanted to calculate WEB OF SCI column distinctly right? SUMX with DISTINCT should work. 

If this doesn't solve your problem, you can attach a demo file. I will try to solve it. 

[rubayatyasmin]

Hi, @bhaba123 

you can use SUMX and then the DISTINCT function to achieve your goal. for example. 

SumDistinctUID = SUMX(DISTINCT('Table'[WEB OF SCI]), 'Table'[WEB OF SCI])

Did I help? If yes, hit 👍 and accept this answer as a solution. 

Thanks