Countif formula or table

[Warhawk]

Hi,

This countif or the DAX version of it is my kryptonite for sure...

I have a task that's really easy to do in Excel and I'm pretty sure can be done in Power BI but I can't figure it out how.

I have a dataset like this:

What I want to do is to count the days where the sum of the spent_hours for a given site is higher than a preset formula (sum of all hours for that site for the week and divided by 14).

So for example for site 1 the value I want to compare to is 0,382643 (sum of all hours for week29 divided by 14) and the end result I want to see is 2 because on days 09.11. and 09.12. the sum of spent_hours are higher than the one I've calculated.

Is there a way to do this in a DAX formula? Or should I add a new table that calculates the daily spent_hours (so takes out the report_type column)? Or is there a better method? All ideas are welcome. 🙂

(I don't know if it's important but country, cluster, week and site are all in slicers.)

[Greg_Deckler · Accepted solution]

Sounds like you want to use SUMMARIZE to summarize your data and then COUNTX with a FILTER to filter it down to just what you want.

[Warhawk]

Thank, this seems to be working!