Forum Discussion

AppleMan's avatar
AppleMan
Icon for Helper III rankHelper III
2 years ago
Solved

Summing column once per distinct value in table

I feel this should be easy, but I have not been able to write a measure that accomplishes it how I need it to. 

 

To explain, here is an example table of how my data looks:

JobOrder DateCreate DateAmount
11/1/20242/202354,500
21/1/20242/202368,400
11/1/20242/202354,500
21/1/20242/202368,400

 

I need to write a measure that would take the above table and sum the amount column only for each distinct job. In this example this would be 54,500 + 68400. The other part of the specifications here is this must be wrapped in a calculation function so I can use:

USERELATIONSHIP(Test[CreateDate], 'Date Table'[Date])
 
The basic idea is to find the total amount, per unique job, for a given period based on create date (what im using userelationship for since this table is joined to date table on order date for every other function im using).
I have tried doing this through SUMX but have not been able to find just the correct way to write this.
 
Let me know if any other information is needed, thanks!
Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    AppleMan,

     

    Try this measure:

     

    Sum Distinct Amount =
VAR vTable =
    SUMMARIZE ( Test, Test[Job], Test[Amount] )
VAR vResult =
    CALCULATE (
        SUMX ( vTable, Test[Amount] ),
        USERELATIONSHIP ( Test[CreateDate], 'Date Table'[Date] )
    )
RETURN
    vResult

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    AppleMan,

     

    Try this measure:

     

    Sum Distinct Amount =
VAR vTable =
    SUMMARIZE ( Test, Test[Job], Test[Amount] )
VAR vResult =
    CALCULATE (
        SUMX ( vTable, Test[Amount] ),
        USERELATIONSHIP ( Test[CreateDate], 'Date Table'[Date] )
    )
RETURN
    vResult

     

     

    • AppleMan's avatar
      AppleMan
      Icon for Helper III rankHelper III
      2 years ago

      Hi thank you, that seems to be working somewhat. I am having an issue where the date slicers (which are hooked up to a different column than the create date) are not properly filtering these values as they should and are filtering them based on the wrong date field. I have used UseRelationship many times so not sure why it is not functioning as intended. But I can see in the table when testing that it is only including one line for the amount per job so I will mark your answer as the solution. I just need to troubleshoot this date issue next.