Forum Discussion

bhaba123's avatar
bhaba123
Regular Visitor
3 years ago
Solved

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

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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's avatar
    bhaba123
    3 years ago

    Thank you so much. perfectly worked in my data

6 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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

  • bhaba123's avatar
    bhaba123
    Regular Visitor
    3 years ago

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

    I tried but did not work

    • rubayatyasmin's avatar
      rubayatyasmin
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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's avatar
      rubayatyasmin
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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's avatar
        bhaba123
        Regular Visitor
        3 years ago

        Thanks I will try this and let you know