cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## SUM - Finding sum of unique value

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

2 ACCEPTED SOLUTIONS
Super User

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.

Proud to be a Super User!

Regular Visitor

Thank you so much. perfectly worked in my data

6 REPLIES 6
Regular Visitor

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

I tried but did not work

Super User

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.

Proud to be a Super User!

Regular Visitor

Thank you so much. perfectly worked in my data

Regular Visitor

Thanks I will try this and let you know

Super User

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.

Proud to be a Super User!

Super User

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

Proud to be a Super User!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors