Distinct Sum of Different IDs

[gabrielmission7]

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 ID	Event Label	Activity Code	Hours Left
1	A	10	50
2	A	10	50
3	A	20	100
4	A	30	200
5	A	30	200
6	A	30	200

[Ahmedx · Accepted solution]

[gabrielmission7]

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

[gabrielmission7]

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!

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RiF0amyMHrQ2CP4Y3?e=0yXv9a