Forum Discussion

gabrielmission7's avatar
gabrielmission7
Frequent Visitor
3 years ago
Solved

Distinct Sum of Different IDs

Hello,

I have a table similar to the one below and want to get sums of the totals Hours left for all the activity codes; in this case, it should be 50 + 100 + 200 (350). However, I keep on getting 800 (50 + 50 + 100 + 200 + 200 + 200) even though I used DISTINCT function. 

Distinct Total Hours Left=
    SUMX(
        DISTINCT(
                'Event'[Activity Code]
            ),
        CALCULATE(SUM('Event[Hours Left]),
        'Event[Event Label] = "A")
    )
 
Thank you!

 

Table Event

Event IDEvent LabelActivity CodeHours Left
1A1050
2A1050
3A20100
4A30200
5A30200
6A30200

 

Need Help

4 Replies

  • gabrielmission7's avatar
    gabrielmission7
    Frequent Visitor
    3 years ago

    Thank you so much Ahmedx ! I'm running an older version of PBI desktop (Feb, 2023), and it's a company laptop so I can't upgrade it myself. I can't open the PBI file as a result. Would you be able to put the dax code here instead? Thanks a lot!

    • gabrielmission7's avatar
      gabrielmission7
      Frequent Visitor
      3 years ago

      thank you so much Ahmedx! That works! I also had my PBI upgraded to the latest release and was able to download the file.