Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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:

 Date ContactID ContactGroupID Time1 Time2 Oct 5 123 1122 60 20 Oct 5 112 1122 60 20 Oct 7 142 2233 30 30 Oct 7 125 2233 30 30 Oct 7 456 2233 30 30
1 ACCEPTED SOLUTION
Super User

@sjacob Try this:

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

8 REPLIES 8
Super User

@sjacob Try this:

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Super User

@sjacob can you explain with example data?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

 Date ContactID ContactGroupID Time1 Time2 Oct 5 123 1122 60 20 Oct 5 112 1122 60 20 Oct 7 142 2233 30 30 Oct 7 125 0 30 30 Oct 7 456 0 30 30

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.

Super User

@sjacob not sure why but it is working for me, how you are visualizing it:

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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.

Super User

@sjacob try this measure:

``Measure 3 = SUMX ( SUMMARIZE ( MyTable2, MyTable2[ContactGroupID], MyTable2[Time1], MyTable2[Time2] ), [Time1] + [Time2] ) ``

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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.

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors