Calculate total count of days based on previous measures.

[AMONT]

Hello, first time poster, but I've been dabbling in PowerBI for awhile now...

I have a dataset of groundwater readings for several wells, and I am trying to calculate the number of days where a groundwater level read over a certain reference value (-12), for 3 different hour intervals (12am, 8am, and 4pm), for a given well. So the day can only be counted if the groundwater exceeded -12 for those three instances. 

So far I have the following calculations: 

Calc'd Column: ExceedRef = if(water level >-12, 1,0) -- this could result in as many as 12 readings for a given hour, if the well read at 5 min intervals, or as low as 1 if it only read once in an hour.

Measure (since some wells read multiple times within an hour, this was to condense the value to a single reading for the start of the hour: 

Avg Exceeds Ref =

ROUND(AVERAGEX(

    KEEPFILTERS(VALUES('All Stations'[Hour Start])),

    CALCULATE(AVERAGE('All Stations'[Exceeds Ref]))

),0)

Measure (with a filtered dataset to 12am, 8am, 4pm, the result ranges from 0 to 3, with three meaning it exceeded the reference level at each hour interval):

Total Exceeds Ref =

ROUND(SUMX (

    VALUES ('All Stations'[Hour Start]),

    AVERAGE ('All Stations'[Exceeds Ref])

),0)

Measure, if a count was 3, assign a value of 1.

Ref Count = IF([Total Exceeds Ref] = 3, 1, 0)

The goal with the last measure is to have it count a given well on a given day, should the Ref Count = 1 for that day. However, the measure doesn't aggregate and I'm stuck. 

I've attached some screenshots showing how the output looks for these fields. In the table on the left, for Well 16, on 5/23/2025, there's 3 readings for the 3 specific hours, all exceeding -12, so the Total Exceeds Ref when shown with the DateOnly (table on the right), and the Ref Count is 1. For 5/24/2025, there were only 2 readings, so the Total Exceeds Ref is only 2, and therefore the Ref Count is 0. 

This is correct, but I don't know how to summarize the Ref Count field, so that I can get the total days that the Ref Count was 1. For Well 16, it should be 7. 

Happy to adapt any code if any of my previous steps are actually unnecessary for this. I appreciate the help.

[v-dineshya · Accepted solution]

Hi @AMONT ,

Thank you for reaching out to the Microsoft Community Forum.

You already calculate "Ref Count" per day well as either 1 or 0. Now you need a measure to count how many days have "Ref Count" = 1.

Please refer below Measure "Total Days With 3 Exceedances" , It iterates over each unique date and checks if "Ref Count = 1" for that date and Counts those days.

Total Days With 3 Exceedances =
SUMX(
VALUES('Table'[DateOnly]),
VAR DayCount =
CALCULATE(
[Ref Count],
ALLEXCEPT('Table', 'Table'[DateOnly], 'Table'[Well_Number])
)
RETURN IF(DayCount = 1, 1, 0)
)

Note: "Ref Count" returns 1 or 0 per Well and Dateonly.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[AMONT]

Thank you so much, this was perfect!

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Greg_Deckler]

@AMONT First, please vote for this idea: https://community.fabric.microsoft.com/t5/Fabric-Ideas/Matrix-Table-grand-totals-with-Measures/idi-p...

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY 
And: https://youtu.be/n4TYhF2ARe8