Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
bhaba123
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 ESUM OF COLUMN E

2 ACCEPTED SOLUTIONS

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:
 
rubayatyasmin_0-1689569378398.png

my original data looks like this:

rubayatyasmin_1-1689569436733.png

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. 
 
rubayatyasmin_0-1689517080227.png

Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

Thank you so much. perfectly worked in my data

View solution in original post

6 REPLIES 6
bhaba123
Regular Visitor

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

I tried but did not work

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:
 
rubayatyasmin_0-1689569378398.png

my original data looks like this:

rubayatyasmin_1-1689569436733.png

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. 
 
rubayatyasmin_0-1689517080227.png

Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Thank you so much. perfectly worked in my data

Thanks I will try this and let you know 

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_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


rubayatyasmin
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


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors