cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Regular Visitor

## Create Median measure for Counting Absent Data as 0

I am having trouble calculating the median number of a column, where each row is a different hour of a day, but there may be missing dates/hours that I'd like to count as 0 in my median function.

It's difficult to explain, so I put a table demonstrating my issue below, covering 5 days of the year (1/1/2023-1/5/2023). NumStudies is the number of studies done during the given date/time. I would like to calculate for the given timeframe (1/1-1/5 in this case), what is the median number of studies done for every hour of the day? If there is no date/time in the table (for example on 1/1 there were no other studies except at 1:00 pm and 8:00 pm) I would like that to count as 0 when I calculate the median studies done for each hour for the given time frame.

Source Data:

 DateTime NumStudies 1/1/23 1:00 PM 1 1/2/23 1:00 PM 3 1/3/23 1:00 PM 5 1/5/23 1:00 PM 7 1/3/23 2:00 PM 1 1/4/23 2:00 PM 2 1/5/23 2:00 PM 3 1/5/23 3:00 PM 5 1/5/23 4:00 PM 10 1/5/23 5:00 PM 20 1/5/23 6:00 PM 30 1/5/23 7:00 PM 40 1/1/23 8:00 PM 100 1/2/23 8:00 PM 110 1/3/23 8:00 PM 120 1/4/23 8:00 PM 130 1/5/23 8:00 PM 140

What I'd like is a measure which calculates the median of the number of studies for each hour, accounting for date/times that may be missing. The results of the median function in this example for 1/1/2023 through 1/5/2023 would show:

Results:

 Hour MedianNumStudies 12:00 AM 0 1:00 AM 0 2:00 AM 0 3:00 AM 0 4:00 AM 0 5:00 AM 0 6:00 AM 0 7:00 AM 0 8:00 AM 0 9:00 AM 0 10:00 AM 0 11:00 AM 0 12:00 PM 0 1:00 PM 3 (i.e. median of 1,3,5,0 (from 1/4/23) ,7) 2:00 PM 1 (median of 1,2,3,0,0) 3:00 PM 0 4:00 PM 0 5:00 PM 0 6:00 PM 0 7:00 PM 0 8:00 PM 120 9:00 PM 0 10:00 PM 0 11:00 PM 0

I've tried a number of different methods but have not been able to come up with anything that works. Any help is much appreciated.

0 REPLIES 0

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors