Forum Discussion

sjacob's avatar
sjacob
Frequent Visitor
2 years ago
Solved

DAX Calculation for Unique Sum

Hello, I would like to get some help and support with the following DAX I've created:

 

Direct Group Time =
SUMX(
    FILTER('crdb contact', NOT(ISBLANK('crdb contact'[ContactGroupID])) && 'crdb contact'[ContactGroupID] <> 0),
    [Direct Contact Time]
)
 
I'm trying to get the sum of minutes from the Direct Contact Time measure from each unique ContactGroupID. Here is a table called Contact as an example. From the above DAX I'm wanting to get the sum of time from a unique ContactGroupID, instead, the above is calculating all the rows. The Direct Contact Time is a measure I've created that sums the Time 1 to Time16 columns. So for example, with the above calculation, I want a sum of 60 + 20 to show for ContactGroupID 1122 and not 60 + 20 + 60 + 20. Appreciate any help and support with this:
 
DateContactIDContactGroupIDTime1Time2
Oct 512311226020
Oct 511211226020
Oct 714222333030
Oct 712522333030
Oct 745622333030
Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    sjacob Try this:

     

    Measure 3 = 
SUMX ( 
    SUMMARIZE ( 
        FILTER ( MyTable2, MyTable2[ContactGroupID] <> 0 ),
        MyTable2[ContactGroupID], 
        MyTable2[Time1], 
        MyTable2[Time2] 
    ), 
    [Time1] + [Time2] 
)

8 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    sjacob try this measure:

     

    Measure 3 = SUMX ( SUMMARIZE ( MyTable2, MyTable2[ContactGroupID], MyTable2[Time1], MyTable2[Time2] ), [Time1] + [Time2] )
    • sjacob's avatar
      sjacob
      Frequent Visitor
      2 years ago

      Thank you for your response. I tried this measure, but it still sums all the rows from this table. I want to be able to sum a unique ContactGroupID, so like the first row of each ContactGroupID.

    • sjacob's avatar
      sjacob
      Frequent Visitor
      2 years ago

      Hi parry2k thank you for the response. I did a matrix visualization and I can actually see it working now thank you. However there's a missing piece I forgot to mention. I would like to only get this calculation for the unique rows with a ContactGroupID that is not 0. Looking forward to your response.

    • sjacob's avatar
      sjacob
      Frequent Visitor
      2 years ago

      Hi parry2k thanks for your response. Sure, for example here is sample table:

       

      DateContactIDContactGroupIDTime1Time2
      Oct 512311226020
      Oct 511211226020
      Oct 714222333030
      Oct 712503030
      Oct 745603030

       

      From the DAX calculation, I would only want the sum of the unique rows of ContactGroupID that don't equal 0. So just the first row and third row for example.

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    sjacob Try this:

     

    Measure 3 = 
SUMX ( 
    SUMMARIZE ( 
        FILTER ( MyTable2, MyTable2[ContactGroupID] <> 0 ),
        MyTable2[ContactGroupID], 
        MyTable2[Time1], 
        MyTable2[Time2] 
    ), 
    [Time1] + [Time2] 
)
    • sjacob's avatar
      sjacob
      Frequent Visitor
      2 years ago

      Hello parry2k thank you very much for this update. This works perfectly! Appreciate your support with this.