Distinct Count for report with sum of duration for more than 5 hours

[Hamdan1234]

Report	Start Date	User	Designation	Duration	Team
A	28/07/2021	Arun	128 Fe	1.17	Editing
A	28/07/2021	Ranjan	120 Nm	0	Research
C	27/07/2021	John	106 Lm	3	Research
D	27/07/2021	Jason	108 Re	2.67	Editing
B	27/07/2021	Varsha	120 Nm	3.5	Research
B	28/07/2021	John	106 Lm	0.75	Research
C	28/07/2021	Jason	120 Nm	0	Editing
A	28/07/2021	Mamta	106 Lm	1.42	Editing
D	28/07/2021	Ronnie	108 Re	0	Research
D	27/07/2021	Ranjan	120 Nm	2.5	Research
D	27/07/2021	Arun	106 Lm	0.58	Editing
A	28/07/2021	Varsha	128 Fe	0	Research
B	27/07/2021	Mamta	108 Re	0.17	Editing

I am facing problem with DAX formula. I want to have "Count of reports which has more than 5 hours duration logged per month"which means each report which has sum of duration more than 40 hours should be counted other should be left behind. What should I do?

The count should be only for report which has more than 5 hours of duration else every thing should not be counted. I have shown the outcome as well as attached the file of data. Please do help.

Thanks

[parry2k · Accepted solution]

@Hamdan1234 add the following measures:

Sum Duaration = SUM ( 'DataTable'[Duration] )


Count above 5 = 
COUNTX ( 
    SUMMARIZE ( 'DataTable', 'DataTable'[Report], "@Hours", [Sum Duaration] ), 
    IF ( [@Hours] > 5, 1 ) 
) 

in a table visual, drop Report column and above measures.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡