cancel
Showing results 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 = 15VAR 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.

4 REPLIES 4
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 : https://community.fabric.microsoft.com/t5/Desktop/Update-a-table-with-values-from-another-table/m-p/...

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

@SteveIves This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

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])
etc.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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.

Announcements

#### 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.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors