Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Calculate subtotal rows by column?

I have some data ('HSBY') which contains periods during which an application was unavailable.

The data is formatted with a Date colum, a start time column, a duration and an endtime column and I add the minute number (from 0-1440) in the day for the start and end times:




I'm trying to create a timeline chart, so I created a measure ('On HSBY') for each 15-minute time period in the day and set that to 0 ir 1 if the application was unavailable for all or part of that period:


On HSBY = 
VAR TimeSlot = MAX('Timeslots'[Slot])
VAR TimeSlotSize = 15
VAR TimeStart = MAX('HSBY'[StartMinute])
VAR TimeEnd = MAX('HSBY'[EndMinute])
VAR InStartSlot = AND(TimeStart >= TimeSlot, (TimeSlot+TimeSlotSize) > TimeStart)
VAR InLastSlot = AND(TimeEnd >= TimeSlot, (TimeSlot+TimeSlotSize) > TimeEnd)
VAR InTimeRange = AND(TimeStart <= TimeSlot, TimeSlot < TimeEnd)

RETURN IF(InStartSlot + InLastSlot + InTimeRange > 0, 1, 0)

'Timeslots'[slot] is a simple table created from :

Timeslots = GENERATESERIES(0,1425,15)

to create the 15-minute time periods.

When displayed as a martix, with DATE, Start Time and End Time as the Rows, Slot as the colums and 'On HSBY' as the values, I can get the following:


which with a bit of conditional formatting becomes:



For the 15/05/21 for example, we have 2 separate rows which I'd like displayed as one, so I end up with have one row per day which I can then hopefully format.

How do I create another measure that is applied after my 'On HSBY' measure to create a new row per day with the sum of all time rows for that day?

The matrix would have to show only the daily subtotal rows.



Frequent Visitor

I've posted a new-but-related question, as I've managed to build a blank timeslot matric in Power Query, but am unsure of how to update each timeslot :


Frequent Visitor

I think one problem is that the data displayed in that matrix doesn't exist as a table anywhere -the cell contents are calculated dynamically (the measure 'On HSBY'). 


So to do this I need to somehow create an actual table either in Power BI using DAX or in Power Query when I pull the data in using M?

Super User
Super User

@SteveIves This looks like a measure aggregation problem. See my blog article about that here:

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks but I don;t think this will work - the 0s and 1s that I am formatting in my DAX measure and that are displayed inthe matric don't exists as values in a table, so I don't know if I can create a susb-total from them?


I'm still quite new to DAX and Power Bi, so I may be looking at this incorrectly. 

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.